How to shade alternate rows with Conditional Formatting 2


How to shade alternate rows with Conditional Formatting

 

I regularly work with large sets of data. One quick and simple “trick” I use ALL the time is to shade every alternate row in the data to make it easier to read.

If you look at the example below, by itself, it is a very visually unattractive spreadsheet, full of lots of data that is hard to read.

We can significantly enhance the readability of this information by shading every alternate row using Conditional Formatting.

Image - Original Data - Shade Alternate Rows with Conditional Formatting

Data can be hard to read without any formatting

 

Conditional Formatting

Simply put, Conditional Formatting automatically applies different formatting styles (colours, background shading etc) to your data based on criteria you select.

For this particular tip, we use Excel row numbers to work out which row to shade (or not).

Excel Tip – How to shade alternate rows with Conditional Formatting

Now to the tip:

Step 1 Select the first row of your data (cells A7 to E7 in my example below)

Step 2 On the Home tab, select Conditional Formatting, then click New Rule…

Image - Add a new Conditional Formatting Rule

Add a new Conditional Formatting Rule

 

Step 3 In the New Formatting Rule dialogue box, select the Use a formula to determine which cells to format

Step 4 In the Format values where this formula is true: box type the following formula:

 

=mod(row(),2)=0

 

The ROW() function brings back the row number in which the formula appears, so for the highlighted row in the image shown below, row() would return the value 7.

The MOD function returns the remainder of a number after it is divided by a divisor. Hence when we use row numbers and a divisor of 2, the remainder of every 2nd sum will equal 0 – that is, the MOD (or remainder) of row 1 divided by 2 equals 0.5, the MOD (or remainder) of row 2 divided by 2 equals 0, MOD row 3 = 0.5, MOD row 4 = 0 and so on.

 

Image - Conditional Formatting rule

Conditional Formatting rule

 

Step 5 Next, click the Format button and select a pale background fill. The colour that you select will become the colour that is applied to the background of every alternate row.

Image - Select a pale background fill colour

Select a pale background fill colour

 

Step 6 Click the OK button and OK again to apply the Conditional Formatting to your first row of data.

Step 7 Now that we have Conditional Formatting set up for your first row, we just need to copy this formatting to your remaining rows of data.

Step 8 In our example, select cells A7 through to E7 and click the Copy button (or CTRL+C).

Step 9 Now select your remaining rows of data and use Paste Special, Formats to paste the Conditional Formatting to this information.

 

Image - Paste Special Conditional Formatting Rules to remaining data

Paste Special Conditional Formatting Rules to remaining data

 

Your data will now have the Conditional Formatting rules applied to it and every alternate row should now be shaded making it far more readable.

Image - Every alternate row shaded

Every alternate row shaded

 

Nb: I “cheated” a little in the image above, as I also changed the title row to have a dark background, with a white font, to make it stand out from the rest of the data.

 

But wait, That’s not all…

Assuming you now want to shade every third row of data in your information. Simply modify the “=mod(row(),2)=0” formula by changing the 2 to a 3 (i.e. “=mod(row(),3)=0”). Or if you want every fourth row of data shaded, change the 2 to a 4 (i.e. “=mod(row(),4)=0”).

 

Ok that’s it for this tip, as always should you have any questions or queries about any information I have provided, let me know.

Till next time.

Cheers,
Brian

 

Please Share

If you liked this or know someone who could use it, please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

Download

You can download a PDF transcript of this tip by clicking the following link – How to shade alternate rows in Excel using Conditional Formatting.

You can also download a copy of the spreadsheet I used in this article so you can explore this tip further – How to shade alternate rows in Excel using Conditional Formatting spreadsheet.

Video

Lastly, if you prefer you can watch an explanation of this tip in the video below.