Analyzing Data with Tables and Charts in Microsoft Excel 2013

Using conditional formatting to highlight cells based on their content

Tables filled with data can be overwhelming and difficult to understand without lengthy explanations. Are there ways to highlight trends and patterns and identify anomalies while still maintaining a full view of the data in a table? Indeed there are.

You can help your audience (and yourself) make more sense of data by using conditional formatting to highlight values that meet criteria you define. We introduced this feature briefly at the beginning of this chapter, in the discussion of the Quick Analysis tool. In this section, we explain how to take full advantage of it.

The idea behind conditional formatting is simple: You want to be able to look at a table or a range of data and quickly see any values that demand your immediate attention or spot trends that might not be apparent from the raw data. Is a particular value greater than or less than a specific amount? Is it in the top (or bottom) of all values in the range? Does it contain a specific word or string of text or fall within a range of dates?

After you define those conditions, Excel can apply automatic formatting that identifies matching values: displaying the bottom 20 percent of values in red, for example, with the top 20 percent in green; or using a color scale that moves from red to yellow to green as the values in the selection go from low to high; or adding data bars that provide a visual representation of the relative size of values in a cell.

The easiest way to get started is to use the Formatting section of the Quick Analysis tool.

Select a range of data—an entire table, a column or row containing totals, or a subset of data representing groups whose performance you want to examine more closely. Then press Ctrl+Q to open the Quick Analysis tool. The options available depend on whether your selection includes numbers or text. Figure 13-1, at the beginning of this chapter, shows the options available when you select numbers. Figure 13-10 shows what you see if your selection includes only text (in this case, the contents of the Company column).

Figure 13-10 The Formatting options in the Quick Analysis tool offer these choices when your selection includes only text.

If you click Duplicate Values, Excel applies a red background to all values in the selection that are repeated at least once. Click Unique Values to perform the inverse operation, applying similar formatting to any cell whose contents are not repeated elsewhere in the list.

The Text Contains and Equal To options require some explanation. The effect of clicking either button depends on the contents of the current cell within the selection. (You can move the current cell within the selection using Tab and Shift+Tab.) If the current cell contains “Contoso,” click Text Contains to apply a red background to any cell containing that word: Contoso, Contoso Inc., Contoso Pharmaceuticals, and so on. Click Equal To and Excel will highlight only cells that are a perfect match for the current cell.

The options in the Quick Analysis tool for a selection that contains numbers rather than text allow you to add data bars, icons, or colored shading to a selection of data, features we explore in the remainder of this section.

Any conditional formatting you apply using the Quick Analysis tool uses default settings. For greater control over the conditions and appearance of the formatting, you can create conditional formatting rules directly.

To get started, select a range of data and then click Conditional Formatting in the Styles group on the Home tab. That displays the Conditional Formatting menu:

Each of the five main choices on this menu provides access to a range of preset rules. The More Rules option at the bottom of each of the submenus allows you to create custom choices that vary from the preset configurations. The following list describes what you’ll find in each of the five main choices:

• Highlight Cells Rules Each of the seven preset options opens a dialog box that lets you construct a formula using a comparison operator (greater than, less than, equal to, and so on) along with a value or cell reference to compare with each cell’s contents. Excel fills in values using its internal algorithms; you can change those values or point to a cell reference. The list on the right allows you to choose the formatting to be applied to cells matching your specified conditions.

  

• Top/Bottom Rules The input dialog box that opens when you select any of the choices on this menu also allows you to create a rule on the fly. Don’t be misled by the number 10 in the Top 10% and similar options. You can change that value or percentage to a different number if you prefer.

  

• Data Bars This option adds a small bar (a longer bar equals a higher value) to each selected data cell, using a solid or gradient color. These bars show up as a live preview in the selected data so you can see the effect before you make it final. In the example shown in Figure 13-11, we’ve already created a customized data bar; choosing an option from the Solid Fill list changes the color without changing other settings. Note the ScreenTip, which explains, tersely, what the thumbnail represents.

  

  Figure 13-11 Adding a data bar to a range (the Rainfall column, in this example) adds a colored bar to visually represent the data in the selection.

• Color Scales This option applies colored cell backgrounds to the selected range using two or three colors in a range that is defined by the data itself. The Green-Yellow-Red option, for example, uses green for the lowest values, red for the highest, and yellow for everything in between. The actual number of shades used is much more than two or three, with more intense shades representing the extremes of higher and lower values.

• Icon Sets This is the most visually diverse (and potentially cringe-inducing) of all the preset conditional formatting options. You can choose from arrows, circles and other shapes, flags, and rating scales made up of stars and bars and immediately recognizable symbols like the ones shown here.

  

You can combine multiple rules in the same selection, highlighting values in the top 20 percent in bold green text on a light green background, with values in the bottom 20 percent displayed in bold red italics on a light red background, for example.

If none of the preset options match your needs, you can create custom rules from scratch. In some cases, you might start with a preset rule (from the Quick Analysis tool or from the Conditional Formatting menu) and then, after applying it to the selection, modify the rule.

Click More Rules at the bottom of any of the Conditional Formatting menu options to display the New Formatting Rule dialog box. The example shown in Figure 13-12 adjusts the default rules for data bars so that the largest bar in the selection doesn’t cover up the number it represents.

Figure 13-12 Click More Rules at the bottom of a conditional formatting option to build a rule from scratch. Changing the Minimum and Maximum values here adjusts the length of data bars to avoid covering the value.

If you’ve already defined rules and want to adjust them, click Conditional Formatting (in the Styles group on the Home tab) and then click Manage Rules. That opens the Conditional Formatting Rules Manager dialog box, shown in Figure 13-13. Select This Worksheet to see all rules for the current sheet.

Figure 13-13 You can edit any existing rule from this dialog box. Use the Stop If True option if you want to apply conditional formats in a precise hierarchy.

To edit an existing rule, select its entry in the Conditional Formatting Rules Manager dialog box and click Edit Rule. The exact options you see here depend on the type of rule you originally created. For example, you can hide the underlying value and show only an icon or data bar.

If you experiment a little too much with conditional formatting rules and want to get a fresh start, click Clear Rules on the Conditional Formatting menu. You can erase the rules from a selection, an entire sheet, a table, or a PivotTable.