Ms Office Tune Up

Another of Excel’s new conditional formatting rules is the top/bottom rule, which applies a format to cells that rank in the top or bottom (for numerical items, the highest or lowest) values in a range. You can select the top or bottom either as an absolute value (for example, the top 10 items) or as a percentage (for example, the bottom 25 percent). You can also apply formatting to those cells that are above or below the average. To create a top/bottom rule, begin by choosing Home, Conditional Formatting, Top/Bottom Rules. Excel displays six choices:

Top 10 Items Choose this command to apply formatting to those cells with values that rank in the top X items in the range, where X is the number of items you want to see (the default is 10).

Top 10% Choose this command to apply formatting to those cells with values that rank in the top X percentage of items in the range, where X is the percentage you want to see (the default is 10).

Bottom 10 Items Choose this command to apply formatting to those cells with values that rank in the bottom X items in the range, where X is the number of items you want to see (the default is 10).

Bottom 10 % Choose this command to apply formatting to those cells with values that rank in the bottom X percentage of items in the range, where X is the percentage you want to see (the default is 10).

Above Average Choose this command to apply formatting to those cells with values that are above the average of all the values in the range.

Below Average Choose this command to apply formatting to those cells with values that are below the average of all the values in the range.

In each case, you see a dialog box that you use to set up the specifics of the rule. For the Top 10 Items, Top 10%, Bottom 10 Items, and Bottom 10% rules, you use the dialog box to specify the condition and the formatting that you want applied to cells that match the condition. For the Above Average and Below Average rules, you use the dialog box to spec­ify the formatting only.

This all works well and is straightforward to apply. However, none of these top/bottom rules might be quite right for your data analysis needs. For example, suppose your data set includes a few anomalous values that are skewing the average much higher or lower than it should be. In that case, a better measure of the “average” might be the median value, which is the value in a data set that falls in the middle when all the values are sorted in numeric order. That is, half of the values fall below the median, and half fall above it. You calculate the median using the MEDIAN() function:

MEDIAN(number1[,number2,...])

number1, number2,… A range, array, or list of values of which you want the median.

For example, to calculate the median of the values in the sales list shown in earlier Figure, use the following formula:

=MEDIAN(D3:D21)

How do you get Excel to highlight cells that are, for example, below the median value? To handle this, Excel 2007 comes with another conditional formatting component that makes this feature even more powerful: You can apply conditional formatting based on the results of a formula. In particular, you set up a logical formula as the conditional formatting crite­ria. If that formula returns TRUE, Excel applies the formatting to the cells; if the formula returns FALSE, instead, Excel doesn’t apply the formatting. In most cases, you use an IF() function, often combined with another logical function such as AND() or OR(). However, you can also use a simple comparison formula. For example, the following formula com­pares the value in cell D3 with the median of the value in the range D3:D21:

=D3 < MEDIAN($D$3:$D$21)

To apply such a formula to a range of cells for the purposes of conditional formatting, you need to bear in mind two more things:

• You must enter the reference to the comparison value that changes (such as D3 in the previous formula) as a relative reference.
• You must enter the reference to the comparison value that doesn’t change (such as the range $D$3:$D$21 above) as an absolute reference.

This way, when Excel applies the conditional formatting formula to each cell in the range, it adjusts the relative reference accordingly so that the formatting is correctly applied to each cell.

Here are the general steps to follow to set up formula-based conditional formatting:

1. Select the cells to which you want the conditional formatting applied.
2. Choose Home, Conditional Formatting, New Rule. Excel displays the New Formatting Rule dialog box.
3. Click Use a Formula to Determine Which Cells to Format.
4. In the Format Values Where This Formula is True range box, type your logical for­mula.
5. Click Format to open the Format Cells dialog box.
6. Use the Number, Font, Border, and Fill tabs to specify the formatting you want to apply and then click OK.
7. Click OK.

Figure 1 shows the conditional formatting formula applied to the range D3:D21 that formats those cells with values less than the median.

Figure 1 You can use a formula as the condition that Excel uses to determine whether to apply formatting.

Technorati Tags: highlight values, median

Popularity: 2% [?]