Conditional Formatting in Excel.

Visual cues help us grasp situations at a glance. When driving, for example, colored lights tell us whether to stop, go or pull over so emergency vehicles can pass. Without standardized signals we could never read the conditions as quickly. The same is true with large data sets. That is why Excel’s conditional formatting is so useful — it lets us set up easy-to-read signals to highlight our data.

How It Works. Conditional formatting changes cell appearance based on rules we specify. For example, it can highlight the highest or lowest values in a list or flag duplicate entries. Best of all the formatting updates automatically as the data changes.

How It’s Done. Everything is under the Conditional Formatting button on the Home ribbon.¹ The following example formats a data set so that the two highest and two lowest values are highlighted:

1. Select the cells to be formatted;
2. Press the triangle on the Conditional Formatting button to display the menu;
3. Choose Top/Bottom Rules and then Top 10 Items;
4. When the dialogue box opens, change the number 10 to 2 and pick the Green format;
5. Repeat steps 3 and 4, but this time pick Bottom 10 Items and choose the Red format.

Now the cells with the two highest values will be green and those with the two lowest will be red.

Applying Multiple Conditions. Multiple rules can be applied to the same data. For instance, the high/low example uses two: one for the top two values and another for the bottom two. So long as there are at least four different values in the list, the criteria are mutually exclusive and both rules can never apply to the same cell.

But suppose we want to flag both duplicates and high/low values. Here both rules will apply any time a high or low value is also a duplicate. Since Excel’s default formats for highs/lows and duplicates both use fill and font color, we need to indicate which format to use when more than one rule applies.

Figure 1 shows two solutions. The red arrows indicate where the number in a cell is changed from 100 to 200. The value 100 was already one of the top two in the list, but when changed it also becomes a duplicate. The solution on the left is to use fill color for the high/low rules and font color for the duplicates rule. When the number 100 is changed to 200 the font turns red because the cell now contains a duplicate while the fill remains green since the new value continues to be one of the top two in the list.

To specify these formats, pick Custom Format when defining the rules. Then choose the desired attributes from the Format Cell dialogue box.

The solution on the right is to specify which rule takes precedence in a conflict. Here we picked yellow fill for duplicates, green for top values and red for bottom values. The duplicates rule is given priority so that when the value 100 is changed to 200, the fill changes to yellow since the duplicates rule has precedence.

Rule priority is specified in the Conditional Formatting Rules Manager. To apply the setting, select the cells to be formatted and choose Manage Rules from the bottom of the Conditional Formatting menu. Rules at the top of the list take precedence over those below them.

Formatting Based on Data in Other Cells. Sometimes we may want to format one cell based on values in others. For example, an accounts receivable person may want a client’s name to be yellow if an invoice has been outstanding more than 30 days and red after 45.

This requires two rules: one for values greater than 30; another for those over 45. The following steps create the over 30 rule:

1. Select the cells to be formatted (A2 to A6 in the example);
2. Choose New Rule from the Conditional Formatting menu;
3. Pick “Use a formula …” as the rule type;
4. Click in the formula box;
5. Enter the formula “=B2>30” (B2 is the cell containing the first entry for Days);
6. Click the Format button and specify the desired appearance; and
7. Click OK.

Repeat the same steps to create a rule for over 45 days. The formula should be “=B2>45”. Then open the Manage Rules dialogue box and make sure the 45 day rule is on top.

This example may not seem very useful since we could easily apply these formats to the number of days in column B using Excel’s built-in conditional formats. In the real world, however, the account aging data could be far away or even on a different sheet. In fact, we could build a conditional format formula that computed the days outstanding from the invoice date and TODAY function and tested whether that amount was greater than 30 or 45.

Absolute or Relative Cell Reference? In the account aging example, the formula B2>30 is the test for cell A2. For cell A3, however, the test should look at the number in B3, for A4 to the number in B4 and so on down the list. Excel will make these adjustments automatically if we enter the equation for the first cell in the list (A2 in the example).

Sometimes, however, we don’t want the referenced cell to be adjusted like that. For example we want to compare every entry in a list to a goal specified in B2. To “lock” a cell reference so it won’t be adjusted, put dollar signs in it, for example $B$2.² This is called an absolute cell reference; entries without dollar signs are called relative cell references.

* * * * *

The old adage that one picture is worth a thousand words has never been truer than today. Our ability to gather data seems to be growing exponentially while unfortunately our ability to analyze it does not. In an effort to close the gap, scientists and business people increasingly turn to data visualization tools to summarize and highlight key information. Excel’s conditional formatting provides a convenient and powerful tool for doing this.

-----

1. Applies to Excel 2007 and newer. In Excel 97 to 2003 conditional formatting is on the Format menu.
2. The entry $B$2 locks both the row and column references. $B2 locks only the column and B$2 only the row.

This article originally appeared in our free semi-monthly newsletter. To receive future issues, please add your name to the subscription list.