In this video, we look at how to use conditional formatting, with a formula, to shade every other row in a table. This is sometimes called "zebra striping", and it's a cool trick.
In this video, we'll look at how to use conditional formatting to shade every other row in a table. This is sometimes called "zebra striping".
In this spreadsheet, we have a table of employees with a minimal amount of formatting. To get shading on every other row, I could just convert this table to a proper Excel Table using Control + T, then customize the format as desired.
This is a good option, especially if you want the table for other reasons as well.
However, you can also apply dynamic zebra striping with conditional formatting and a formula.
The classic formula for zebra striping uses the MOD function. MOD takes a number and a divisor, and then returns the remainder. So, if I use the ROW function, without a reference, to get number of the current ROW, and then use 2 as the divisor, the MOD function returns zero when the row is an even number and 1 if not.
If I copy this formula across multiple cells, you can see we get rows of ones and zeros. We can use these results directly in a conditional formatting rule, since 1 and zero are equivalent to TRUE and FALSE.
So, to add the striping, I can just create a new conditional format rule based on a formula...then add the formula, and set a fill color.
If you'd rather shade even rows, just add "= 0" to the formula to switch the logic.
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the firth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the...
Thank you very much for this resource. I've been visiting your site here-and-there for over a year now and I want to thank you for all of the valuable knowledge that you and your team have shared. - Neil