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.
Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel
This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.
You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/
#Excel #ExcelTips #ExcelTutorial