of the text box and select a worksheet cell that contains the value. Also, depending on the operator, you might need to specify two values.5 Use the right drop-down list to select the formatting to apply to cells that match your condition.If you’re feeling creative, you can make up your own format by selecting the Custom Format command.
6 Click OK.Excel applies the formatting to cells that meet the condition you specified.
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
