Highlight Active Row and Column in Excel (Based on Cell Selection)

One of the Excel queries I often get is - “How to highlight the Active Row and Column in a data range?”
Read more about the tutorial: trumpexcel.com...
Download file: www.dropbox.co...
So I decided to crate this video and show how to do this,
In this video, I will show you how to use Excel formula and conditional formatting to instanly highlight active row and column as soon as the user makes a selection or changes the selection.
To make sure this is dynamic, I have used a very simple VBA code as well (the worksheet change event).
Useful Notes:
-- This method would not impact any formatting/highlighting you have done manually to the cells.
-- Conditional formatting is volatile. If you use it on very large datasets, it may lead to a slow workbook.
-- The VBA code used above would refresh the workbook every time there is a change in selection.
-- CELL Function is available in Excel 2007 and above version for Windows and Excel 2011 and above for Mac. In case you’re using an older version, use this technique by Chandoo.
Free Excel Course - trumpexcel.com...
Paid Online Training - trumpexcel.com...
Best Excel Books: trumpexcel.com...
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: www.youtube.co...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

[@jilldunning8870]

Genius!!! This is the perfect workaround for the inability to highlight the row and column headings.

[@kristylazo3403]

Even though this video is from 2018, 4 years later, your video is still helpful. I saved my Excel file to a Macro-Enabled file and works perfectly after saving and closing. Thank you, again.

[@MariyamSharief1711]

2018 was 4 years ago???? 😳 time flies

[@kristylazo3403]

@@MariyamSharief1711 lol, at first I thought I miscalculated..lol . Yes, time flies as we get older (I'm showing my age🤐)

[@MarcFireHart]

Excellent tutorial! I can't believe I haven't been doing this before. It makes it much easier to show selected rows or columns and not have to keep double-checking to make sure you're looking at the correct data.

[@loogan7440]

it actually works on tables. also you may not need to highlight row and column, but only rows, so here you have shortest code for ths task =OR(CELL("row")=ROW())

[@judderdude]

BRILLANT! Concise and No Nonsense tutorial that got the job done for me and also allowed me to modify the formula to have only a row as well as change the cell range. WELL DONE Ace!

[@princetonlucian802]

I know Im asking randomly but does any of you know of a way to get back into an instagram account..? I was dumb forgot my password. I love any tips you can offer me!

[@ulisesshane8379]

@Princeton Lucian instablaster ;)

[@princetonlucian802]

@Ulises Shane Thanks for your reply. I got to the site thru google and I'm trying it out now. I see it takes a while so I will reply here later with my results.

[@princetonlucian802]

@Ulises Shane It worked and I actually got access to my account again. I'm so happy:D Thanks so much you saved my account !

[@ulisesshane8379]

@Princeton Lucian Happy to help :)

[@KetoJustRight]

Thanks for making this video...I wondered if this type of feature was available and your video has helped me. Initially, it didn't work, but I learned from a commenter below that I had to replace the quotation marks for "row" and "col". I pasted the formula into Word, replaced the quotation marks, then pasted the formula into the formatting field as you instructed and it worked. This will save me so much time at work. Thank you, thank you, thank you!

[@meetkcs]

Every day I am learning new tricks from your videos - very productive in real-time scenarios. A big thanks to all the Hands and Minds of these videos.

[@mahamohan1]

I am viewing this video for the second or third time. I find this video much useful in my day-to-day work and I want to add one more functional aspect to it. As long as you are using the data sheet alone its fine; but if your boss who is not excel savvy needs to go through it, you may insert a check box from the form control and combine this with the cell function formatting. So that when you unclick, there will not be conditional formatting and when clicked, there will be formatting. Also, this helps you when you try to print the data without formatting.

[@elviaazalialopez130]

Very helpful addition. How can I do that? Thank you!

[@andriikulishov3371]

Such a contrast to how you convey the information! I've started to watch the video and in the 1st minute of it, wow, understandable! Thanks!! Am continue watching! P.S. Have a nice day too! 🙂

[@trumpexcel]

Thank you so much for the kind words Andrii 😊 Glad you found the video helpful!

