Highlight Active Row & Column in Excel (7 Levels)

Discover how to highlight rows and columns in Excel. ➡️ Click to show more
** UPDATE: **Want to know how to use VBA to make this method work for all worksheets in the workbook? Check out my friend Ismail's new video: • Highlight Active Row o...
Most people try to skim long rows with their eyes or use fingers to keep track of their place. But your eyes can play tricks on you. And you might be presenting online where no-one else can see where your fingers are pointing.
Our method makes it easy to keep track of your place in big spreadsheets. And it uses Excel's built-in features so you don't have to buy anything extra to make it work.
You can use this if you're an analyst who works with large datasets. Or an auditor who has gigantic spreadsheets to check.
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.
Our step-by-step guide is perfect for intermediate Excel users.
🅰️ If you prefer to read here is the article with full written instructions:
www.launchexcel.com/highlight...
🅱️ Download the sample workbook here:
d1yei2z3i6k35z.cloudfront.net...
🔹 Chapters:
00:00 - Intro to Excel Highlighting
00:47 - Manual Highlighting of Rows and Columns
01:17 - Level 1: Highlight Active Row with Conditional Formatting
02:41 - Level 2: Automate Row Highlighting with VBA Code
05:20 - Level 3: Highlight Active Column with Conditional Formatting
07:21 - Level 4: Highlight Both Active Row and Column
09:14 - Level 5: Create Crosshairs Effect with Different Colors
13:18 - Level 6: Adding an On/Off Dropdown
16:29 - Level 7: Adding an On/Off Toggle Button
20:51 - Bonus: Keeping Headers in View
22:33 - Conclusion and More Resources
🚀 Best Excel Resources
Excel Dashboards: Learn how to make killer dashboards in Excel. They will set your skills apart from the crowd. Invest in your data reporting and presentation skills.
➡️ go.launchexcel.com/moth-dashb...
Explore 100+ Excel Templates: Save time and streamline your workflow. Here is a selection of over 100 spreadsheet templates. They cover Accounting, Finance, Data Analysis, HR, Operations, Project Management, Real Estate, Sales & Marketing, Small Business, and Management.
➡️ www.launchexcel.com/best-exce...
Launch Excel Macros & VBA School: Say Goodbye to Tedious Manual Work and Automate Your Way with Excel VBA. Our course helps you to learn Excel VBA and save hours of time every week even if you have zero prior experience with programming.
➡️ www.launchexcel.com/shop
🚀 Ready to launch your Excel skills into orbit?
Subscribe and join the Launch Excel community for more stellar Excel tips and tricks! Check out www.launchexcel.com for in-depth tutorials that will propel your skills beyond the stars!

[@launchexcel]

🔹 Chapters: 00:00 - Intro to Excel Highlighting 00:47 - Manual Highlighting of Rows and Columns 01:17 - Level 1: Highlight Active Row with Conditional Formatting 02:41 - Level 2: Automate Row Highlighting with VBA Code 05:20 - Level 3: Highlight Active Column with Conditional Formatting 07:21 - Level 4: Highlight Both Active Row and Column 09:14 - Level 5: Create Crosshairs Effect with Different Colors 13:18 - Level 6: Adding an On/Off Dropdown 16:29 - Level 7: Adding an On/Off Toggle Button 20:51 - Bonus: Keeping Headers in View 22:33 - Conclusion and More Resources If you prefer to read here is the article with full written instructions: www.launchexcel.com/highlight-active-row-column-excel-7-levels Download the sample workbook here: d1yei2z3i6k35z.cloudfront.net/2762499/65d497ba9e1d9_HighlightActiveCellpublic.xlsm.zip

[@tusharvador8553]

Extremely helpful. The level wise instruction really helped.👍🏻

[@StepDub]

Clear and concise. Appreciate the inclusion of shortcuts. Thank you.

[@launchexcel]

I'm glad you found it clear and concise. I love shortcuts for speeding up work in Excel. Thank you for the kind feedback!

[@StepDub]

@@launchexcel this was a really helpful tip for me, because I use two screens and if I just highlight the row that I am wondering from, the highlight disappears when the sheet loses focus. Using your method I can keep the highlight while working in another screen. Thanks 🙏

[@rsbcares985]

Very clear..good job!!!

[@launchexcel]

Thanks rbscares985!

[@AndyLoweUTube]

Nice demo. Shows concepts that can be utilized for other solutions.

[@launchexcel]

