Paul McFedries - Excel Data Analysis For Dummies

What are the top 10 values?

Which cell values are above average, and which are below average?

These simple questions aren’t easy to answer just by glancing at the worksheet, and the more numbers you’re dealing with, the harder it gets. To help you eyeball your worksheets and answer these and similar questions, Excel lets you apply conditional formatting to the cells. Excel applies this special format only to cells that satisfy some condition, which Excel calls a rule. For example, you could apply formatting to display all negative values in a red font, or you could apply a filter to show only the top 10 values.

A conditional format is formatting that Excel applies only to cells that meet the criteria you specify. For example, you can tell Excel to apply the formatting only if a cell’s value is greater or less than some specified amount, between two specified values, or equal to some value. You can also look for cells that contain specified text, dates that occur during a specified time frame, and more.

When you set up your conditional format, you can specify the font, border, and background pattern. This formatting helps to ensure that the cells that meet your criteria stand out from the other cells in the range. Here are the steps to follow:

1 Select the range you want to work with.Select just the data values you want to format. Don’t select any surrounding data.

2 Choose Home ⇒ Conditional Formatting.

3 Choose Highlight Cells Rules and then select the rule you want to use for the condition.You have six rules to play around with:Greater Than: Applies the conditional format to cells that have a value larger than a value that you specify.Less Than: Applies the conditional format to cells that have a value smaller than a value that you specify.Between: Applies the conditional format to cells that have a value that is greater than or equal to a minimum value that you specify and less than or equal to a maximum value that you specify.Equal To: Applies the conditional format to cells that have a value that is the same as a value that you specify.Text that Contains: Applies the conditional format to cells that include the text that you specify.A Date Occurring: Applies the conditional format to cells that have a date value that meets the condition that you specify (such as Yesterday, Last Week, or Next Month).(I cover a seventh rule — Duplicate Values — later in this chapter.) A dialog box appears, the name of which depends on the rule you click in Step 3. For example, Figure 1-1 shows the dialog box for the Greater Than rule. FIGURE 1-1:The Greater Than dialog box and some highlighted values.

4 Type the value to use for the condition.You can also click the up arrow button that appears to the right 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.

Excel enables you to specify multiple conditional formats for the same range. For example, you can set up one condition for cells that are greater than some value and a separate condition for cells that are less than some other value. You can apply unique formats to each condition. Keep the range selected and follow Steps 2 through 6 to configure the new condition.

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.

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.