An Example of Zebra Striping in Excel

Using Conditional Formatting for Zebra Striping in Microsoft Excel

Zebra Striping in Excel is a useful way to make it easier to differentiate between long (page-wide) rows of data especially when each column stores data which looks very similar. Simply put, you use alternating colors to define rows. I’ve found this to be most useful when printing picklists or other lists for use in our warehouse.

Achieving this effect is simple in Microsoft Excel with Excel’s Conditional Formatting tool. In any version of Office after 2003, click on “Conditional Formatting” in the “Home” ribbon and create a new rule. Here the instructions diverge slightly: In Office:Mac choose “Classic” style then “Use a formula to determine which cells to format”, in Office for Windows choose the “Use a formula to determine which cells to format” Rule Type.

At this point the instructions converge again: type in the following formula into the formula box:

=MOD(ROW(),2)

After this, in Office for Windows click on “Format…” to define the format you want to use for every other row. In Office:Mac click on the “Format With” drop down and choose “Custom Format” to define your format.

The formula above uses Modular Arithmetic (the MOD() function) with the row number (the ROW() function gets the current row) as the input and 2 as the divisor. If the result is greater than 0 (any uneven row since uneven numbers can’t divide cleanly by two) the row will be formatted with your defined format, otherwise the result is 0 and the row is left with its current format.

 

Leave a Reply