Alternate Row Shading

Alternate Row Shading – an easy way to enhance readability, attractiveness, and style

Sometimes, the simplest edits can greatly enhance your presentation. For example, refer to the 2 exhibits below.

When presenting a sizeable list of items with various columns, the reader may struggle at tracking all the information you are trying to convey. Adding alternate row shading helps the user track a specific row of data with ease.

Adding alternate row shading is quite easy. Just highlight the region where you want to add the shading, then go to Format > Conditional Formatting. Then choose the option to write in your own formula. Type in the following:

=mod(row(),2)=0

Then choose a fill color for your formatting. The exact steps vary between the different versions of Excel, but the general idea remains the same.

Let’s break down this formula. The “Mod()” function takes a number, divides it by the second number, and returns the remainder. The “Row()” function returns the current row. So what the formula does, is that it takes the current row, divides it by 2, and gives the remainder. The remainder will alternate between 1 and 0 depending on your row, since whenever you divide something by 2, the remainder can only be 1 or 0. If it is a 0, then perform the formatting, if it is 1, then do nothing. Keep in mind that you can edit the formatting however you desire. It does not have to be a fill, instead it can be a change in text color, font, borders, etc.

Suppose you want to shade 2 lines at a time, how would you do that? Use the following formula:

=mod(row(),4)>1

Then choose a fill color for your formatting. This formula takes the current row, divides it by 4. The remainders can either be 0, 1, 2, or 3. If it is greater than 1 (2 or 3), then shade it, else (0 or 1) leave it alone. As a result, 2 rows are shaded at a time.

If you want to shade every 4^th row, then use:

=mod(row(),4)>2

Then choose the formatting you want. You may need to adjust the number of rows before your exhibit begins to get it to shade the 4^th row. In other words, you may need to paste or delete rows around your exhibit to adjust the where the shading occurs.

To see these exhibits on a spreadsheet, please download it here.