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/highlight-acti...
Download file: www.dropbox.com/s/jxcu38sm4br...
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/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ 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: kzread.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Пікірлер: 364
Great tutorial, thank you. Is simple to implement, is unobtrusive and above all it works! Thank you
Great video - clear and simple; very useful, and works exactly as you showed when I tried it myself. Thank you!
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.
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.
You're awesome, I'm gonna try this! My eyes play tricks on me when looking at long rows. This is the solution!
works great. I really need this when looking through massive amounts of data. Thank you, brother.
The only useful VBA code that I know I can do by myself. Wonderful tutorial as always. Thanks!
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!
Wow! Thank very much! This was perfect for what I needed and you made easy to follow and execute on my own. Well done.
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.
Amazingly useful. Thank you for the great tutorial!
THIS IS AWESOME!!!! Financial auditor here and my spreadsheet are gigantic. This is literally going to save me multiple hours a week probably.
Fantastic! Thank you. You are a wonderful teacher!
So ease in the explanation. Great teaching skill. Thank you for your efforts.
Thank you for this video and the quality of your explanation. You are very pedagogue
Worked Perfect! Thank You, easy to follow video.
Thank you very much for this easy to understand and very helpful tutorial...exactly what I was needed.
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!
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.
This is very helpful! Also, your video instruction is very easy to follow. Thanks.
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())
*Bro ur videos are osm, altruistic and admirable, plz don't stop making videos, I'm big fan of ur teaching*
Genius!!! This is the perfect workaround for the inability to highlight the row and column headings.
A master of his trade! Kudos to you.
Useful things, I often require this selection but didn't search ever before. Now whenever I need it, surely I will do it
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
2 жыл бұрын
2018 was 4 years ago???? 😳 time flies
@kristylazo3403
2 жыл бұрын
@@MariyamSharief1711 lol, at first I thought I miscalculated..lol . Yes, time flies as we get older (I'm showing my age🤐)
Your every video is so precise. God bless You for sharing your knowledge. keep up the good work.
Thank you for your efforts. It's really a handy technique.
AMAZING tutorial , Definitely earned yourself a SUB
Great video. Thank you for being clever and sharing with the rest of us :)
Just brilliant! Thank you for sharing your knowledge and ideas.
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
2 жыл бұрын
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
2 жыл бұрын
@Princeton Lucian instablaster ;)
@princetonlucian802
2 жыл бұрын
@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
2 жыл бұрын
@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
2 жыл бұрын
@Princeton Lucian Happy to help :)
Perfect solution for my query. Thank you so much.
Sir, Very very excellent video. Thank so much. This is so useful things.
It's very useful, thanks a lot for this tutorial.
Great job! Appreciate your work.
Excellent video, thank you for sharing
best tutorial on this concept yet, and very clean formatting. thanks!
Thank you dear! Clear, simple and useful video!
I Love You So So So Much for this technique you're a super hero to me bro ❤️❤️❤️❤️
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!
Thanks, I appreciate you work. very much
This is so elegant. Wonderful :)
Amazing work.. You have no idea how much you helped me! I really appreciate it!
Just what I needed. Thank you!
Exceptional brilliant.. Very useful Thank you very much
Vary useful and practical video. Thanks a lot.
Hi, Thanks for a very useful technique :-)
Wow, you are an AMAZING instructor ❤
@trumpexcel
3 ай бұрын
Thank you so much for the kind words 😊
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
2 ай бұрын
Thanks Narupa... Glad you found the video helpful 🙂
Great video. Easy to follow.
Thank you so much for this tip! It will help me in my day to day work with spreadsheets.
This video deserves a big Like :D
Simply Brilliant!
Thank for the video... I didn’t get to know to make column and row colour different
Thanks a lot, very helpful and you explain very good!! tumb up!
This is amazing and simple, love it good job.
Very nicely explained. Loved that thanks
Saved my time. Thank You
@vtechnolearning3696
29 күн бұрын
kzread.info/dash/bejne/on-j27iGfJfRkrg.html
Awesome and Informative! Thank you!
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
3 ай бұрын
Thank you so much for the kind words Andrii 😊 Glad you found the video helpful!
@iZacq
2 ай бұрын
@@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
2 ай бұрын
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)
Thank you - this tutorial helped me make an amped up stat book for my HS lacrosse team! Well done!
Thank you for the easy to follow guide
WOW Excellent Tutorial! Thanks you. Very helpful.
Great idea, This can be very useful.
Thanks, buddy you made my day. you are a pro
Thanks man. You are first person that i have subscribed for excell. Solved my problem. Stay blessed always
That's very helpful. Thank you for sharing this tip.
Superb, It avoids the human error(s)
Helpful. Thank you!
Hi this really helps. Keep up the good work.
Excellent video................ ... this video resolved my biggest headache to read excel data...Thank you so much for the video
Exactly what I was looking for
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?
You are going to be my God since today!
Thank you so much for sharing this.
This was extremely helpful!!!!!! thank you
Thank you for this good and beneficial tutorial. If we choose an array, how does conditional formatting act?
This is very helpful. Thank you so much!
@trumpexcel
3 жыл бұрын
Glad you found it useful
it is helpful. presenting excel on teams is a difficult task on both side. with this, it will be easier.
Amazing perfect explication from start to finish !
@trumpexcel
3 ай бұрын
Thank you 😊 Glad you found the video useful!
Awesome Bro... Keep posting videos
Excellent! Thank you for the learning experience. :-)
Thank you, this was helpful!
This is a very useful video! Thank you. Do I have to set this rule and macro every time I open a new file of excel?
This was of great help! Thanks!
Awesome video.. Really it will help me a lot
SO GRATEFUL FOR THIS VIDEO
Very useful -- thanks!
Excellent Sir ... Thank You Very Much
Thanks a lot, Bro. Exactly what I was finding.......
excellent tutorial best for beginners
beautiful explanation. well done!
@trumpexcel
3 жыл бұрын
Glad you found the video useful!
Great Tutorial - very usual
Wonderful tutorial!!!
subscribed , perfect video, thank you
Very helpful many thanks Sir
Great. Thanks a lot. 👍
Perfect! Thanks!
So so helpful thank you.