Highlight Active Row & Column in Excel (7 Levels)
Last updated on Feb 20, 2024 By Victor Chan
Do you work with large Excel spreadsheets? Have you found yourself losing your place when you look along a row with lots of columns? When working with lots of data, it's common to get mixed up and lose track of where you are.
Most people use their mouse to highlight the cells they are working on. But this method isn't the best. It can be slow, and sometimes you’ll click on the wrong thing by mistake.
Now you can forget about these problems.
I'm going to show you a much better way. It will automatically highlight the row and/or column of the active cell for you. So you always know where you are.
You might be an analyst who works with large data sets. Or an auditor who has gigantic spreadsheets to check. You can use the methods I’m going to show you so your eyes don't play tricks on you.
You can also use it if you’re presenting online in a Team or Zoom call. It will highlight your position and make it easier for others to follow along.
In this guide, you will learn how to make Excel show you where you are by highlighting the row and/or column you're in. This is fully customizable so you can choose the colors you like best.
By the end, you'll know how to make Excel automatically highlight the active cell, row, and column. And you'll learn how to build a switch to toggle the highlighting on and off with ease.
Level 0: Manual Highlighting (what most people do)
Level 1: Highlight Active Row Using Conditional Formatting
Level 2: Automate Row Highlighting with VBA
Level 3: Highlight Active Column with Conditional Formatting
Level 4: Highlight Both Active Row and Column
Level 5: Create a Crosshairs Effect with Different Colors
Level 6: Add a Dropdown to Switch Highlighting On and Off
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file Highlight-Active- Cell.zip
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
Level 0: Manual Highlighting (what most people do)
Before we get to doing this automatically, let’s see how most people do it manually.
- 1. Open your Excel spreadsheet.
- 2. To select a whole row, move your cursor to the row number on the left side of the sheet and click on it.
- 3. To select a column, click on the column header, which is the letter at the top of the spreadsheet.
What are the problems with this?
- No Customization: You can’t change the default grey highlighting to a different color. It’s not very distinct, which can make it harder to see in large spreadsheets or when your cells are grey.
- Single Direction: You can’t select a column and row at the same time so you can’t track data in two directions.
- Time Consuming: Clicking on each row or column header is slow when you’re dealing with large datasets.
Let’s find out how to overcome each of these problems, starting with highlighting a row using a different color from the default grey.
Level 1: Highlight Active Row Using Conditional Formatting
Start by learning how to highlight rows using Excel's conditional formatting. We'll show you the exact formula to use and how to apply it to your spreadsheet for that personal touch.
- 1. Select the range of cells you want to highlight.
- 2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
- 3. Choose Use a formula to determine which cells to format.
- 4. In the formula box, type =row()=cell("row") (This compares whether the row number is the same as the active cell’s row. If this is true, we change the format in the next step to highlight the row).
- 5. Click on Format, select your desired highlight color from the Fill tab, and press OK.
- 6. Confirm the new rule with OK.
This highlights the currently active row. But when you select a new cell it doesn’t update. You need to press F9 to recalculate the workbook.
We don’t want to keep pressing F9 every time we select a new row. Level 2 shows us how to refresh the row automatically.
Level 2: Automate Row Highlighting with VBA
Take the leap into automation by adding a simple line of VBA code. VBA stands for Visual Basic for Applications. Don't worry if you're new to VBA. It’s simply a programming language built into Excel that let’s us automate our work.
We’ll walk you through every step to make your rows light up as you navigate.
- 1. Enable the Developer tab by right-clicking the ribbon, selecting Customize the Ribbon, and checking the Developer option.
- 2. Under the Developer tab, click on the button for Visual Basic. This opens up the VBA editor.
- 3. In the VBA editor, double-click on the sheet you're working on from the project explorer.
- 4. In the worksheet's code window, select Worksheet from the object drop-down (top left) and SelectionChange from the procedure drop-down (top right).
- 5. Type the code Target.Calculate within the Worksheet_SelectionChange subroutine. Your end result will look like below.
- 6. Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file. To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
[VBA Code Box]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub
Level 3: Highlight Active Column with Conditional Formatting
Extend your skills to columns. With a conditional formatting rule like the one in Level 1, you'll be able to make any selected column pop with color.
- 1.Select the range of cells you want to highlight.
- 2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
- 3. Choose Use a formula to determine which cells to format.
- 4. In the formula box, type =column()=cell("col") (This compares whether the column number is the same as the active cell’s column. If this is true, we change the format in the next step to highlight the column).
- 5. Click on Format, select your desired highlight color from the Fill tab, and press OK.
- 6. Confirm the new rule with OK.
- 7. Remember to include the VBA code from Level 2 so the updates are automatic. The code goes in the worksheet that you want to highlight.
Level 4: Highlight Both Active Row and Column
Why stop at one when you can have both? Combine your knowledge from Levels 1 - 3 to achieve a comprehensive highlighting effect.
- 1. Select the range of cells you want to highlight.
- 2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
- 3. Choose Use a formula to determine which cells to format.
- 4. In the formula box, type =or(row()=cell("row"), column()=cell("col")) (This compares whether the row number is the same as the active cell’s row, OR the column number is the same as the active cell’s column. If either of these statements is true, we change the format in the next step to highlight the row and column).
- 5. Click on Format, select your desired highlight color from the Fill tab, and press OK.
- 6. Confirm the new rule with OK.
- 7. Remember to include the VBA code from Level 2 so the updates are automatic. The code goes in the worksheet that you want to highlight.
Level 5: Create a Crosshairs Effect with Different Colors
For those who want more, we introduce the crosshairs effect. By implementing dual conditional formatting rules, your active cell will shine even brighter in contrast to the row and column.
- 1. We need to set up two conditional formatting rules. The first with an AND formula for the active cell and the second with an OR formula for the row and column.
- 2. Follow the same steps to add Conditional Formatting in Levels 1 - 4.
- 3. To highlight the active cell, use the formula =and(row()=cell("row"), column()=cell("col")) and select a unique color. The formula is TRUE when both the row and column match the active cell.
- 4. To highlight the active cell, use the formula =or(row()=cell("row"), column()=cell("col")) and select a unique color. The formula is TRUE when either the row or the column match the active cell.
- 5. An important note: Ensure the AND rule is placed above the OR rule in the Manage Rules dialog of Conditional Formatting. Otherwise we won’t see the active cell being highlighted. This is because the rules are applied from top to bottom. Select a rule and use the UP/DOWN buttons to change its position.
- 6. Remember to include the VBA code from Level 2 so the updates are automatic. The code goes in the worksheet that you want to highlight.
What happens if I want to get rid of the highlighting? Say your boss doesn’t like it. You can click on Conditional Formattingand click on Clear Rules > Clear Rules from Entire Sheet. That gets rid of all the conditional formatting. But it’s a bit of a pain to put it back because you’ll have to type all the formulas again. Let’s do this a smarter way in Level 6.
Level 6: Add a Dropdown to Switch Highlighting On and Off
Learn how to toggle the highlighting effect on and off with a simple drop-down list. This feature is perfect for when you need a clear view without the extra colors.
- 1. Start by typing "Highlight" in cell E2.
- 2. Press the Tab key to move to cell F2, where you’ll create a dropdown menu.
- 3. Go to the Data tab on Excel's ribbon.
- 4. Click on Data Validation.
- 5. In the Data Validation settings, choose List under the Allow option.
- 6. In the Source box, type "On, Off" to create your dropdown options.
- 7. Press OK to finish setting up the dropdown menu.
- 8. Now, you'll see "On" and "Off" as options in the dropdown menu in cell F2.
- 9. Click Off in the dropdown to set the default state.
- 10. With cell F2 still selected, go to the Name Box above the spreadsheet and type "Highlight_switch" to name the cell.
- 11. Press Enter to confirm the name.
- 12. Select the data range that you want to apply the highlighting to.
- 13. With the range selected, go back to the Home tab and click on Conditional Formatting, then New Rule.
- 14. Choose "Use a formula to determine which cells to format."
- 15. In the formula box, type =IF(highlight_switch="Off",TRUE,FALSE).
- 16. Don't set any format and click OK.
- 17. Now go back to Conditional Formatting and choose Manage Rules.
- 18. You’ll see your new rule there. Check the option "Stop If True" for this rule.
- 19. Click OK. Now, the highlighting will only be active when you select "On" from the dropdown menu in cell F2.
- 20. If any of these steps aren’t clear, watch my video “Highlight Active Row & Column in Excel (7 Levels)” to see me performing each step with a full explanation.
With these steps, you can easily switch the highlighting on your data on and off. This can make your Excel tasks more efficient and your presentations clearer.
But, suppose you want an even faster way, skipping the two-click dropdown. In that case, we'll simplify it to a single click in Level 7.
Level 7: Add a Button to Toggle Highlighting On and Off
Let's take the toggle switch up a notch, integrating it with VBA for a seamless experience. Add a custom button to your spreadsheet that controls your highlighting with a single click.
- 1. Duplicate the setup from Level 6. If you’re highlighting a different sheet, use a different name for the highlight_switch, such as highlight_switch_2. Otherwise you can stick to the same name.
- 2. Access the VBA Editor by selecting the Developer tab in the ribbon and clicking on the Visual Basic button.
- 3. In the VBA editor, find the module corresponding to your worksheet.
- 4. Begin typing your macro with Sub Toggle_Click() to define a new subroutine.
- 5. Directly below the Sub line, insert a comment for clarity, such as ’This macro toggles the highlighting on and off.
- 6. Type the following VBA code.
[VBA Code Box]
Sub Toggle_Click()
' This toggles the highlight on and off
With [Highlight_switch_2]
If .Value = "Off" Then
.Value = "On"
Else
.Value = "Off"
End If
End With
End Sub
- 7. Go back to your Excel worksheet and add an interactive element by navigating to Insert > Shapes, then select and draw your desired shape. Pro Tip: Hold ALT before clicking to snap to the grid.
- 8. Customize the shape with a fill color and add the text "Toggle Highlight" for clarity.
- 9. Assign the macro to the shape by right-clicking the shape, selecting Assign Macro, and choosing "Toggle_Click" from the available options in “This Workbook”.
- 10. Test the macro by clicking the shape, which should now toggle the highlighting with a single click. Yay!
- 11. Finally, save your workbook as an Excel Macro-Enabled Workbook (*.xlsm) through File > Save As. This ensures your VBA code stays intact.
- 12. If you are having trouble following these steps, check out my video “Highlight Active Row & Column in Excel (7 Levels)” to see me performing each step with a full explanation.
For a beginner’s guide on how to work with VBA macros in Excel, read this article How to Edit Macros in Excel: The Ultimate Beginner's Guide.
And if you’d like to learn Excel VBA in a structured, methodical way, I have an online course called “Launch Excel Macros & VBA School”. You can find it in the shop.
Bonus: How to Keep Your Headers in View
As a bonus, discover two methods to keep your headers visible as you scroll through your data. Choose between freezing panes or transforming your range into a data table for better navigation.
- 1. Use the Freeze Panes feature under the View tab to keep headers visible while scrolling.
- 2. Alternatively, convert your data range into a table using the Insert tab to enable automatic header visibility.
Conclusion
We've reached the end of our step-by-step guide to dynamic highlighting in Excel.
With practice, these new techniques will become second nature. Ensure you save your workbook as a macro-enabled file to keep the VBA magic intact.
I’d love to hear your feedback, so please share your experiences and questions in the comments below. Which part of the journey was your favorite?
I have a library of videos you can watch to learn more about Excel. You’ll learn new ways to improve your spreadsheets. Here’s the link to my YouTube channel.
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.