Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to create zebra stripes with conditional formatting

Video Transcript 

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.

=MOD(ROW())

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.

=MOD(ROW())=0

In later versions of Excel, you can use two special functions, ISODD, and ISEVEN in conditional formatting rules.

So, instead of the MOD formula, I could just use ISEVEN with ROW.


To shade odd rows, I just need to replace ISEVEN with ISODD.


If you want to shade columns instead of rows, you can just substitute the COLUMN function for the ROW function.


Of course, you could add more than one rule, to shade both columns and rows.

Finally, in case you ever want to create a checkerboard effect, you can use ROW and COLUMN together. The formula is simply ROW + COLUMN, wrapped in ISODD or ISEVEN.

=ISODD(ROW() + COLUMN())
Author 
Dave Bruns
I have been on your emailing list and your tips have made sense to me so I thought that would take the next step and really try and up skill myself with excel. - Maara
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course