Thanks for the feedback! Yes you're right, with VBA we can reuse concepts in many other solutions. The beauty of coding 😀

[@emiellebulawit8795]

Thanks a lot! Great tutorial!

[@markhuang368]

It is such a high quality tutorial. I like you video very much. I think your tutorials are as good as those of Leila's. Hope to see more videos from your channel.

[@launchexcel]

More coming soon, if you have specific requests let me know 😄

[@robertconrad7269]

Brilliant ❣️

[@launchexcel]

Thanks!

[@sitotagirma2157]

Oh Dear, it is great work that makes life easier. I appreciate your presentation.

[@launchexcel]

I am glad you find this makes life easier for you, and I appreciate your comment 😄

[@RipRoarLB]

I left the conditional formatting in place, one for the row one for the column. I liked this method rather than the combined method, is so I could use a different format for the column and the row. That could be an alternative. This was a great insight - good job and thank you.

[@launchexcel]

Thank you for sharing this!

[@adhamm5503]

This's great! Going through levels with clear explanation. Thank you bro..can you make a video on how to create a VBA to spell numbers. Thank you

[@launchexcel]

Sure! I'll add that to my list of video ideas. Is there a specific range of numbers you want to spell? How big? Include negative numbers? And decimals or whole numbers?

[@ravchana2393]

AMAZING!

[@launchexcel]

Thanks @ravchana

[@paramveerssachdeva]

For this same function / calculation, I have seen various videos in various accents. maybe a hundred. This tutorial is rated no 1 of out 100. Full marks 100 out of 100. have downloaded the workbook will be attempting to do this on my sheets. Your explanation is step by step, thorough, leaving out no steps so the person who's learning will not miss out on a single thing. Will be buying your course also as this shows how precise you are. Perfection. hope there is not lag on the end sheet when i finish doing this to my sheets. As there is no lag on the worksheet i have downloaded from your page, thanks a lot

[@launchexcel]

Thanks for you support paramveerssachdeva! I just found an Excel add-in by JKP, who is an Excel MVP. It accomplishes the same task but in a different way, using arrow shapes drawn on the worksheet. And it works in any open workbook after you install the add-in. No need to write more code. Here's the link to JKP's Follow Cell Pointer addin: jkp-ads.com/download.asp#followcellpointer Microsoft Instructions on how to install or remove Excel add-ins: support.microsoft.com/en-gb/office/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460#OfficeVersion=Windows P.S. As always, be careful of what you download. I can recommend JKP because of his long-standing solid reputation in the Excel community. But do be careful what you install.

[@exploreyourexcel]

I love your deep tutorial videos.

[@launchexcel]

Thanks! I hope you enjoy the new tutorials I release. At the moment the focus is Excel VBA. In future it may be different. Good luck with your channel 😄

[@exploreyourexcel]

@@launchexcel i love vba also please continue and if possible then create a perfect playlist so that we can lear deeply as per topic. I am waiting for video. Thanks

[@launchexcel]

@@exploreyourexcel yes VBA is pretty awesome! I wish it got more actively developed by Microsoft but I'm happy it continues to be supported even with newer automation tools coming out.

[@bitaperez8171]

Your amazing

[@launchexcel]

You are too!

[@abdelazizallam]

thanks

[@launchexcel]

You're welcome!

[@gerardogarciagutierrez7219]

Awesome Video! I was wondering if there would be a way to implement this but without highlighting columns that have other type of conditional formatting so that the original format is not altered in those specific columns.

[@launchexcel]

That's a great question, I think you might have to experiment with the precedence of Conditional Formatting rules. Check out this Tech Republic article as a starting point: www.techrepublic.com/article/learn-how-excel-applies-conditional-formatting-rules-to-avoid-unintended-results/

[@gayustambunan6250]

Welcome back.

[@launchexcel]

Good to be back 😄

[@bitaperez8171]

Tanks

[@launchexcel]

Welcome!

[@nikk5273]

Thank you, very nice work. What if we want to highlight not the entire row but only a range of cells, eg A1 to A10 ?

[@launchexcel]

You can restrict the Conditional Formatting to a specific range of cells. In the Conditional Formatting Rules Manager, you need to find the "Applies To" range. And specify the range you want e.g A1:A10. Hope that helps 😁

[@tipwareetivapaibool8683]

Cool vid! Love it! One question, how can I apply to highlight active roll for all sheet in the workbook? Thank you very much ☺️

[@launchexcel]

