Let's Dive Right In
Histograms are typically used in statistical analysis, in particular where the data falls into a conventional bell curve, with the larger frequencies of data in the middle range and the smaller frequencies of data on the outer edges.
There are 2 ways to create a histogram
- Use the standard chart feature.
- Use the Data Analysis Add-In.
Consider this student data which is arranged in two columns, one for name and one for test score. The more data you have the more useful the histogram is.
To create a histogram chart using the chart feature:
1. Select a single cell in your data to select the entire range.
2. Select the Insert tab.
3. Click the Statistical chart icon in the Charts group, then choose Histogram.
Alternatively, go to the Recommended Charts | All Charts tab | Histogram.
The initial chart looks like this.
Notice how it has broken up the data into 9 main numerical groups and displayed these in square brackets on the horizontal axis.
To set up the numerical bins
1. Double click one of the numerical data values on the bottom axis to display the sidebar.
2. In the sidebar under Format Axis | Axis Options | Axis Options, either set the Bin Width to the size of the number group or set the Number of bins.
3. For the small values at either end of the bell curve, you can collect them together into one bucket by setting the values in the Overflow Bin and Underflow Bin.
In the settings (pictured right) the Overflow bin has been set to 80 so any score over 80 is collected into the final bin
Here’s how that plays out on the chart:
To change the gap width between columns
1. Double-click one of the histogram columns to display the sidebar.
2. In the sidebar under Format Data Series | Series Options | Series Options, set the Gap Width.
For no gap choose 0%.
It is always worth checking out the Chart Styles and Quick Layouts on the Chart Tools Design ribbon for different configurations. This provides a great styling start point. Customise from there.
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!