Excel 2010 - highlight every two rows

Highlight every other row

Excel 2010 makes it easy to add stripes to your worksheet to help with readability. Click Home | Format As Table (click here for help on this) and select a layout - instantly every other row is a different colour.

Every two rows

But what if you'd like to highlight every two rows? Sometimes this can be useful if data comes in pairs perhaps. In this case you can use conditional formatting. Here's an example:

In my example I have a table of data, the first row is my header (City, Country, County). I don't want my header to be included in the highlighting (stripes) so the first thing to do is:

1.  Select all the data excluding the header (as shown above)

2.  Click Conditional Formatting (on the Home menu)

3.  Click New Rule (as shown in the above screen shot)

4.  Click Use a formula to determine which cells to format

5.  Enter the formula:
=MOD(ROW()+0,4)<2

6.  Click the Format button and select a background colour (in my example above I selected blue)

7.  Click OK - you'll see something similar to the following:

Every two rows are highlighted blue! 

You can adapt this formula for different combinations of rows if you wish. Try it yourself and experiment to find what is useful to you.

References

Highlight every other row (stripes) for readability - use Format as Table.

http://office.microsoft.com/en-001/excel-help/format-an-excel-table-HA010013769.aspx