Microsoft Office Tutorials and References
In Depth Information
Using Histograms to Track Relationships and Frequency
This chapter discusses how to create a histogram using formulas and pivot tables. The techniques
covered here fit nicely in data models where you separate data, analysis, and presentation
information. In addition, these techniques allow for a level of automation and interactivity that comes in
handy when updating dashboards each month.
We discuss how to develop a data model in Chapter 11.
Adding formulas to group data
First, you need a table that contains your raw data. The raw data ideally consists of records that
represent unique counts for the data you want to group. For instance, the raw data table in Figure 9-9
contains unique sales reps and the number of units each has sold. Follow these steps to create a
1. Before you create your histogram, you need to create a bin table (see Figure 9-9).
The bin table dictates the grouping parameters that are used to break your raw data into the
frequency groups. The bin table tells Excel to cluster all sales reps selling fewer than 5 units
into the first frequency group, any sales reps selling 5 to 14 units in the second frequency
group, and so on.
Figure 9-9: Start with your raw data table and a bin table.
You can freely set your own grouping parameters when you build your bin table.
However, it’s generally a good idea to keep your parameters as equally spaced as
possible. We typically end our bin tables with the largest number in our dataset. This allows
us to have clean groupings that end in a finite number — not in an open-ended greater