How to Create a HISTOGRAM in Excel – Step by Step

It’s a step by step tutorial to create a HISTOGRAM in Excel in different ways in a different version of Excel.

Excel Histogram Chart

A histogram is a column or a bar chart that represents values using a range. Think of a class teacher who wants to present marks of students using a category range (Bins). In the class, she has 50 students and to present the performance of the class, she can use the distribution of students according to their marks using a histogram.

steps to create a histogram in excel all version

In this guide, I’ll be sharing with you everything you need to master this chart. So stay with me for the next few minutes…

  • 2016
  • 2013
  • Mac
  • Pivot
  • Other

Steps to Make a Histogram in Excel 2016

If you are like me, using Excel 2016 I've got  good news for you and the good news is, there is an inbuilt option to create a histogram in this version of Excel.

employee data to create a histogram in excel

And now you need to create a histogram which should show the number of employees in a particular period range (Bin).

Follow these simple steps:

  • First of all, select the data and go to the Insert Tab Charting Insert Statistic Charts Histogram Histogram, to insert the chart.
go to data tab to create a histogram in excel 2016

In reality, this is the only step you need to follow, your chart is there in your spreadsheet.

insert excel histogram from data tab charts

You can delete the chart title and gridlines if you want.

excel histogram 2016 without chart title gridlines

But now you need to have look at how can you customize it and all the options you have with it.

Let’s explore some of the main options related to bins one by one:

  • Now, select your chart’s axis and right-click on it and open “Format Axis”.
open format axis option to change settings for the chart

1. By Categories

If you think about the data we are using here, every entry is unique as each employee’s name is different.

excel histogram 2016 with categories

But if you have data where you have repetitive entries, then you can use this option to create a chart based on the SUM or COUNT of those categories instead of numeric bins.

In this case, the bins would be the categories not the distribution of numerical data.

2 Automatic

When you create a histogram, Excel creates bins automatically using the range of the values you have. In simple words, this option is the default way to make a histogram.

3. Bin Width

By using this option, you can specify the width of the Bins.

Let’s say if you want to make each bin have 10 then this will create each bin of 0-10, 10-20 and so on.

With this option you have control over the width of the bin and Excel will create the number of bins using width you specified.

Let’s say you have values from 1 to 20 and you want bin width 4, then Excel will create 5 bins like this:

excel histogram with specific bin width

4. Number of Bin

And if you want to specify the number of bins instead of the range you can click on this option and enter the number.

Let’s say if you want to have 3 bins and the range of values is 0-15 then Excel will create bins like:

excel histogram with specific number of bins

5. Overflow Bin Underflow Bin

These two options help you to create more than or less than bins in the chart.

Let say, you want to have a bin for the employees with a working period of fewer than 10 years.

You can simply enter 10 in the underflow.

Or, if you want to have Bin for the employees having a working period of more than 20 years, enter 20 in the overflow.

excel histogram with overflow underflow bins

When You Need to Create a Histogram

Creating a histogram in Excel is not rocket science, but you need to aware of the situations where it can be helpful and useful.

1. To Present Large Data

Remember the example of the students? Sometimes it’s hard to present data in a normal chart (Line, Column, or Bar). But with a histogram, you can make bins and present large data in a simple chart.

2. To Understand the Distribution

A histogram not only distributes data into bins but also helps you to understand the distribution. When you look at a below histogram you can understand that most of the students scored between 40 to 80. And 23 students scored above 60.

3. To Help in Decision Making

By using a histogram you can easily decide the students who need to work on their performance. Once you know the numbers in distribution you can separately focus on each distribution.

What are Bins?

Bin (Bucket) is a range of values in which you distribute the frequency of occurrence. Normally, when someone makes a histogram he/she needs to create bins before but in while making a histogram in Excel you don’t need to create bins.

In the end

If you are using Excel 2016 then there is a simple one-click way to create a histogram as it’s there already. Otherwise, you can use “Analysis Tool Pack”, but you need to install it first to use it.

Well, if you ask me, my favorite method to make a histogram is by using a pivot chart as it’s way more dynamic and gives you more options to control the chart.

But now you have to tell me one thing. Which method is your favorite?

Make sure to share your views with me in the comment section, I'd love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.