Use Excel Pivot Tables to Get Top and Bottom Views
Create a Bullet Graph for Your Excel Dashboards
How to Move Embedded Charts to Chart Sheets in Excel 2013

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:

1

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.

2

Right-click any value in the ROWS area and choose Group.

The Grouping dialog box appears.

3

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.

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. 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.

blog comments powered by Disqus
How to Format the X and Y Axis Values on Charts in Excel 2013
Use Histograms to Track Frequency in Excel Dashboards
How to Create Charts via the Quick Analysis Tool in Excel 2013
How to Create Side-by-Side Time Comparisons in Excel Reports
How to Save a Customized Chart as an Excel 2013 Template
Advertisement

Inside Dummies.com