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.

20150326a

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.

Invoice Table by Jeff Lenning

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.

PivotTable by Jeff Lenning

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.

PivotTable Count by Jeff Lenning

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.

Group by Jeff Lenning

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

Aging by Jeff Lenning

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.

Column Chart by Jeff Lenning

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.

Gap Width by Jeff Lenning

The resulting histogram is shown below.

Histogram by Jeff Lenning

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.

AR Aging by Jeff Lenning

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

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

7 Comments

  1. David Blumberg on March 26, 2015 at 12:21 pm

    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?

    • jefflenning on March 26, 2015 at 1:34 pm

      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

  2. Chris Coates on March 31, 2015 at 3:38 am

    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+

    • jefflenning on March 31, 2015 at 11:12 am

      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

  3. Alec on May 31, 2018 at 11:50 am

    Hi Jeff
    Really great stuff. It has enabled us to do a weighted distribution very efficently!
    Many thanks
    Alec.

  4. Martin Manscher on November 15, 2018 at 5:12 am

    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)

    • Ben on March 9, 2024 at 8:19 pm

      Same problem here

Leave a Comment