Section 6.3. Conditional Formatting | Excel 2007[c] The Missing Manual

6.3. Conditional Formatting

A good worksheet highlights the most important information, thereby making it easy to spot. For example, if you look at a worksheet that shows the last year of a company's sales, you want to be able to find underperforming products without having to hunt through hundreds of cells . And even if you're not using Excel in the business world, you still need to hone in on key details in a spreadsheetwhether it's a budget-busting dinner in your monthly expense worksheet or a skipped week at the gym in your exercise log. All too often, these essential details are buried in an avalanche of data.

As you learned in Chapter 5, you can use formatting tricks to make important data stand out from the crowd . But the problem with formatting is that it's up to you to track down the cells that need to be formatted. Not only is this a time-devouring task, you also run into trouble when you start using formulas (as discussed in Part 2). Formulas let you set up elaborate calculations that link cells together, which means that a change to a single cell can cascade through your worksheet, altering data everywhere else. If you're highlighting important information by hand, you just might need to repeat the whole formatting process each time a value changes.

Fortunately, Excel has a feature that's designed to spare you the drudgery. It's called conditional formatting , and it allows Excel to automatically find and highlight important information. In this section, you'll learn to master conditional formatting to make sure important bits of data stick out for all to see. You'll also see how, with conditional formatting, you can use shaded bars and mini-pictures to give a graphical representation of different values, which is one of the Excel's funkiest new features.

Note: Conditional formatting has always been a favorite trick of Excel experts. However, Excel 2007 boosts conditional formatting with new power (you can use more conditions at once), better support (you can create popular conditions with a couple of clicks), and slick new frills (you can highlight data with pictures and shaded bars).

6.3.1. The Basics of Conditional Formatting

In Chapter 5, you learned how to create custom format strings. As you saw, Excel lets you create up to three different format strings for the numbers in a single cell (Section 5.1.4.4). For example, you can define a format string for positive numbers, a format string for negative numbers, and another format string for zero values. Using this technique, you could create a worksheet that automatically highlights negative numbers in red lettering while leaving non-negative numbers in black.

This ability to treat negative numbers differently from positive numbers is quite handy, but it's obviously limited. For example, what if you want to flag extravagant expenses that top $100, or you want to flag a monthly sales total if it exceeds the previous month's sales by 50 percent? Custom format strings can't help you there, but conditional formatting fills the gap.

With conditional formatting, you set a condition that, if true, prompts Excel to apply additional formatting to a cell. This new formatting can change the text color , or use some of the other formatting tricks you saw in Chapter 5, including modifying fill colors and fonts. You can also use other graphical tricks, like data bars (shaded bars that grow or shrink based on the number in a cell) and icons.

6.3.2. Highlighting Specific Values

One of the simplest ways to use conditional formatting is to use a little formatting razzle- dazzle to highlight important values. For example, consider the daily calorie intake log shown in Figure 6-10.

To apply conditional formatting, select the cells that you want to examine and format. Next , you need to pick the right conditional formatting rule. A rule is an instruction that tells Excel when to apply conditional formatting to a cell and when to ignore it. For example, a typical rule might state, "If the cell value is greater than 10,000, apply bold formatting."

Excel has a wide range of conditional formatting rules, and they fall into two categories (each of which has a separate menu):

Highlight specific values . If your cell contains numbers or dates, you can set a minimum, a maximum, or a range of values that should be highlighted. In the case of text, you can highlight cells that contain certain specific text, start with specific text, and so on. In the case of dates, you can pick out dates that fall within certain ranges (last week, last month, next week, and so on). To see all your choices, choose Home Styles Conditional Formatting Highlight Cells Rules.
Highlight values based on where they fall in a series . These options get Excel to highlight the top values, bottom values, or values that fall above or below average. To see your choices, choose Home Styles Conditional Formatting Top/Bottom Rules.

Figure 6-10. This worksheet tracks the calorie intake of Carolynne, the pet llama, over several weeks. Carolynne's owners have noticed a dramatic weight gain over the same period, but they're at a loss to pinpoint exactly when the overeating took place. Fortunately, conditional formatting can highlight the problem areas.

