Highlight every other row

What are the ISEVEN and ISODD functions?

The ISEVEN and the ISODD function, together with the ROW() function, are useful tools in conditional formatting that to highlight every other row in a table. This technique is also known as the Zebra striping.

Formula

To highlight ODD rows, use the following generic formula;

=ISODD(ROW())

While, to highlight EVEN rows, use the following generic formula;

=ISEVEN(ROW())

Explanation

Note: This post focuses on the ISODD function. The ROW() function would return the Row number while the ISODD function would return “TRUE” if the row number is ODD (i.e. 1, 3, 5 etc.).

When “TRUE” is return, the rule is triggered and the ODD rows are highlighted.

Example

How to use the ISODD(ROW()) functions to highlight every other row.

The following steps should be taken;

  1. Open your Microsoft Excel and create a new Excel workbook by clicking on “Blank workbook”.

Figure 1. Create a blank sheet

  1. In the new spreadsheet (or an already open Excel spreadsheet), highlight the entire area that you want to apply the Zebra striping effect to. Row 1-14 would be highlighted in this example.

Figure 2. Highlight the area for Zebra striping

  1. Click on the “Home” tab, find the “Conditional Formatting” and click on it.

Figure 3. Click Conditional Formatting

  1. Find and click “New Rule” in the drop-down list that emerges.

Figure 4. Click New Rule

  1. A dialogue box titled “New Formatting Rule” should be open by now, Choose “Use a formula to determine which cells to format”.

Figure 5. Use a formula to format

  1. Insert “=ISODD(ROW())” in the area given and click on “Format”.

Figure 6. Insert formula

  1. Another dialogue box titled “Format Cells” would be open. Click on the “Fill” tab, pick a color of your choice, and Click “OK

Figure 7. Pick a filling color

  1. You should be back to the previous box. Click “OK” finally to apply your New Rule.
  2. Our final sheet looks like this.

Figure 8. Sample final sheet

Note

  • *The Zebra effect can also be achieved using the ISEVEN function. The ROW() function would return the Row number while the ISEVEN function would return “TRUE” if the row number is EVEN (i.e. 2, 4, 6 etc.).
  • When “TRUE” is return, the rule is triggered and the EVEN rows are highlighted.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar