Microsoft Office Tutorials and References

In Depth Information

**Emphasizing Top Values in Charts**

Figure 9-20:
This chart highlights the top five quarters with different font and labeling.

Figure 9-21:
Using the LARGE function returns the
nth largest number from a dataset.

The idea is fairly simple. In order to identify the top five values in a dataset, you first need to identify

the fifth largest number (LARGE function to the rescue) and then test each value in the dataset to see

if it’s bigger than the fifth largest number. Here’s what you do:

1.
Build a chart feeder that consists of formulas that link back to your raw data. The feeder

should have two columns: one to hold data that isn’t in the top five and one to hold data

that is in the top five (see Figure 9-22).

2.
In the first row of the chart feeder, enter the formulas shown in Figure 9-22.

The formula for the first column (F4) checks to see if the value in cell C4 is less than the

number returned by the LARGE formula (the fifth largest value). If it is, the value in cell C4 is

returned. Otherwise, NA is used. The formula for the second column works in the same way,

except the IF statement is reversed: If the value in cell C4 is greater than or equal to the

number returned by the LARGE formula, then the value is returned; otherwise NA is used.

3.
Copy the formulas down to fill the table.