For example, here's how you can quickly pick out big eating days in the llama food table (Figure 6-10):

In the Caloric Intake column, select the cells .

Click the C column's header to select the whole column.
Choose Home Styles Conditional Formatting Highlight Cells Rules Greater Than .

A dialog box appears where you can set the cut-off number and the formatting (Figure 6-11).

Figure 6-11. Each time Carolynne the llama eats more than 10,000 calories , the cell is highlighted with a yellow shaded background.
Set your minimum value in the text box on the left .

In this case, use 10000.

Note: Usually, conditional formatting compares a cell value to a fixed number. However, you can also create conditions that compare the cell value to other cells in your worksheet. To take this step, select the text box where you'd normally enter the comparison number, and then click the worksheet to select the cell that Excel should use. Excel automatically inserts a cell reference (like $D$2 for cell D2) into the box.
Choose the type of formatting from the list box on the right .

You can choose from several presets (like Red Text, Red Border, Red Fill with Dark Red Text, and so on), or you can define your own formatting.

To define your own format settings, choose Custom Format. An abbreviated version of the Format Cells dialog box appears. A few settings are disabled, because they can't be applied conditionally. For example, you can't conditionally change the font or font size , but you can conditionally set other font characteristics like the use of bold, italics, and underline. Aside from these limitations, the tabs are exactly the same as the ones you're familiar with from the full-blown Format Cells dialog box (Section 5.1). Click OK when you're finished choosing your format options.

Note: Imaginative Excel fans can do a lot with the Format Cells dialog box and conditional formatting. For example, you can highlight specific values by drawing a border around them, adding a different color fill, or changing the number format to add more decimal places.
Click OK .

As soon as you click OK, Excel evaluates the conditions and adjusts the formatting as needed. Every time you open your spreadsheet, or change the value in one of the conditional cells, Excel evaluates the condition and adds or removes the formatting as required.

Figure 6-12 shows the result.

Tip: To remove any type of conditional formatting, select your cells, and then choose Home

Styles

Conditional Formatting

Clear Rules

Clear Rules from Selected Cells. You can also use Home

Styles

Conditional Formatting

Clear Rules

Clear Rules from Entire Sheet to wipe out all the conditional formatting on your entire worksheet.

Styles

Conditional Formatting

New Rule. You see the New Formatting Rule dialog box (Figure 6-13).

Figure 6-12. Now Carolynne's days of indulgence stand out. The highlights you see here are a result of the settings applied in Every time the calorie value tops 10,000, Excel adds a yellow background.

Figure 6-13. The New Formatting Rule dialog box is split into two sections. The top portion of the window lets you choose the type of rule. (Ignore the first rule, "Format all cells based on their values," because that's used for data bars, color scales , and icon setsthree features you'll consider a little later in this chapter.) The bottom section lets you define all the rule settings. In this example, a new rule is being created that formats any cell with a value greater than 10,000.

