Microsoft Office Tutorials and References

In Depth Information

**Emphasizing Top Values in Charts**

4.
Click OK to confirm your settings.

The pivot table calculates the number of sales reps for each defined increment, just as in a

frequency distribution. (See Figure 9-19.) You can now leverage this result to create a histogram!

Figure 9-19:
The result of grouping the values in the Row area is a frequency distribution that can be

charted into a histogram.

The obvious benefit to this technique is that after you have a frequency distribution and a histogram,

you can interactively filter the data based on other dimensions, like Region and Market. For instance,

you can see the histogram for the Canada market and then quickly switch to see the histogram for

the California market.

Note that you can’t add cumulative percentages to a histogram based on a pivot table.

Tip

Emphasizing Top Values in Charts

Sometimes a chart is indeed the best way to display a set of data, but you still want to call attention

to the top values in that chart. In these cases, you can use a technique that
actually
highlights the top

values in your charts. That is to say, you can use Excel to figure out which values in your data series

are in the top
nth value and then apply special formatting to them. Figure 9-20 illustrates an example

where the top five quarters are highlighted and given a label.

The secret to this technique lies in Excel’s obscure LARGE function. The LARGE function returns the
nth

largest number from a dataset. In other words, you tell it where to look and the number rank you want.

To find the largest number in the dataset, you enter the formula LARGE(Data_Range, 1). To find the

fifth largest number in the dataset, you use LARGE(Data_Range, 5). Figure 9-21 illustrates how the

LARGE function works.