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