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 https://trumpexcel.com/learnexcel/
Paid Online Training https://trumpexcel.com/exceltraining/
Best Excel Books: https://trumpexcel.com/bestexcelbooks/

⚙ Gear I Recommend:
Camera https://amzn.to/3bmHko7
Screen Recorder techsmith.z6rjha.net/26D9Q
USB Mic https://amzn.to/2uzhVHd
Wireless Mic: https://amzn.to/3blQ8uk
Lighting https://amzn.to/2uxOxRv

Subscribe to get awesome Excel Tips every week: https://www.youtube.com/user/trumpexc...

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial