Excel Tip of the Week: Conditional Formatting

Today’s topic is conditional formatting.

This topic is too big to cover in a single post, so I’ll simply be showing one of my favorite tricks using conditional formatting. There will likely be more conditional formatting posts in the future.

In a nutshell, conditional formatting allows you to create rules whereby cells change color, font, fill color, etc. based on the value or placement of the cell.

The tip I’ll be showing you today will use conditional formatting to make a table much easier to read. Let’s say you have a table with a fair amount of data. Something like this:

Here is the same table with every other row highlighted to provide better readability:

Instead of individually highlighting every other row in the entire table, we can use conditional formatting to make Excel do it for us. Here are the steps:

1) Download the example spreadsheet here
2) Highlight the entire range of cells except for the headers (Bonus tip! To highlight without the mouse, use the arrow keys to place the cursor into cell A3, and then while holding CTRL and SHIFT press the right arrow followed by the down arrow.)
3) From the “Home” menu select “Conditional Formatting” and then select “New Rule” (PC keyboard shortcut Alt → H → L → N)
4a) PC: Select “Use a formula to determine which cells to format”
4b) Mac: Select “Classic” in the drop-down box for Style. Click the second drop-down box and choose “Use a formula to determine which cells to format”
5) In the formula box type this formula: =MOD(ROW(),2)=1 (Bonus explanation of the formula at the end for the super Excel nerds)
6a) PC: Click the “Format” button. Select the “Fill” tab and then choose a color. I usually go with a light grey or light blue. Click OK and then click OK again.
6b) Mac: Select the drop-down list for “Format with” and select “custom format”. Then select Fill, choose a background color (I like a light grey or light blue) and click OK

You should now see every other row highlighted just as the picture above shows. The BEST part about using this trick is that you are able to insert or delete rows and excel will automatically adjust the formatting to keep it looking correct.

I have to give a big shout out to Owen Alum James Granberry, MBA 2011, for showing me this trick.

NERD ALERT! Here is the explanation of how the formula works: Think back to elementary school math when you had to do division and the teacher would give you a problem like 47 divided by 6. The answer you would give was 7 with a remainder of 5. The remainder number is what the MOD formula returns. So if you typed in =MOD(47,6) the result would be 5.

The ROW formula returns just that, the row number in which the cell exists. So the formula we used in the example is saying, “take the row number this cell is in, divide it by 2 and if it has a remainder of 1 then fill it in with my desired color.” Awesome, huh?