How to Make a Histogram in Excel?—2 Best Methods
Note: This tutorial on how to make a histogram in Excel is suitable for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Office 365 users.
In this guide, I will show you how to create a histogram in Excel, the easy way. Histograms add a power punch to your reports. They are powerful data visualization tools that help you gain insights and tell a story based on your data.
I’ll cover :
Find the sample Excel Sheet used in this guide, below. You can use it to follow along with me.
Related:
How To Find Duplicates In Excel? The Best Guide
Excel Goal Seek—the Easiest Guide (3 Examples)
Create A Pivot Table In Excel—the Easiest Guide
Excel Histogram Overview
A histogram is a column chart that displays the frequency of a variable in a given range. It is very similar to bar charts, except that it groups data together in user-specified intervals called bins.
This is very helpful for easy data interpretation.
Let’s say, you are a teacher who just finished evaluating your students’ performance. Now, you want to find how many students have failed the test and how many students have performed well.
For this, you create three intervals or bins, namely, poor performance, average performance and good performance. Using an Excel Histogram, you can plot the number of students in these three intervals easily.
How to Make a Histogram in Excel?
There are two easy ways to add an Excel Histogram. If you are using Excel versions 2013 or earlier, you can add them using the Data Analysis Tool.
In later versions of Excel, there is a ready-made Histogram Chart tool available in the Charts section. I’ll explain both these methods using simple examples.
How to Make a Histogram in Excel Using Data Analysis Tool?
To add Excel Histogram using the Data Analysis tool pack, follow these steps.
- Get your data ready. I have the list of students along with their marks in this example.
- Enter the upper limits of each bin (interval) in a separate column, anywhere in the sheet.
I have entered the upper limits in column D, starting from D2. I have specified 3 upper limits, which means I want to have three bins.
- Click on the Data Analysis button under the Data tab.
- In the Data Analysis menu, select Histogram and click OK.
- Select the Input Range for the Histogram. In my example, it is going to be B2:B21.
- Select the Bin Range for the Histogram. In this example, it is D2:D4.
- Select a suitable Output Range for the Histogram. I have selected cell F2.
- Check the Chart Output tick box and click OK.
- To change the gap width between bins, right-click on any one of the bars and click on the Format Data Series button. Also, remove unwanted intrevals by unchecking them in the Values filter as shown below.
Also Read:
How To Use Excel Countifs: The Best Guide
Excel Conditional Formatting -the Best Guide (Bonus Video)
The Best Excel Project Management Template In 2021
How to Make a Histogram in Excel Using the Histogram Chart Tool?
Let’s see how to make a frequency Histogram in Excel using the Histogram Chart Tool:
- Select the Data Range. I have selected the range B2:B21 in this example.
- Locate and click on the Histogram option under the Charts group in the Insert tab.
- After Excel plots the Histogram, you can format it if needed. To format, right-click on the Horizontal axis and then on the Format Axis option.
- Change the Number of Bins and the Bin Width in the Format Axis pane.
Specify the number of bins, the range of overflow and underflow bins here to make Excel plot your histogram properly.
You can change the gap between the bins by right-clicking on the bins and changing the Gap Width in the Format Data Series pane.
Suggested Reads:
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
Predict Future Values Using Excel Forecast Sheet – The Best Guide
FAQs
What is a histogram chart used for in Excel?
A histogram displays the frequency of a variable in certain intervals (bins) in the form of a column chart. It simplifies data visualisation to a great extent.
Is a histogram suitable for continuous data?
Histograms are suitable for plotting both continuous and discrete data, as long as they need to be measured on an interval scale.
What is the difference between a histogram vs bar graph?
Though histograms and bar graphs may look very similar, they are very different from each other.
Histograms present the frequency distribution of a quantitative variable in a single chart. Bar graphs on the other hand compare different categorical variables in a single chart.
Let’s Wrap Up
In this guide, I have walked you through how to make a histogram in Excel. We also saw, how to change the bin width and range of a histogram. If you have any doubts regarding histograms or any other Excel feature, please let us know in the comments below.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advance Excel courses with in-depth training modules.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.