Create a Histogram with a Pivot Table for Excel Dashboards
Did you know you can use a pivot table as the source for a histogram for your Excel dashboards and reports? That’s right. With a little-known trick, you can create a histogram that’s as interactive as a pivot table!
The first step in creating a histogram with a pivot table is to create a frequency distribution. Just follow these steps:
Create a pivot table and plot the data values in the row area (not the data area).
As you can see in this figure, the SumOfSales_Amount field is placed in the ROWS drop zone. Place the Sales Rep field in the VALUES dropzone.
Right-click any value in the ROWS area and choose Group.
The Grouping dialog box appears.
In this dialog box, set the Starting At and Ending At values and then set the interval.
This creates your frequency distribution. In the previous figure, the distribution is set to start at 5,000 and to create groups in increments of 1,000 until it ends at 100,000.
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. You can now leverage this result to create 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.