## Histogram with a PivotChart

A histogram graphically displays the number of items that fall within equal intervals, or, bins. For example, the number of days with a high temperature between 71-80 degrees, 81-90, and 91-100, the number of students with test scores between 60-69, 70-79, 80-89, or the number of invoices that are due in 31-60, 61-90, or 91-120 days. In this post, we’ll create a histogram using a PivotChart.

## Objective

We have exported a list of open invoices from our accounting system. We would like to create a chart that displays the invoices based on their aging status, grouped into equal intervals, 1-30, 31-60, 61-90 days, and so on. This is illustrated below.

Typically, the most time-consuming part of creating a histogram is summarizing the data and computing the number of items within each interval. Once the data has been summarized, it is fairly easy to plot.

As with anything in Excel, there are several ways to summarize the data, and with a little trick, we can use a PivotTable.

Before we jump right in, there is a note of caution. PivotTables only display values that exist in the data source. That is, if there are no invoices falling between 31-60 days, that interval will be excluded from the PivotTable and PivotChart. There are a few ways to address this. Depending on what you are charting, you could add blank value cells to the table for the missing interval labels, or, use a different solution including COUNTIFS, FREQUENCY, or the Histogram tool.

Assuming our data contains values for all needed intervals, it is pretty quick and easy to create a PivotTable and related PivotChart.

## PivotTable/PivotChart

Let’s start by looking at our data. We have exported the open invoices from our accounting system, and stored the data in a table (**Insert > Table**). We have defined an aging “As Of” date and then computed the number of Days between the invoice date and the As Of date with a simple subtraction formula. The resulting data table is shown below.

To summarize the data table and have Excel automatically place the data into intervals, we create a PivotTable. We **Insert > PivotTable**, and then insert the Days field into the ROWS area and the Amount field into the VALUES area. This is shown below.

By default, Excel will sum the Amount field since it is numeric. For our histogram we want to change the math to count, so we right-click any PivotTable value cell and select **Summarize Values By > Count**. The updated PivotTable is shown below.

The final step is to create the intervals, or bins. This is pretty easy with a PivotTable once we know about the group trick!

All we need to do is **right-click** any day cell in the PivotTable and select **Group**. In the resulting Grouping dialog box, we can specify the starting and ending values as well as the interval length. In our case, we want to start the first interval with day 1, we want to end at 120, and we want the intervals to span 30 days.

When we click OK…bam…the PivotTable shows the intervals as desired!

We can now insert a PivotChart by selecting the **PivotTable Tools > Analyze > PivotChart** icon. In the resulting Insert Chart dialog, we accept the default column chart and click OK. Excel inserts a PivotChart into the worksheet as shown below.

All we need to do now is clean it up with a few cosmetic touches. We remove the legend by selecting it and pressing the delete key on our keyboard. We remove the chart buttons by clicking the **PivotChart Tools > Analyze > Field Buttons** icon. We select a desired style with the **PivotChart Tools > Design > Chart Styles** options. We remove the empty space between columns by changing the Gap Width to 0% as shown below.

The resulting histogram is shown below.

While a histogram shows the frequency distribution, that is, the number of items in each interval, we can tweak our PivotTable to show the number of dollars within each interval by changing the math from count to sum. We do this by right-clicking any value cell in the report and selecting **Summarize Values By > Sum**. The updated column chart is shown below.

Now our chart displays the number of dollars that fall within each range. This is really easy to update in future periods as well. All we need to do is paste transactions into the data table and Refresh the PivotTable. Doing so will update the PivotTable as well as the PivotChart.

If you have any other approaches, preferences, or tricks…please share by posting a comment below.

## Additional Resources

Great stuff. Really enjoy these tips. The prior one was moving averages and was also great. What is the formula for eliminating top and bottom figures and the computing the mean?

David…thanks! The beautiful thing about summarizing the data with a PivotTable is that computing the average and filtering top and bottom values is pretty easy. To filter, use the filter control at the top of the report and use the Value Filters options, and to switch the math to average, right-click a value cell and then select Summarize Values By > Average.

Thanks,

Jeff

Thanks for this article. I didn’t know you could do that. Very handy indeed.

I have always used a vlookup with my source data using the “TRUE” attribute to fine the first match and return a text value for the range which I then pivot. This is useful for uneven ranges, when you want to weight a particular set of values but you have to carefully format the text to ensure that it sorts properly to avoid manually sorting your pivot table.

I use this when reporting things like customer average transaction value, as there is often an overwhelming weighting of values under £50 (a median around £40) with some values as high as £5000

lookup =VLOOKUP(A2, LookupRange,2,TRUE)

Value Lookup

1 0000-0024

2 0000-0024

3 0000-0024

35 0025-0099

45 0025-0099

55 0025-0099

65 0025-0099

75 0025-0099

99 0025-0099

101 0100-0999

400 0100-0999

1001 0999+

LookupRange:Value Range

0 0000-0024

25 0025-0099

100 0100-0999

1000 0999+

Chris – Ah…yes…the PivotTable approach assumes that the intervals are equal. Thanks for sharing your formula-based approach when the intervals are uneven!

Thanks

Jeff

Hi Jeff

Really great stuff. It has enabled us to do a weighted distribution very efficently!

Many thanks

Alec.

Doesn’t seem to work… When I right-click one row data cell and select “Group…”, I get a “Cannot group that selection” error. When I select multiple and right-click, they are grouped, but I’m not allowed to select bins (Office Professional Plus 2016)