Highlight Alternate Rows (or Every Third/Fourth Row) in Excel - Using Formula
Vložit
- čas přidán 17. 07. 2019
- In this video, I will show you how to highlight alternate rows in Excel (or how to highlight/color every second, third, or fourth row in Excel).
The trick is to use Conditional formatting with a formula that checks each cell for the row number and returns a TRUE for every second row and FALSE otherwise.
This TRUE/FALSE result is then used by Conditional Formatting to apply the specified formatting.
In this video, I use the MOD function to check the row number (which is given by the ROW function).
The formula used is =MOD(ROW(),2)=1
The above formula takes the ROW function result and divides it by 2 and returns the remainder. The possible result of the MOD function in this can be a 0 or a 1 (0 if the row number is even and 1 when it's odd).
It then checks whether the value is equal to 1 or not. Since the data starts from second row onwards, I am checking for 1. else I would have checked for 0.
This formula then returns a TRUE when the ROW number is either 3 or 5 or 7 or so on, and FALSE when it's even.
Conditional formatting then uses this to shade/color every second row in the dataset.
In case you want to highlight/color every third row, you change the formula to =MOD(ROW(),3)=1
This would highlight every third row instead.
Another quick way to highlight alternate rows in Excel is to convert the data into an Excel Table. An Excel Table automatically colors alternate rows.
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: czcams.com/users/trumpexc...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Thank you very much my dear professor for this wonderful video, and on this method the coolest.
You are an amazing Tutor.
Your way of teaching excel is superb.
God bless You for helping us with a good heart.
Thank you so much for the kind words. Glad you found the video useful :)
after selection how to extract only Highlights cells Data
Very nice way of teaching, any one can understand very well
Pronunciation is very clear too.For future videos please provide practice file too.just a request.
-Thanks
Very VERY nicoe solution. It's also good to see you on the screen. 👍👍👍
Thanks Oz.. It took so much effort to have the setup and get the video out. I need to learn how to do this better from you.
Thank you very much my dear Sir for this wonderful video and on this method the coolest.
THANK YOU!
Thanks for ur face my teacher.
Thanks for ur excel lesson because u make me shine.....
First time u on cam good to see.
Thank you very much for this video...
Very nice thanks
It was amazing to learn with you. Thanks for helping us learn! I have a scenario where I have data in Column B2 to H2 and Row 2 to Row 10. I want to highlight every 4th row, but I don't want to highlight the entire 4th row, only from Column D2 to F2. Is it possible to highlight in this way? The thing to remember is that the data is in Excel worksheet not in table
Thank you! :)
Wao...
You are my idol.
First time i have seen you at camera face...
Hi Sumit, thanks for sharing. Would be great to have an illustration of dynamic button for live charts, i.e. click by sales area button to reflect different live charts... Thanks.
Thanks
Dear sir, thanks
Tq tq so much bro
Nice
Hi just watched your video and applied to one of my sheets which i have been trying to get to highlight every block of 10 merged cells which contain days of the week every 28 days when i put 28 in the formula it highlights every `14 days if i change to 56 it then works for 28 days please could you advise thanks Dave
Hi Sumit.. great tip.. thanks! One trick I employ is to use expanding ROWS() instead of ROW() inside MOD() which protects the integrity of the table highlighting, in case rows are inserted above the table. So, in your example, I would use: =MOD(ROWS($A$1:A2),2)=1 to have the first row of the table (row 2 of the worksheet) as FALSE or no highlight and then alternating from there. If I insert rows above the column headers to move the table down, the first row of the table always remains as non-highlighted. Using the ROW() method, each insertion would switch the result of the MOD() formula and change the position of the highlights. It's a slight difference from your approach, but works for me in cases where I might ultimately end up moving the position of the table with inserts. If no table move is anticipated, then your method is easier to write. Always multiple ways to solve things with EXCEL. Thanks for sharing your vast knowledge in your great videos and on your web site and for providing the inspiration for me to tinker and create new solutions. Thumbs up!
Wayne Edmondson , how can I highlight double alternating rows, highlight 1&2 then 5&6 then 9&10 etc
@@briancowan21 Hi Brian.. thanks for your question. Building on what I wrote above, the following formula will work: =MOD(ROWS(A$2:A2)-1,4)1. I hope this helps. Good luck with your project!
How do you do dynamic alternative?
Plz Reply On this Comment 🙏🙏
In Your Playlist Section
One playlist that is Basic to Advanced....Are these Full Excel Videos which Covers the Whole Info And Use Of MS-Excel??
after selection how to extract only Highlights cells Data
Hi Sumit. Can you please fix the downlink link for your Google Sheets Leave Tracker? The link is broken and I've tried reaching out to you about this on multiple platforms. Hoping that you can see this and update the link. Myself and others really need this tracker.
What about highlight 5 rows, every 5 rows?
Hi Dear, pl make videos on MS Word, & Power Point
1st time view
Thanks