Below is the complete written tutorial, in case you prefer reading over watching a video. Conditional Formatting in Excel can be a great ally in while working with spreadsheets. A trick as simple as the one to highlight every other row in Excel could immensely increase the readability of your data set. And it is as EASY as PIE. Suppose you have a dataset as shown below: Let’s say you want to highlight every second month (i.e., February, April and so on) in this data set. This can easily be achieved using conditional formatting.
Highlight Every Other Row in Excel
Here are the steps to highlight every alternate row in Excel: That’s it!! You have the alternate rows highlighted. Now let’s take a step back and understand how this thing works. The entire magic is in the formula =MOD(ROW(),2)=1. [MOD formula returns the remainder when the ROW number is divided by 2]. This evaluates each cell and checks if it meets the criteria. So it first checks B4. Since the ROW function in B4 would return 4, =MOD(4,2) returns 0, which does not meet our specified criteria. So it moves on to the other cell in next row. Here Row number of cell B5 is 5 and =MOD(5,1) returns 1, which meets the condition. Hence, it highlights this entire row (since all the cells in this row have the same row number). You can change the formula according to your requirements. Here are a few examples:
Highlight every 2nd row starting from the first row =MOD(ROW(),2)=0 Highlight every 3rd Row =MOD(ROW(),3)=1 Highlight every 2nd column =MOD(COLUMN(),2)=0
These banded rows are also called zebra lines and are quite helpful in increasing the readability of the data set. If you plan to print this, make sure you use a light shade to highlight the rows so that it is readable. Bonus Tip: Another quick way to highlight alternate rows in Excel is to convert the data range into an Excel Table. As a part of the Excl Table formatting, you can easily highlight alternate rows in Excel (all you need is to use the check the Banded Rows option as shown below):
Creating a Heat Map in Excel. How to Apply Conditional Formatting in a Pivot Table in Excel. Conditional Formatting to Create 100% Stacked Bar Chart in Excel. How to Insert Multiple Rows in Excel. How to Count Colored Cells in Excel. How to Highlight Blank Cells in Excel. 7 Quick & Easy Ways to Number Rows in Excel. How to Compare Two Columns in Excel. Insert a Blank Row after Every Row in Excel (or Every Nth Row) Apply Conditional Formatting Based on Another Column in Excel
…but then I discovered Tables and have never done this again =ISODD(ROUNDUP(ROW()/4,0)) =AND((A$1=1),MOD(COLUMN(A$1),5)=0) =Mod(row($H1),15)=0 =AND(MOD(ROW(A1),4)=0,A1>0,A1<9999)
