Reading Time: minutes remaining

How to Create a Histogram Fast Using a Standard Excel Chart Type

Jason Morrell

by Jason Morrell 
April 12, 2020

Select Dynamic field

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

  1. Use the standard chart feature.
  2. Use the Data Analysis Add-In.

Example Data

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.

Example Histogram data

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.

How to find the Histogram chart type

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.

The initial Histogram will look like this

Numerical Bins

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

Your Histogram's Numerical Bins are controlled by the Bin Width, Overflow bin and Underflow bin settings

Here’s how that plays out on the chart:

Histogram (Method 1: Regular Chart)

Gap Width

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

The Gap Width on the Histogram can be changed

Styling

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.

Use the Chart Styles and Quick Layouts to find 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.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


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!

SHARE

POPULAR EXCEL POSTS

Radar charts in Excel


Sunburst charts in Excel


Box plots (Box & Whisker charts) in Excel


Waterfall charts in Excel


Funnel charts in Excel


VLOOKUP de-mystified


How to fix broken formulas fast


3 Ways To Insert a Degree Symbol In Excel


11 little-known sorting secrets (wait until you see 5, 6 and 9)

«« SEE ALL EXCEL POSTS »»

more from

Office Mastery logo
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>