Thanks for your comment. For the method explained, you need to set up the conditional formatting on each worksheet and copy the VBA macro to each sheet you want to use. MichaelSchultz1647 shared a tip in another comment: "I've created a worksheet with the highlighted functionality completed that I've renamed "template". I then copy this into a new workbook multiple times. Each sheet has the highlight set up."

[@launchexcel]

Check out a video by my friend Ismail: kzbin.info/www/bejne/iajaYWeJdqucj5Y

[@rajdeepchakraborty3259]

Such a nice and detailed explanations of concepts. Thanks

[@launchexcel]

Glad you liked it!

[@sjp1987]

Hi there, I love this video, very well explained. I have one question, can you this be turned into a macro that you can save in your personal macro workbook? I know the conditional formatting element can easily be recorded, but not sure about if I can add the code to the active worksheet to automatically recalculate. example what I imagine it would do. 1. Highlight your data area 2. Run the macro (having the on/off switch on your ribbon) 3. Do what you need to do, then turn it off. 4. Move to next workbook and repeat. I hope that makes sense. Thanks either way.

[@launchexcel]

Thanks for your comment! yes, putting this in the personal macro workbook would make it available in any workbook. However, I just found an Excel add-in that highlights the active cell either with arrows or by selecting the active row and column. It comes from from JKP (Jan Karel Pieterse) who is an Excel MVP and is a free download. I recommend you check it out. It's called "Follow Cell Pointer" and you can download it here: jkp-ads.com/download.asp#followcellpointer And here are instructions from Microsoft for adding or removing add-ins: support.microsoft.com/en-gb/office/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460#OfficeVersion=Windows

[@VinceMims77]

Awesome tutorial for an Excel noob like me. Is there a way to make this a macro to apply to NEW workbooks super quick in the future? Thanks!

[@launchexcel]

Thanks for your comment 😃 I don't have a solution to make the macro apply to new workbooks quickly. Another viewer @michaelschultz1647 suggested: "Thanks. I've created a worksheet with the highlighted functionality completed that I've renamed "template". I then copy this into a new workbook multiple times. Each sheet has the highlight set up. But it would have been nice to do this in an existing workbook." That's a workaround you can try.

[@VinceMims77]

@@launchexcel Oh! That's a great idea! Thanks!

[@launchexcel]

You might want to try out my friend Ismail's VBA method as explained in his video: kzbin.info/www/bejne/iajaYWeJdqucj5Y

[@edwindeo]

Hi Vic, would be able to do diagonal and anti diagonal highlights from active cell

[@launchexcel]

That's an interesting question. I guess there's a conditional formatting formula that handles diagonals. You might want to check out this thread for a start: www.reddit.com/r/excel/comments/wpveti/how_to_select_cells_diagonally_right_and_up/ The suggested formula for diagonal cells is this: =COLUMN()+ROW()=50+6

[@kingmaker1985]

Nice Video! Can you make it as an option in the custom ribbon? So that I can open any excel and click on the ribbon to toggle between on and off

[@launchexcel]

That's a great suggestion. I don't currently have a solution for that, but it makes a lot of sense. Let me look into this.

[@xllearner]

Is there a way to put this in your personal macro workbook so it can us used to apply to all worksheet as desired.@@launchexcel

[@launchexcel]

@@xllearnerI think it could be done, but I haven't figured out how.

[@launchexcel]

*** UPDATE: *** Want to know how to use VBA to make this method work for all worksheets in the workbook? Check out my friend Ismail's new video: kzbin.info/www/bejne/iajaYWeJdqucj5Y

[@michaelschultz1647]

Great video. How do I apply this for multiple sheets in the same workbook.

[@aakashchaturvedi5882]

Same question. Every sheet has to be redone in a workbook.

[@launchexcel]

At the moment, if you want to apply to multiple sheets, you need to redo the steps for each sheet.

[@michaelschultz1647]

Thanks. I've created a worksheet with the highlighted functionality completed that I've renamed "template". I then copy this into a new workbook multiple times. Each sheet has the highlight set up. But it would have been nice to do this in an existing workbook. I'll keep watching your channel for updates and to learn new things.

[@launchexcel]

Check out my friend Ismail's VBA method as explained in his video: kzbin.info/www/bejne/iajaYWeJdqucj5Y to see if this helps

[@Joy-kg4iv]

Great video! Tried and it worked but after returning over the weekend, it does not anymore, even if I tried to remove the rules set earlier. I’ve also saved this file as excel macro-enabled. Any help is greatly appreciated

