Excel: creating quick frequency distribution graphs

Excel level: Intermediate

This article shows how you can use Excel to create quick frequency distribution summaries. This is mainly intended for exploratory analysis rather than for presentation. You can obviously polish it up a bit if you need to present it too.

There are different methods available in Excel (depending on which version you are using):

• Pivot Table method
• Histogram standard chart type (Excel 2016)
• Analysis Pack Add-In Histogram

Let's start off with some sample data. We have 168 records of a single measure set up on a worksheet in a single column like this (just the top 16 records are illustrated here to save space):

Pivot Table method: Step One
Highlight the entire block of data then use  Insert...Pivot Table to create a Pivot Table to a new worksheet.

Pivot Table method: Step Two
Drag the Data field to both the Rows and the Values areas in the Pivot Table field selection dialogue

This will add a second column to the Pivot Table. But the new column will likely default to Sum of Data and have totals rather than counts in it

Pivot Table method: Step Three

Right click in the Pivot Table Sum of Data column then select the Value Field Settings option. From this select Count. The pivot table should now be transformed to show a count of each value (rather than the sum). Something like this: 

We now need to group the data values into regular bands ('bins'  in histogram speak). 

Pivot Table method: Step Four

Right click in the original column (anywhere will do) and select Group

The default dialogue box will have both detected what the range of your data and suggested an interval. Change these into something more suitable (you can change these settings again later) . 

You should then see the pivot table transformed into a frequency distribution summary, something like this:

Pivot Table method: Step Five

You can turn this table into a graph by using Insert....Pivotchart

This method takes far longer to describe than to actually do.


Histogram standard chart (Excel 2016): Step One
Highlight the entire block of data then use Insert...Chart and pick Histogram. You will be presented with a chart something like:

Histogram standard chart (Excel 2016): Step Two
Adjust the number of 'bins' via formatting the axis. Either by setting the number of bins:

Or by setting the bin widths:

This is very fast. But there are some problems fitting bin boundaries. It is not available in earlier versions of Excel