The bigger your spreadsheet gets, the harder it is to highlight the really important information or, indeed, to even find it. For example you might have a long column of percentages and maybe they change frequently as you update the supporting information. Wouldn't it be great to be able to track these changes at a glance?
Or, maybe you've even gone down a list of information and highlighted positive numbers with a green background and maybe negative numbers with red. How would you feel if Excel would do that for you, and update it instantly if the numbers changed? It can using a tool called conditional formatting... (and it’s easier to use than you might think!)
Let’s get started with an example and then expand on some of the possible uses. Look at the simple table that compares fiscal year 2007 to 2008 below:
Here we're interested in comparing this year’s results to the previous year. Again, keeping it simple, our results can be up, down, or level.
1. Select cells D2:D6
2. Clicking Format > Conditional Formatting… brings up the following dialog box:
Any changes we make will apply to all of the selected cells.
For the first condition make the following changes:
Cell value is, greater than, 0
Note that when ‘greater than’ is selected in the second drop down Excel deletes the 4th field.
Now that we’ve identified the first condition (regions that improved) we need to tell Excel how we want these to look. Click the button to bring up the familiar formatting choices that we typically use for formatting cells. Click the patterns tab and select a green color of your preference, then click OK. Back in the Conditional Formatting dialog notice that the preview of condition 1 now shows a green background.
Click the Add>> button in the Conditional Formatting box to add a second condition. Following the same logic, change the values to:
Cell value is, less than, 0 and format the pattern to be red. This time click on the fonts tab and change the font color to white. Click OK and review your preview to make sure it looks right.
Click OK and Excel evaluates whether the cells meet either condition 1 or condition 2.
Notice that the Mid-Atlantic region did not change year over year so D3 is unaffected by conditional formatting because it does not meet condition 1 or 2. You may be tempted to add an ‘equal to’ 0 as a third condition but this is unnecessary. You’ll quickly find that there are only 3 available conditions in conditional formatting which can limit some of your fancier ideas. In another post I'll demonstrate some really advanced examples and even how to edge out a 4th condition.
For now, we’ll add just one more level of complexity. You notice that the first pulldown in the condition definition gives you the options of ‘cell value is’ and ‘formula is.’ If we want to highlight the entire row of the region that had the biggest increase then we use ‘formula is’. And that’s the way to think about it. If you want to format the cell that you are evaluating like we did in column D then use ‘cell value is.’ Any time that you want a cell to change based on the information contained in a different cell, use a formula.
I have to admit though, as nice as this is, it really does get a bit more complicated, and usually requires a couple of tries to get perfect. And that’s why we’ll leave this example for our next post...
Comments