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

Подписаться 369 тыс.

Просмотров 342 тыс.

50% 1

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: ru-vid.com...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

Опубликовано:

19 сен 2018

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 366

@kristylazo3403 2 года назад

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🤐)

@jilldunning8870 Год назад

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

@loogan7440 3 года назад

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())

@MarcFireHart 2 года назад

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.

@meetkcs 4 года назад

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.

@JEDss454 5 лет назад

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

@trustabyss 5 лет назад

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

@heathstapleford5044 4 года назад

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

@marius964 5 лет назад

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

@pranavshrestha4953 4 года назад

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

@imsha569 2 года назад

Saved my time. Thank You

@vtechnolearning3696 Месяц назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pKoyVML4kbU.html

@MGeXpansion 4 года назад

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 :)

@freazingflame 4 года назад

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

@KetoJustRight 4 года назад

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!

@mahamohan1 5 лет назад

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!

@dadondada41 4 года назад

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.

@narupa9 3 месяца назад

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 3 месяца назад

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

@potentiallyunfulfilled5544 3 года назад

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

@PhillipBailey 4 года назад

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!

@gaelmaury4253 5 лет назад

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

@ErwinHandokoMD 4 года назад

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

@ipeceurope935 5 лет назад

Fantastic! Thank you. You are a wonderful teacher!

@trendsetter4079 3 года назад

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

@Ethikly 4 года назад

Amazingly useful. Thank you for the great tutorial!

@utube8108 Год назад

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

@sameerk12982 3 года назад

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

@techneko2007 3 года назад

AMAZING tutorial , Definitely earned yourself a SUB

@andriikulishov3371 3 месяца назад

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 3 месяца назад

@@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 3 месяца назад

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)

@hamdanasr8574 4 месяца назад

Wow, you are an AMAZING instructor ❤

@trumpexcel 4 месяца назад

Thank you so much for the kind words 😊

@goforit2112 4 года назад

Worked Perfect! Thank You, easy to follow video.

@kvnagendra5354 5 лет назад

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

@SteS 5 лет назад

A master of his trade! Kudos to you.

@shubhamgupta4U 4 года назад

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

@tawsifislam-accafinalist2796 5 лет назад

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

@richardgordon 3 года назад

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

@et1965 4 года назад

Great video. Thank you for being clever and sharing with the rest of us :)

@rljordan91 Год назад

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

@AlanSchlintz 2 дня назад

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())))

@divyshah6794 4 года назад

Perfect solution for my query. Thank you so much.

@ajayshah2274 4 года назад

Thank you for your efforts. It's really a handy technique.

@ashoksahu9546 5 лет назад

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

@gabrielahermaya3773 4 года назад

Thank you dear! Clear, simple and useful video!

@MrAsifsonu Год назад

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

@BGLoscar 3 года назад

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

@diassetefle9775 4 года назад

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

@jwalkerbg60 3 года назад

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.

@santoshkiran5858 4 года назад

I Love You So So So Much for this technique you're a super hero to me bro ❤️❤️❤️❤️

@roberthofberger8847 5 лет назад

Great job! Appreciate your work.

@ThinkersThinkThoughts 4 года назад

This is so elegant. Wonderful :)

@mapanalysis4509 5 лет назад

Excellent video, thank you for sharing

@sleepingbeauty9809 6 месяцев назад

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

@mishraamukesh 5 лет назад

Exceptional brilliant.. Very useful Thank you very much

@LotfyKozman 5 лет назад

Vary useful and practical video. Thanks a lot.

@prowidia 5 лет назад

Thanks, I appreciate you work. very much

@dayalanpadayachy 5 лет назад

Hi, Thanks for a very useful technique :-)

@MeredithHurstongplus 3 года назад

Just what I needed. Thank you!

@goddessk3740 2 года назад

SO GRATEFUL FOR THIS VIDEO

@KemarConstantine 5 лет назад

Great video. Easy to follow.

@marshallkrebs963 3 года назад

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

@fernandosanchezmontiel6192 5 лет назад

Thanks a lot, very helpful and you explain very good!! tumb up!

@sanjeve8769 9 месяцев назад

Great Tutorial - very usual

@slupien35 2 года назад

Thank you for the easy to follow guide

@jeovannylopeztoribio4299 4 месяца назад

Amazing perfect explication from start to finish !

@trumpexcel 4 месяца назад

Thank you 😊 Glad you found the video useful!

@Miky198007 4 года назад

This video deserves a big Like :D

@MrElliottjc 4 года назад

Simply Brilliant!

@arjungovindan178 4 месяца назад

Superb, It avoids the human error(s)

@trik3002 Год назад

Thanx a ton mate. Good luck and best wishes.

@babun9353 4 года назад

Very nicely explained. Loved that thanks

@thezionzion 4 года назад

Awesome and Informative! Thank you!

@rohankaranjkar2813 Год назад

excellent tutorial best for beginners

@Robby-Rob-Robertson-III Год назад

This is amazing and simple, love it good job.

@kathymcbee6371 Год назад

Excellent tutorial!!!

@dav4238 8 месяцев назад

WOW Excellent Tutorial! Thanks you. Very helpful.

@myal1717 5 лет назад

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

@Tubiquitous2 5 лет назад

Helpful. Thank you!

@larrylahti2864 5 лет назад

Great idea, This can be very useful.

@RezaurRahmanEther 3 года назад

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

@rupendankhara3492 4 года назад

Hi this really helps. Keep up the good work.

@dannydominguez6815 2 года назад

Thanks, you got a new subscriber

@MCSsergio 10 месяцев назад

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

@cristcar1574 Год назад

Thank you, this was helpful!

@adaokeke573 3 года назад

This is very helpful. Thank you so much!

@trumpexcel 3 года назад

Glad you found it useful

@MrGIMPdude 4 года назад

beautiful explanation. well done!

@trumpexcel 4 года назад

Glad you found the video useful!

@fady04 4 года назад

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

@eharishkumargoud3159 5 лет назад

Awesome Bro... Keep posting videos

@grzegorzdziewiaty7190 Год назад

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

@r.i.4259 2 года назад

subscribed , perfect video, thank you

@TheCoolsatishkumar Год назад

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

@alvaroromero2419 4 года назад

You are going to be my God since today!

@shamrao5672 Год назад

Excellent Sir ... Thank You Very Much

@usamanaeem8737 Год назад

Exactly what I was looking for

@pnkmjd48 3 года назад

Thank you so much for sharing this.

@Msedk 3 года назад

Excellent! Thank you for the learning experience. :-)

@Ganeshacanasho 2 года назад

This was of great help! Thanks!

@robinmullen3019 4 месяца назад

Fabulous instructions!

@trumpexcel 4 месяца назад

Glad you found the video helpful 🙂

@kandisannicolas4538 8 месяцев назад

Great tutorial!

@solartechPH Месяц назад

THANK YOU MASTER

@SidharthMiddela 4 года назад

For Excel Mac users First save the file as "Enable with Macros". Then type the code mentioned in this video, you should be good to go. I am a Excel mac user and using VB 2012 with Office 365 subscription and it all works fine.

@tawsifislam-accafinalist2796 5 лет назад

Awesome video.. Really it will help me a lot