The New Formatting Rule dialog box is surprisingly intuitive (translation: it's not just for tech jockeys). The "Format only cells that contain" rule is by far the most versatile. It lets you pick out specific numbers, dates, blank cells, cells with errors, and so on. Most people find this formatting rule satisfies most of their conditional formatting needs.

There are also two rules that work well with values that change frequently; these are "Format only top or bottom ranked values" and "Format only values that are above or below average." Both of these rules pick out values that stand out in relationship to the others. For example, if you didn't know that 10,000 calories is the threshold for llama overeating, you might use one of these rules to pick the largest meals, as shown in Figure 6-14.

Figure 6-14. Top: This rule picks out the top 10 percent of all values.
Bottom: This rule picks out values that are one standard deviation above average.
Both rules format the highest values, without your actually needing to know what these values are.

These rules are the foundation of conditional formatting. In the following sections, you'll learn about three more specialized conditional formatting features that use unique formatting to distinguish between different values.

6.3.3. Data Bars

Data bars a feature that places a shaded bar in the background of every cell you selectare one of the simplest and most useful forms of conditional formatting. The trick is that the data bar's length varies depending on the content in the cell. Larger values generate longer data bars, while smaller values get smaller data bars.

To see how this works, consider the worksheet shown in Figure 6-15, which shows a boring grid of numbers with no formatting.

Figure 6-15. This worksheet shows the weekly results of the company Oreo-eating competition. Right now, it's a densely packed grid of information, so it's hard to see who's set the most impressive totals.

To use data bars, select the cells you want to format (in this example, that's cells B3 to F10), and then choose Home Styles Conditional Formatting Data Bars. You see a gallery with several different data bar choices. The only difference is the color of the bars; so feel free to pick any one. Figure 6-16 shows the result.

When using data bars, Excel finds the largest value and makes that the largest data bar (so it fills the cell almost completely). Excel then finds the smallest value and uses that for the smallest data bar (which just barely fills the cell). It then creates a proportionately sized data bar for all the other cells.

Figure 6-16. The data bars allow you to quickly pick out the largest and smallest values. For example, there's no doubt that Toby's Thursday and Friday totals and Dwight's performance on Monday set the office standard.

The best part about data bars is that Excel keeps them synchronized with your data. In other words, if you change the worksheet shown in this example by filling in the numbers for the next week, Excel automatically adjusts all the data bars.

Data bars work best with groups of numbers that are evenly spread out. This feature makes sense for recording the Oreo cookie extravaganza because the Oreo consumption of most employees falls into the same basic range. However, when you have one or two values that are dramatically higher or lower than the rest, they skew the scale and make it hard to see the variance between the other values.

6.3.4. Color Scales

Color scales allow you to format different cells with different colors. As with data bars, Excel automatically chooses the right color for each cell. Excel assigns a predefined color to the lowest value, and another predefined color to the highest value, and it uses a weighted blend of the two for all the other values. For example, if 0 is blue and 100 is yellow, the value 50 gets a shade of mid-green.

To apply a color scale, select your cells, choose Home Styles Conditional Formatting Color Scales, and then choose one of the color combinations shown in the gallery. To test out different color scale options, hover over them with the mouse, and then take a look at the live preview on your worksheet.

Color scales aren't used as often as data bars because they tend to create a more visually cluttered worksheet. If you do decide to use color scales, you may want to customize how they work so they're applied only to specific cells, or so that they use a less obtrusive pair of colors (like white and light red). You can learn more about these techniques in Section 6.3.6.2.

6.3.5. Icon Sets

So far, you've seen how to highlight different values with a different graphical representation using shaded bars and colors. Both these tricks are called data visualizations . Excel has one more data visualization tool up its sleeve: icon sets.

The idea behind icon sets is that you choose a set of three to five icons. Excel then examines your cells, and displays one of these icons next to each value, depending on the value. For example, if you choose an icon set with three icons, the bottom 33 percent of all values get the first icon, the middle 33 percent get the second icon, and the top 33 percent get the third icon.

To see the available icon sets, choose Home Styles Conditional Formatting Icon Sets (Figure 6-17).

Figure 6-17. Excel has several useful icon sets. Popular choices are red-yellow-green shapes , arrows pointing in different directions, Xs and checkmarks, moon phases, and partly filled circles.

Sadly, you can't create your own icon sets in this version of Excel. However, there's a lot you can accomplish by using the existing icon sets with a little imagination . Figure 6-18 shows two options with the table of Oreo eaters. The top example ranks each competitor's performance each day, while the bottom one calculates per-person totals (using the SUM( ) function described in Section 9.2), and then adds icons to those totals.

Figure 6-18. Top: The moon phase icon set (known by the seemingly contradictory name 5 Quarters) shows how different employees stack up in the Oreoeating competition. A full moon, for example, indicates a competitionleading performance. Empty moons are clear signs of Oreo-haters.
Bottom: It's time to go to round two. Employees with a checkmark icon qualify to move on, while those with an X icon can stay home and drink milk. Employees with an exclamation mark are in the middle ground, and can fight for the last remaining playoff spaces in another qualifying round.

6.3.6. Fine-Tuning a Formatting Rule

When you apply data bars, color scales, or icon sets to a group of numbers, Excel creates a new conditional formatting rule. It's this rule that tells Excel how to format the group of cells you've selected.

To fine-tune the way conditional formatting works, you can tweak your rule. Here's how:

Select Home Styles Conditional Formatting Manage Rules .

The Conditional Formatting Rules Manager appears, displaying any conditional formatting rules you previously created. You'll learn more about this window in Section 6.3.7.
Select the rule you want to edit, and then click Edit Rule .

The Edit Formatting Rule dialog box appears. (Jump ahead to Figure 6-20 to take a look.) The Edit Formatting Rule dialog box looks exactly the same as the New Formatting Rule dialog box you saw earlier. The top portion of the window lets you change the type of rule (which isn't what you want to do), and the bottom section lets you refine the current rule (which makes more sense).
Modify the settings in the "Edit the Rule Description" section, and then click OK to apply them .

You can also click Preview to see what the result of your change will be on your worksheet.
Click OK again to close the Conditional Formatting Rules Manager .

The options in the Edit Formatting Rule dialog box depend on the type of rule you're modifying. The following sections guide you through your choices.

6.3.6.1. Fine-tuning data bars

If you're editing a data bar, there are three details you can change:

Use the Show Bar Only checkbox to hide cell value and show just the data bar.
Use the Bar Color list to pick the exact bar color that you want.
Use the Shortest Bar and Longest Bar lists to control how Excel determines bar length.

The last option is the most interesting. Ordinarily, the lowest value gets the shortest bar and the highest value gets the longest bar. However, this isn't always the best approach. For one thing, it can wipe out fine distinctions if there are a few very large or very small values in your selection. Figure 6-19 shows the problem.

To fix this problem, you can set a specific minimum or maximum value. To do so, change the Shortest Bar setting from Lowest Value to Number, and change the Longest Bar setting from Highest Value to Number, as shown in Figure 6-20. That way, you can focus the scale on the range of values that's most important.

Along with the Number setting, you can also set the Shortest Bar and Highest Bar setting to Percent, Percentile, and Formula.

Percent allows you to supply the minimum and maximum values as percentages instead of fixed numbers. Ordinarily, the lowest value is 0 percent and the highest value is set to 100 percent. So, if you want to cut off the ends of the scale, you might start the smallest bar at 10 percent and cap the highest at 90 percent. If your actual values range from 0 to 500, the bottom value (at 10 percent) becomes 50, and the top value (at 90 percent) becomes 450.

Figure 6-19. One challenge when using data bars is how to handle off-the- charts values (like Kobayashi's Thursday performance) that make everyone else's numbers look the same. The difference between the smallest value (4) and the largest value (786) is much larger than the difference between most values. See Figure 6-21 for an elegant solution.

Figure 6-20. In this example, Excel treats 0 as the smallest value and 100 as the largest. All values in between are filled proportionately (so 50 is a half-length data bar). Any value less than the minimum gets the minimum bar size, and any value larger than the maximum gets the maximum bar size. Figure 6-21 shows the result.

The Percentile number works similarly, but in a way that's more satisfying for mathematically minded statisticians. This number arranges all the values in order from lowest to highest, and then slots them into different percentiles . In a set of 10 ordered values, the 40th percentile is always the fourth value, regardless of its exact number. In other words, when you're using percentiles, Excel isn't all that interested in how high or low the exact value isinstead, it pays attention to how that value falls in relationship to everything else. If you set a data bar minimum to a percentile value of 10, the bottom 10 percent of values get the shortest bar. If you set the maximum to 90, the top 10 percent of values get the longest bar.

Figure 6-21. Now it's easy to see the variation between midrange values. The only tradeoff is that every value above 100 gets the same data bar, so you lose the ability to distinguish between high and extremely high values.

Tip: The nice thing about percentiles is that you always get a good range of short, medium, and long data bars, even if your numbers are spread about unevenly.

Finally, the Formulas option is an advanced trick that lets you use a formula that tells Excel what the highest or lowest value should be. You can learn more about formulas in Chapter 8.

6.3.6.2. Fine-tuning color scales

You can fine-tune color scales in much the same way you adjust data bars. Here are the settings you have to play with:

In the Format Style list, choose 2-Color Scale or 3-Color Scale, depending on how many colors you want to use.
Choose the colors for the minimum and maximum (and, in a three-color scale, the midpoint ).
Use the Minimum, Midpoint, and Maximum lists to choose how Excel determines these values.

One reason you might fine-tune a color scale is to use less obtrusive formatting by using white as your minimum value color, and choosing a light color for the maximum. You can also use percentiles to make a more reserved-looking worksheet, where most values get no color and the highest values get just a tinge (see Figure 6-22).

Figure 6-22. A two-color scale blends from one color to another, while a three-color scale has a specific midpoint color. Excel blends values in the bottom range between the lowest value color and the midpoint color, and values in the top range between the midpoint color and the highest value color. Thanks to the explicit percentile limits used here (like the Minimum Value entry of 85), only the top 15 percent of values have any color.

6.3.6.3. Fine-tuning icon sets

With icon sets, you have several settings to play with:

Use the Icon Style list to change the icons you're using. You have all the options you saw in the Home Styles Conditional Formatting Icon Sets gallery.
Use the Reverse Icon Order checkbox to arrange icons in reverse order (so the icon that was formerly being used for the top values is now applied to the bottom ones).
Use the Show Icon Only checkbox to hide cell values and display just the icon. You can also use this trick with data bars, but it's way more convenient with icon sets. It lets you create a dashboard-like display that indicates the significance of different values (for example, good, bad, or neutral) rather than the exact value (which may be of much less interest to the spreadsheet reader).
Choose the range of values that Excel uses for each icon. As with data bars, you can set these ranges using fixed numbers, percentages, percentiles, or a formula. Figure 6-23 shows an example.

Figure 6-23. Ordinarily, Excel splits the range of values into equal percentile groups. As a result, all three icons appear about the same number of times. By changing the ranges, you can reserve some icons for narrower ranges of numbers. For example, you could decide to use the X icon for all test scores that fall under 50, and keep the green checkmark for the best performing above-80 scorers (as shown here).

TROUBLESHOOTING MOMENT
Conditional Formatting in Older Versions of Excel

Some conditional formatting trickslike data bars, color scales, icon sets, conditional formatting that overlaps, and cells with more than three conditional formatting rulesdon't work in any version of Excel except Excel 2007. If you use one of these tricks, export your spreadsheet to the Excel 97-2003 standard (as described in Section 1.4.2), and then open it in an older version of Excel, you won't see your conditional formatting. Instead, your cells will have the normal, unformatted appearance.

Unless you start fiddling with the conditional formatting settings in an older version of Excel, all the formatting should return the next time you open the spreadsheet in Excel 2007. That's because the information is still there in your spreadsheet file. Older versions of Excel just can't deal with it.

6.3.7. Using Multiple Rules

So far, you've seen examples that use only one conditional formatting rule. However, there's actually no limit to the number of conditional formatting rules you can use at the same time. (Older versions of Excel don't have the same abilitythey top out at three conditions.)

Excel gives you two basic ways to use multiple rules:

You can create rules that format different subsections of data. This lets you apply several different layers of conditional formatting to highlight different values.
You can create rules that overlap. For example, you can highlight the top five values with red lettering and values above 10,000 with bold. If one of the top five values has a value above 10,000, it gets the combined formatting settings, and Excel displays it in bold red.

If you use conditional rules that overlap, there's always the possibility of conflict. For example, one conditional formatting rule might apply a red background fill while another sets a yellow background fill. If both these rules affect the same cell, only one can win.

In this situation, it all depends on the order in which Excel applies conditional formatting rules. If there's a conflict, rules that are applied later override rules that are applied earlier. Ordinarily, Excel applies rules in the same order that you created them, but if this isn't what you want, you can change the order using the Conditional Formatting Rules Manager, which is shown in Figure 6-24. To get to the Conditional Formatting Rules Manager, select one of the cells that uses the conditional formatting, and then choose Home Styles Conditional Formatting Manage Rules.

Figure 6-24. To reorder a rule, select it, and then click the up or down arrow button (circled). Excel applies the rules at the top of the list first. In this example, Excel applies the Cell Value > 10000 rule before the Top 5 rule. As a result, the Top 5 rule formatting may override the Cell Value > 10000 formatting, if they conflict.

Note: Ordinarily, the Conditional Formatting Rules Manager shows only the conditional formatting rules that apply to the currently selected cell (or cells). However, you can choose a specific worksheet in the "Show formatting rules for" list to see everything that's defined on that worksheet. Now you have a nice way to review all the conditional formatting rules that you've created, but it can be a little confusing. When showing all the formatting on a worksheet, it's important to remember that the order of rules isn't important if the rules apply to different sets of cells.

The Conditional Formatting Rules Manager isn't just for reordering your rules. It also lets you:

Create rules (click New Rule)
Modify rules (select a rule in the list, and then click Edit Rule)
Delete rules (select a rule, and then click Delete Rule)

Finally, there's one easily overlooked gem: the Stop If True column. You can use this setting to tell Excel to stop evaluating conditional formatting rules for a cell. This setting is a handy way to limit where Excel shows data bars, color scales, and icon sets.

For example, imagine you have a worksheet that shows test scores for a top culinary school. You want to show an X icon next to all the failing grades, but you don't want to bother showing any icon next to the others, as shown in Figure 6-25.

Figure 6-25. In this worksheet, "icon sets" flag the underperformers but leave the other cells uncluttered. Excel experts love this nifty trick.

You may think the solution is to select just the cells you want to use (the failing grades), and then apply the conditional formatting rule. But doing that takes away all the benefits of conditional formatting. Instead of getting Excel to find the cells you want, you're stuck hunting for the right ones. This approach also runs into trouble if the grades change, in which case you need to painstakingly reapply your formatting to the right cells. What you really want is a way to take a bunch of cells, give them all icons, and then hide the icons that don't interest you. Here's a case where Stop If True can solve your problem.

To whip up this concoction, you need two rules. Here's how to create them:

Select the test scores, and then choose Home Styles Conditional Formatting Manage Rules .

In order to create and fine-tune your rules, you need to use the Conditional Formatting Rules Manager.
Click New Rule .

The first rule you create is the icon set that formats your cells.
Choose "Format all cells based on their values" for your rule type .

Now you need to fill in the rule settings.
In the Format Style list, choose Icon Sets. In the Icon Style list, choose "3 Symbols (Uncircled)."

This gives you the checkmark, exclamation mark, and X combination.
Specify that the checkmark applies to scores above or equal to (>=) 70. The exclamation mark applies to scores above or equal to 50. The remaining scores get the X .

Make sure that you choose Number in the Type box for each value, so that Excel does the comparisons on the number that's in each cell.
Click OK .

This returns you to the Conditional Formatting Rules Manager, where you can see your newly added rule.
Click New Rule .

Now it's time to add the second rule. This rules matches all the cells that won't have iconsin other words, the passing scores.
Choose "Format only cells that contain" for your rule type .

Now you need to fill in the rule settings.
Under the "Format only cells with" heading, choose "Cell Value" is "greater than or equal to" and supply a minimum score value of 50 .

Don't set any formatting options.
Add a checkmark in the Stop If True column .

When Excel finds a passing score, it matches this rule. No formatting's defined, so it doesn't change the way the cell looks. However, it heeds the Stop If True condition, and stops applying any more rules.
Click OK to finish adding the rule .

Now you see both rules (Figure 6-26). Remember, the Stop If True rule must be first in the list because it needs to stop Excel before the icon set is applied.
Click OK .

Now, you get the result shown in Figure 6-25. Excel shows the icon set only in cells that bypass the first condition, which means they have a score under 50.

Figure 6-26. Here are the two rules this example needs. The first one ignores cells that shouldn't have icons, and the second one applies X to the failing grades.