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.