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.
Search JabSto ::

Custom Search