[@iZacq]

​@@trumpexcel Someone needs to Talk about this How can I get to Show *VIEW CODE* when I Right Click on Tab I don't get it when I Right Click on Tab :(

[@trumpexcel]

That's weird... It should show up. Alternatively, you can use the keyboard shortcut ALT + F11 to open the VB Editor (but you'll have to double-click on the sheet name to see the code in that sheet)

[@MrAsifsonu]

Excellent video................ ... this video resolved my biggest headache to read excel data...Thank you so much for the video

[@heathstapleford5044]

THIS IS AWESOME!!!! Financial auditor here and my spreadsheet are gigantic. This is literally going to save me multiple hours a week probably.

[@narupa9]

I have watched many You Tube videos to accomplish this. However, your video was the only one that helped me. Keep up the great work. I look forward to more videos.

[@trumpexcel]

Thanks Narupa... Glad you found the video helpful 🙂

[@utube8108]

Thanks man. You are first person that i have subscribed for excell. Solved my problem. Stay blessed always

[@trustabyss]

You're awesome, I'm gonna try this! My eyes play tricks on me when looking at long rows. This is the solution!

[@hamdanasr8574]

Wow, you are an AMAZING instructor ❤

[@trumpexcel]

Thank you so much for the kind words 😊

[@jwalkerbg60]

Thank you for the video. I need this dynamic highlighting because I use wide tables with many columns and it is not easy to look left then right and to stay at the same row visually.

[@pranavshrestha4953]

The only useful VBA code that I know I can do by myself. Wonderful tutorial as always. Thanks!

[@slupien35]

Thank you for the easy to follow guide

[@shubhamgupta4U]

Useful things, I often require this selection but didn't search ever before. Now whenever I need it, surely I will do it

[@rljordan91]

best tutorial on this concept yet, and very clean formatting. thanks!

[@gaelmaury4253]

Thank you for this video and the quality of your explanation. You are very pedagogue

[@jeovannylopeztoribio4299]

Amazing perfect explication from start to finish !

[@trumpexcel]

Thank you 😊 Glad you found the video useful!

[@arjungovindan178]

Superb, It avoids the human error(s)

[@freazingflame]

works great. I really need this when looking through massive amounts of data. Thank you, brother.

[@gabrielahermaya3773]

Thank you dear! Clear, simple and useful video!

[@AbdelrahmanMohamed-mm7iy]

Excellent tutorial, Tanks!

[@sameerk12982]

Thank you very much for this easy to understand and very helpful tutorial...exactly what I was needed.

[@dadondada41]

Awesome. Tried it and it works. I only needed rows highlighted so i ignored the 'column' part of the formula and it worked perfectly. Thanks a mil.

[@TheCoolsatishkumar]

it is helpful. presenting excel on teams is a difficult task on both side. with this, it will be easier.

[@PhillipBailey]

Thanks a million! The earlier versions of excel seemed to be better at this or maybe it's just my eyes getting older. Works fantastic!

[@MCSsergio]

Thank you so much for this tip! It will help me in my day to day work with spreadsheets.

[@JEDss454]

Great video - clear and simple; very useful, and works exactly as you showed when I tried it myself. Thank you!

[@trendsetter4079]

So ease in the explanation. Great teaching skill. Thank you for your efforts.

[@grzegorzdziewiaty7190]

That's very helpful. Thank you for sharing this tip.

[@AnonymousHippo5]

This video is very good. How could you prevent highlighted column and row (orange color in this example) to NOT show up when you are printing the document out?

[@anandk1079]

thanks ji, simple and beautiful explanation

[@dav4238]

WOW Excellent Tutorial! Thanks you. Very helpful.

[@wasifabbas8160]

Its really great video which resolve many issues. My query is that when condition in or formula will return false as in every case, result will be true.

[@imsha569]

Saved my time. Thank You

[@vtechnolearning3696]

kzbin.info/www/bejne/pnzSqomDgZmemLc

[@marshallkrebs963]

Thank you - this tutorial helped me make an amped up stat book for my HS lacrosse team! Well done!

