Reading Time: minutes remaining

How to Create a Histogram Fast Using Excel’s Data Analysis Tool

Jason Morrell

by Jason Morrell 
February 23, 2023

Select Dynamic field

While you can create a Histogram using Excel's standard chart feature, you can also create a frequency distribution using Excel's Data Analysis add-in tool.

And that's the focus of this post.

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.

The frequency distribution data for your histogram

Create the initial Histogram

To create a histogram using the Data Analysis add in:

1.  Ensure you have the Data Analysis tool.

  • Click the File tab, select Options and choose Add Ins.
  • Click the Go button next to Manage Excel Add Ins.
  • Tick the Analysis Toolpak and click OK.
  • Return to your spreadsheet.

2.  Create a simple table which contains your number buckets (bands) between the min and max values.

A simple table is used to show the min and max values for each number bucket

3.  Click the Data tab | Data Analysis tool (right-hand side).

How to create your initial histogram using the Data Analysis tool on the Data ribbon (after adding it in)

4.  In the dialog, choose Histogram and click OK.

5.  Set the Input Range to the numerical range in your original data.

6.  For the Bin Range select the Max range, including the heading.

7.  Tick the Labels box and the Chart Output box.

8.  Select the Output Range option, then select the starting cell for your results.

9.  Click OK. You now have a new data table and a Histogram.

The Data Analysis tool produces a new table (with the data rejigged) and the initial Histogram
The Data Analysis tool produces a new table (with the data rejigged) and the initial Histogram

The histogram is functional, but quite bland. Plenty of room for improvement.

Make the labels more meaningful

In the new data table adapt the values in the Max column to show a numerical range such as 30-39, 40-49 etc. Concatenation is the easiest method. Here’s the new formula for cell G2 in our example.

=D2 & "-" & E2

This is how the table now looks.

After writing a formula to create more meaningful labels, the frequency distribution table now looks like this

Customise the Histogram

1.  Double-click one of the Histogram columns to display the sidebar.

2.  Reduce the Gap Width to 5% (or whatever your preference is).

3.  Set the Series Overlap to 0%

4.  Change the Horizontal axis label to Student Test Score.

5.  Change the title to Frequency Distribution of Student Test Scores.

6.  Remove the Legend.

7.  Change the colours etc.

Format the histogram's gap width, series overlap, horizontal axis label, title and colours

8.  Remove the last column of the chart (which always says ‘More’) by clicking the Select Data icon on the Chart Tools Design ribbon, then reduce the current selection by one row.

9.  If you want to combine the first two or last two columns of data because the numbers are so small, then make the adjustments manually, then set the data range ads you did in the last step. This achieves the same end result as the Overflow bins and Underflow bins when creating a Histogram as a standard chart type.

For the outer extremes of the data where the frequency is tiny, consider combining 2 or more numerical bins into one

10.  Play with the Chart Options gallery to check if there is a nicer design ...

The Chart Options gallery provides a quick way to add a professional finish to your histogram

11.  … and the Quick Layouts to see if there is an option that shows your histogram data more effectively..

The Quick Layouts provides a quick way to re-configure every option on your histogram

The final result

With a few quick tweaks, the Histogram / Frequency Distribution can evolve from …

Histogram / Frequency Distribution - Start Point

to

Histogram / Frequency Distribution - End Point

If you want to see a more detailed breakdown of your data, just create more numerical bins, then create a new frequency table and histogram using the Data Analysis Tool.

This is how the Frequency Distribution and Histogram finally looks after using the Data Analysis tool and the Chart Options

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"}
>