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.
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…
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.
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.
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.
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.
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, Facebook and Twitter.
Video
Lastly, if you prefer you can watch an explanation of this tip in the video below.