[@launchexcel]

Hi Joy! My first thought is that when you open the file, even though it’s saved as an Excel macro-enabled file, you might not have allowed macros to run. To ensure the code works, you need to enable macros. Here’s how to do it: 1. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. 2. Select Enable all macros. For detailed steps, check out my article on enabling macros here: www.launchexcel.com/enable-disable-macros-excel I'd also check that the VBA code and conditional formatting rules are still in place. If issues persist, try removing and reapplying the rules. Feel free to reach out if you need more help!

[@Joy-kg4iv]

@@launchexcel Thank you so much for your kind response. I’m gonna try out and hopefully it works again. Thank you

[@robbe58]

Is there no example file for this video? Always handy to consult.

[@launchexcel]

Thanks for the comment. Yes, I'll prepare one.

[@robbe58]

@@launchexcel Thank you. Give us a sign when it is available for download.

[@launchexcel]

​@@robbe58You can download the sample workbook here. Enjoy! d1yei2z3i6k35z.cloudfront.net/2762499/65d497ba9e1d9_HighlightActiveCellpublic.xlsm.zip And if you prefer to read, here is the article with full written instructions: www.launchexcel.com/highlight-active-row-column-excel-7-levels

[@Miro_L]

Quit nice, can it be made so it highlights the row and column just by hovering over the cell instead clicking on a cell

[@launchexcel]

Thanks for the question. I think hovering is much harder with VBA because there's no Hover event. You might want to check out this thread for some ideas: chandoo.org/forum/threads/how-to-make-row-colour-change-on-mouse-hover.32029/

[@zs9853]

This would be great for my work but before i try, how will it work in a MULTI-USER environment where multiple people are using the same workbook at the same time? Will it highlight multiple rows and columns selected by every user?

[@launchexcel]

Hey @zs9853, I haven't tested it in a multi-user environment. But I think it won't work. An alternative might be to make a copy of the workbook for your own personal use, and then update the shared workbook later.

[@zs9853]

@@launchexcel Thank you very much. Is there a possibility of adding another level in this video to implent multi user? where each user see just their row/col highlighted?

[@launchexcel]

That's a great idea, but my Excel knowledge doesn't take me that far. If anyone reading this has any ideas, give us a shout :) @@zs9853

[@kevingreenhill9166]

I also noticed that when you enable this, you can no longer copy and paste between cells. I have created a control sheet and put the on / off switch on this, and put the code in a module. I then put the code for each sheet in an IF statement, so when it is set to off, I can still copy and paste. Sadly, this means amending the code on 70 sheets.

[@launchexcel]

Thank you for sharing your solution. Would you mind pasting the code here for others to see?

[@kevingreenhill9166]

@launchexcel Sorry I cannot post the code.

[@launchexcel]

@@kevingreenhill9166 Ok, no worries. BTW I'm still on the look out for an easier way to make this all happen. Will keep everyone posted ...

[@lalitkumarpandey5911]

Hi, I am struggling to apply the conditional formatting based on other cell values. Kindly help. The case is, in one column I have qtrs. like Q-1, Q-2, Q-3 Q-4 and in other column there are branch codes and these branch codes are repetitive based on some activities. I want to highlight only those cells of branch codes column which are repetitive in that qtr. only. Suppose if I filter out Q-1, it will show only those cells which appear more than once in Q-1 only and likewise in other qtrs.

[@launchexcel]

For this query, I recommend you try getting specific help from go.launchexcel.com/excel-rescue-main

[@kevingreenhill9166]

Can you please provide the code to apply this to all sheets in the workbook.

[@launchexcel]

I hear you! Yes, I'm looking for an approach to make it easier to apply to all sheets in the workbook. At the moment, my method only works on a single sheet. If I find a good way, I'll post here.

[@launchexcel]

Check out my friend Ismail's VBA method as explained in his video: kzbin.info/www/bejne/iajaYWeJdqucj5Y to see if this helps

[@sundarg1068]

Tried doing this in more than one sheet of a workbook and it only works on the first sheet this is being done, and it is not working on the rest of the sheets. Any reason why and how it can be done for multiple sheets?

[@launchexcel]

For the method explained, you need to set up the conditional formatting on each worksheet and copy the VBA macro to each sheet you want to use. How many worksheets do you want to use it for?

[@sundarg1068]