[@marius964]

Great tutorial, thank you. Is simple to implement, is unobtrusive and above all it works! Thank you

[@SteS]

A master of his trade! Kudos to you.

[@rajata2819]

First of all thankyou so much for this and one more thing i would ask if you could tell one more formula which make current cell color different than the entire row and columns color that would make thing more clearly for me thanks in advance.

[@usamanaeem8737]

Exactly what I was looking for

[@potentiallyunfulfilled5544]

Wow! Thank very much! This was perfect for what I needed and you made easy to follow and execute on my own. Well done.

[@trik3002]

Thanx a ton mate. Good luck and best wishes.

[@tawsifislam-accafinalist2796]

Your every video is so precise. God bless You for sharing your knowledge. keep up the good work.

[@chinx0925]

Thank you so much for this. I have a problem though. When I inserted a row on the spread sheet. The highlight is not working on the inserted row

[@babun9353]

Very nicely explained. Loved that thanks

[@sleepingbeauty9809]

This was extremely helpful!!!!!! thank you

[@AdequateMinds]

Great Video, could you please advise why this VB process has to be repeated after reopening the file Thanks

[@diassetefle9775]

It's very useful, thanks a lot for this tutorial.

[@rohankaranjkar2813]

excellent tutorial best for beginners

[@RezaurRahmanEther]

Thanks a lot, Bro. Exactly what I was finding.......

[@fady04]

Thanks, buddy you made my day. you are a pro

[@cristcar1574]

Thank you, this was helpful!

[@ipeceurope935]

Fantastic! Thank you. You are a wonderful teacher!

[@Ganeshacanasho]

This was of great help! Thanks!

[@TheALevelMathsTutor]

Thanks for this fantastic tutorial. You explained the steps very clearly and it was very quick to implement in the school markbook I am using. However, when I initially copied the formula for the conditional formatting, I had to replace the quotation marks. Perhaps it could be that my keyboard uses UK rather than US English, but the ones in the tutorial look like this: `` and needed replacing with this: ". I hope that makes sense. Thanks again, anyway.

[@EZStudRack]

Thanks for the " tip. I could not get mine to work till I replaced the ".

[@KetoJustRight]

Thanks so much! I searched for a couple of hours on how to make this work and this tip did the trick for me!!

[@neochuck]

Thank you! This one should be the top comment.

[@RNCHFND]

Thanks for the tip! That was my issue

[@ashoksahu9546]

Sir, Very very excellent video. Thank so much. This is so useful things.

[@robinmullen3019]

Fabulous instructions!

[@trumpexcel]

Glad you found the video helpful 🙂

[@richardgordon]

Just brilliant! Thank you for sharing your knowledge and ideas.

[@MrGIMPdude]

beautiful explanation. well done!

[@trumpexcel]

Glad you found the video useful!

[@Zilllon]

Thank you for this! Very helpful even 3 years later. Did you ever run into any copy and paste issues? I can't seem to copy and paste now. Thanks!

[@Zilllon]

I just "turned off" the VBA and copy-paste worked again. Thanks!

[@ricardopalomojr2349]

I did this and I ran into a small issue. Can you help? So after I did this when I go to print my spreadsheet part of my spreadsheet doesn't print, it gets cut off. When printing I am selecting to print only the selected area because I only need that part to print for my team. Not sure if there is another formula that needs to be added to allow this to print properly. Thank you. The highlighting part works great by the way. This was super helpful. As soon as I figure out the printing part this will be perfect.

[@techneko2007]

AMAZING tutorial , Definitely earned yourself a SUB

[@Robby-Rob-Robertson-III]

This is amazing and simple, love it good job.

[@alvaroromero2419]

You are going to be my God since today!

[@AllanMurillo-s7u]

Hello good day. Having fun viewing your video presentation in autohighlight data in column and rows. Question? How can I make this with my Mac? Thanks

[@sanjeve8769]

Great Tutorial - very usual

[@pnkmjd48]

Thank you so much for sharing this.

[@hariprasadsharma3617]

what a wonderfull trick sir. I found it nowhere. Thanks a lot

