Showing pesky duplicate values
You use conditional formatting mostly to highlight numbers greater than or less than some value, or dates occurring within some range. However, you can use conditional formatting also to look for duplicate values in a range. Why would you want to do that? The main reason is that many range or table columns require unique values. For example, a column of student IDs or part numbers shouldn’t have duplicates.
Unfortunately, scanning such numbers and picking out the repeat values is hard. Not to worry! With conditional formatting, you can specify a font, border, and background pattern that ensures that any duplicate cells in a range or table stand out from the other cells. Here’s what you do:
1 Select the range that you want to check for duplicates.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Highlight Cells Rules ⇒ Duplicate Values.The Duplicate Values dialog box appears. The left drop-down list has Duplicate selected by default, as shown in Figure 1-2. However, if you want to highlight all the unique values instead of the duplicates, select Unique from this list.FIGURE 1-2: Use the Duplicate Values rule to highlight worksheet duplicates.
4 In the right drop-down list, select the formatting to apply to the cells with duplicate values.You can create your own format by choosing the Custom Format command. In the Format Cells dialog box, use the Font, Border, and Fill tabs to specify the formatting you want to apply, and then click OK.
5 Click OK.Excel applies the formatting to any cells that have duplicate values in the range.
Highlighting the top or bottom values in a range
When analyzing worksheet data, looking for items that stand out from the norm is often useful. For example, you might want to know which sales reps sold the most last year, or which departments had the lowest gross margins. To quickly and easily view the extreme values in a range, you can apply a conditional format to the top or bottom values of that range.
You can apply such a format by setting up a top/bottom rule, in which Excel applies a conditional format to those items that are at the top or bottom of a range of values. For the top or bottom values, you can specify a number, such as the top 5 or 10, or a percentage, such as the bottom 20 percent. Here’s how it works:
1 Select the range you want to work with.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Top/Bottom Rules and then select the type of rule you want to create.You have six rules to mess with:Top 10 Items: Applies the conditional format to cells that rank in the top X, where X is a number that you specify (the default is 10).Top 10 %: Applies the conditional format to cells that rank in the top X %, where X is a number that you specify (the default is 10).Bottom 10 Items: Applies the conditional format to cells that rank in the bottom X, where X is a number that you specify (the default is 10).Bottom 10 %: Applies the conditional format to cells that rank in the bottom X %, where X is a number that you specify (the default is 10).Above Average: Applies the conditional format to cells that rank above the average value of the range.Below Average: Applies the conditional format to cells that rank below the average value of the range.A dialog box appears, the name of which depends on the rule you selected in Step 3. For example, Figure 1-3 shows the dialog box for the Top Ten Items rule.
4 Type the value to use for the condition.You can also click the spin buttons that appear to the right of the text box. Note that you don’t need to enter a value for the Above Average and Below Average rules.FIGURE 1-3: The Top 10 Items dialog box with the top 5 values highlighted.
5 In the right drop-down list, select the formatting to apply to cells that match your condition. When you set up your top/bottom rule, select a format that ensures that the cells that meet your criteria will stand out from the other cells in the range. If none of the predefined formats suits your needs, you can always choose Custom Format and then use the Format Cells dialog box to create a suitable formatting combination. Use the Font, Border, and Fill tabs to specify the formatting you want to apply, and then click OK.
6 Click OK.Excel applies the formatting to cells that meet the condition you specified.
Analyzing cell values with data bars
In some data-analysis scenarios, you might be interested more in the relative values within a range than the absolute values. For example, if you have a table of products that includes a column showing unit sales, you might want to compare the relative sales of all products.
Comparing relative values is often easiest if you visualize the values, and one of the easiest ways to visualize data in Excel is to use data bars, a data visualization feature that applies colored horizontal bars to each cell in a range of values; these bars appear “behind” (that is, in the background of) the values in the range. The length of the data bar in each cell depends on the value in that cell: the larger the value, the longer the data bar.
Follow these steps to apply data bars to a range:
1 Select the range you want to work with.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Data Bars and then select the fill type of data bars you want to create.You can apply two type of data bars:Gradient fill: The data bars begin with a solid color and then gradually fade to a lighter color.Solid fill: The data bars are a solid color.Excel applies the data bars to each cell in the range. Figure 1-4 shows an example in the Units column.
FIGURE 1-4: The higher the value, the longer the data bar.
Analyzing cell values with color scales
Getting some idea about the overall distribution of values in a range is often useful. For example, you might want to know whether a range has many low values and just a few high values. Color scales can help you analyze your data in this way. A color scale compares the relative values in a range by applying shading to each cell, where the color reflects each cell’s value.
Color scales can also tell you whether your data includes outliers: values that are much higher or lower than the others. Similarly, color scales can help you make value judgments about your data. For example, high sales and low numbers of product defects are good, whereas low margins and high employee turnover rates are bad.
To apply a color scale to a range of values, do the following:
1 Select the range you want to format.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Color Scales and then select the color scale that has the color scheme you want to apply.Color scales come in two varieties: three-color scales and two-color scales. If your goal is to look for outliers, go with a three-color scale because it helps the outliers stand out more. A three-color scale is also useful if you want to make value judgments about your data, because you can assign your own values to the colors (such as positive, neutral, and negative). Use a two-color scale when you want to look for patterns in the data, because a two-color scale offers less contrast.Excel