@@launchexcel Yes i did set up conditional formatting for each sheet individually and also gave the VBA macro for each sheet separately. Still, only the first sheet works and not the others. I'm trying for 2 more additional sheets than the first one.

[@launchexcel]

@@sundarg1068 OK, if the first sheet works but not the others, it could be that you are using the same named range in the VBA code. You'll need to have a separate named range for the On/Off value, e.g. Highlight_status_1 / Highlight_status_2 / Highlight_status_3 etc. Is that what you're doing?

[@launchexcel]

Check out my friend Ismail's VBA method as explained in his video: kzbin.info/www/bejne/iajaYWeJdqucj5Y to see if this helps

[@millerlighter]

I’m getting a Visual Basic error “variable uses an automation type not supported in Visual Basic” when I try to select worksheet in the Visual Basic editor

[@launchexcel]

It looks like this could be a Mac-specific problem. Read this page on the Microsoft support site: support.microsoft.com/en-gb/topic/-variable-uses-an-automation-type-not-supported-error-in-visual-basic-editor-in-excel-for-mac-c3125bf3-c30b-4d62-be42-d2f1bec0e5ad Hope that helps.

[@asniamustapha5832]

nice tutorial! but mine doesn't work, the Formula its self.help

[@launchexcel]

Thanks for your comment and for watching my tutorial on highlighting the active row and column. I'd like to help, however I don't understand what you mean by "the Formula its self.help". Which part of the video did you get stuck at? If you can give me a video timestamp (mm:ss) it will give me more context to answer.

[@user-mw1ku9jo2u]

I can't get this to work. I have followed the instructions, but my lines will not be colored. Is there any setting that needs to be done in excel?. The usual conditional formatting works.

[@launchexcel]

Hmm, that's odd. The highlighting should work if your conditional formatting works. Have you tried following the steps in the article? www.launchexcel.com/highlight-active-row-column-excel-7-levels

[@user-mw1ku9jo2u]

Hello. I have gone through all the steps thoroughly. It doesn't work for me. When I use conditional formatting, such as "rules for formatting greater than", the formatting works. @@launchexcel

[@launchexcel]

Can I check what version of Excel do you use?

[@user-mw1ku9jo2u]

Absolute. How?@@launchexcel

[@launchexcel]

@@user-mw1ku9jo2u try the instructions on these sites to find out what version of MS Office you have (this will tell you what version of Excel you have) support.microsoft.com/en-gb/topic/what-version-of-office-am-i-using-d21c5935-40b1-b117-122d-5e5f05a1a263 support.microsoft.com/en-us/office/find-details-for-other-versions-of-office-8e83dd74-3b83-4528-bda6-6ff6118f8293

[@MrLuupski]

It fails when you select multiple cells

[@launchexcel]

yes, that's true. It's a limitation of Excel not having a *function to determine the selected range of cells* . So in conditional formatting we can only detect the Active Cell, as far as I'm aware. If anyone has a solution, feel free to post! Thx

[@arfr3567]

Noice

[@launchexcel]

Tanks 😀

[@user-ri6ed8gh1q]

It's extremely easily in WPS spreadsheet, one click on a icon does this. But, why is it so much difficult in Excel. Time to switch from Excel to WPS spreadsheet

[@launchexcel]

Thanks for commenting! I had no idea WPS could do this in Reading Layout in just one click. After a quick search I found the official WPS video here (45 seconds) kzbin.info/www/bejne/fZqlg4yMdtd8j9k Then I discovered that the official WPS channel has fewer subs than this (small) Excel channel... so I guess Excel is just too far ahead in popularity that it's hard for WPS to get a foothold. What would you say are the biggest strengths and weaknesses of WPS vs Excel? I'm curious to learn as I know nothing about WPS, and you seem to be familiar. Again, thanks for bringing this up.

[@spongebobby188]

22 mins? I'm sorry I need a 2 min version ....my time is more precious ...I won't watch this vid.

[@launchexcel]

I hear you! That's great feedback. Let me see what I can do.

[@jbtejada]

😂

[@justscrolling8973]

Took about 2 minutes just to leave this comment…

[@turbotommyf]

How can I highlight just the first cell (A) in the row and the top cell (1) in the column for an active cell? Thank you!

[@launchexcel]

Try this. Go to "Conditional Formatting" → "Manage Rules" and then specify where you want the rule to apply to in the column called "Applies To'. You can select applies to column A:A for the row highlight and applies to row 1:1 for the column highlight. Hope that helps.