[@shamrao5672]

Excellent Sir ... Thank You Very Much

[@kayyumforu]

Excellent! How can we have this for any sheet in excel?

[@ErwinHandokoMD]

This is very helpful! Also, your video instruction is very easy to follow. Thanks.

[@richardmarkham4910]

Fantastic. BUT. My Excel doesn't except the suggested formula: "=OR(CELL("col")=COLUMN(),CELL("row")=ROW())" Do you have any advice?

[@dannydominguez6815]

Thanks, you got a new subscriber

[@AlanSchlintz]

Great tool. I modified the conditional formatting to do two things. The first was to show the normal fill formatting of the highlighted cell. It makes it a bullseye without any changes. I use fill on certain cells to mean different things. This lets me see and change that without confusion from conditional formatting. The second part was I set cell A1 as a flag to turn it on or off. It's on if cell A1 = 1, off for any other value. It looks like this: =AND($A$1=1,OR(CELL("col")=COLUMN(),CELL("row")=ROW()),NOT(AND(CELL("col")=COLUMN(),CELL("row")=ROW())))

[@r.i.4259]

subscribed , perfect video, thank you

[@BGLoscar]

Amazing work.. You have no idea how much you helped me! I really appreciate it!

[@thezionzion]

Awesome and Informative! Thank you!

[@maybahag]

great! serves my purpose! thanks.... but how can I still edit the sheet? I can't add rows, copy and paste.... thanks

[@danielchiu3755]

Hi thanks for the idea. Noted the method you described is applicable to one worksheet only. Can you advise how to make this auto-hight available to all worksheet opened?

[@kvnagendra5354]

*Bro ur videos are osm, altruistic and admirable, plz don't stop making videos, I'm big fan of ur teaching*

[@Raj-cf6fe]

Helloo Good video But question if there is any other coloured cells, will this work?

[@LotfyKozman]

Vary useful and practical video. Thanks a lot.

[@kathymcbee6371]

Excellent tutorial!!!

[@divyshah6794]

Perfect solution for my query. Thank you so much.

[@johnslawkawski5160]

Thank you for the great work. Can you tell me if you'd be able to toggle the highlighting option ON and OFF?

[@GeorgeAJululian]

Very helpful many thanks Sir

[@goddessk3740]

SO GRATEFUL FOR THIS VIDEO

[@lazenbytim]

Hi thanks for your great video however when I press f9 this just locks my computer and doesnt do as you say in the video. Do I need to press something else before f9?

[@YeaNo.]

So so helpful thank you.

[@teoxengineer]

Thank you for this good and beneficial tutorial. If we choose an array, how does conditional formatting act?

[@myal1717]

Thank for the video... I didn’t get to know to make column and row colour different

[@Evil_Morty__]

Thank you so much! I seem to be losing my copy and paste abilities after executing the application.calculate code. is there a work around for this?

[@Dirtchamp14]

Excellent addition to my sheets! thanks for showing us. I do have one question... is there any way to get the colors to stop at the intersection point?

[@behyuhuan]

Hi, Thank you for your helpful video. How do I do this in Excel for Mac? ie. When I open the developer tab and select the workbook, I get the error message: Variable uses an Automation type not supported in Visual Basic

[@Jrod_FPV]

Update: After a quick test, this does not help me. When clicking away from excel, it seems the proper row/ cell do not stay highlighted, the highlighting moves to another cell which is wrong. great video. This is very helpful . THANK YOU!!! Though in my opinion this should be built in functionality to Excel! What a pain when i have 2 workbooks open and i'm trying to read one while updating data in the other, Excel does not how the highlighted cell if it is not the active window on the desktop!

[@susantasahukar7391]

Nice info.. I am thinking about scalability... Can we give an option to the user whether if he wants this feature to be activated in active workbook? bcz in reality, we work with multiple workbooks so it's not possible to apply conditional formatting in all workbook. My requirement is that create an add-in where one checkbox will be there in an userform. If the user tickmark the checkbox then "highlight the Active Row and Column in a data range" script should run. Hope you understood my query. If possible please make a video on it.