Excel HISTOGRAM Chart (Create in 2016, 2013, and MAC 2011)

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, that there is an inbuilt option to create a histogram in this version of Excel.

Let’s suppose you have a list of employees (here is the list) and their period of employment with the company.

employee data to create a histogram in excel

And now you need to create a histogram that 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

Pro Tip When you’re using Excel 2016 make sure to convert your data into an Excel table to make it dynamic. So whenever you add new data to it, your chart gets updated automatically.

…download this sample file from here to learn more.

But now you need to have a 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.

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 the 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:

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 a Bin for the employees having a working period of more than 20 years, enter 20 in the overflow.

excel histogram with overflow underflow bins

Steps to Create a Histogram Chart in Excel 2013 (Data Analysis Tool Pack)

If you are using Excel 2013 or a lower version then you need to follow different steps to create this chart.

There is ADD-IN which you need to install, “Analysis Tool Pack”. It comes with your Excel but you need to install it.

So let’s learn to install it (I’m using screenshots for Excel 2016 but the steps are the same in all the Excel versions) and then we’ll get into making a histogram.

Steps to Install Data Analysis Tool Pack

First of all, click on the file tab and open “Excel Options”.
Now from here, go to Add-In and open “Excel Add-Ins”.
And, tick-mark the “Analysis Took Pack” and click OK.

Once you click OK, it will process for a few seconds and you’re ready to go. You’ll have a “Data Analysis” button in the Data tab.

data analysis button to create histogram

…make sure to download this sample data file from here to follow along.

Before you create a histogram using “Analysis Took Pack” you need to create Bins. Yes, manually. but it’s a simple thing, you know. Here we need bins like below or whatever you want to make.

create bins to create histogram in excel 2013

Now let’s follow these simple steps to insert the histogram and make sure you have this data file with you to follow along.

First of all, go to the data tab and click on “Data Analysis”.
Now, from the data analysis menu, select “Histogram” and click OK.
At this point, you have a histogram window just like below and from this window, you need to specify the following details:

Input Range: The range where you have employee years with the company (Make sure to only select the values without heading).
Bin Range: The range where you have bins, yes the one we have just created (Make sure to only select the values without heading).
Output Option: Select the “New Worksheet” for the output and tick mark the “Chart Output” for the chart.

In the end, click OK.

Once you click OK you’ll have a new worksheet with a chart and a frequency.

…here’s the sample file with a histogram created using “Analysis Tool Pack”.

Important Points You Need to Understand when you are using “Analysis Took Pack” to Create a Histogram in Excel

The first bin includes lower than the value from itself and the rest of the bins include the lower than values from itself and greater values from the previous bin.
Apart from the bins you create, it adds an extra bin with the name of “More” to show values more than the last bin. Even if you don’t have a value greater than the last bin it adds “More” as a bin.
“Analysis Took Pack” creates a chart that is not dynamic. When you add a new value in the main data it will not update it, so you need to create a new chart.
If you skip specifying the bins, it will automatically choose bins and creates the chart.
When you create a histogram using “Analysis Took Pack” you can’t undo it, you need to delete it and create a new one if you want to make changes.

Creating a Histogram in the Excel for Mac [2011]

To create a histogram in the Mac version of Excel [2011] we need to install the “Analysis Tool Pack” as well.

…make sure to download this sample file to follow along.

Activating “Analysis Tool Pack” in Excel Mac

Follow these simple steps to activate “Analysis Tool Pack” in Excel Mac

First of all, go to Tool Menu and click on “Excel Add-Ins”.
Now from the add-in window, tick mark “Analysis Tool Pack” and click OK.
This will instantly add the “Data Analysis” button into the Data Tab.

Now the next step.

Step to Create a Histogram [Mac Excel]

Before you create your chart, make sure to create the bins as we have made in the above method. And, if you don’t know about the Bins, make sure to read about them in the above section of this post.

First of all, go to the data tab and click on “Data Analysis” button.
From the data analysis window, select “Histogram” and click OK.
Once you click OK, you’ll have the histogram dialog box where you need to enter the following:

Input Range: The range where you have employee years with the company (Make sure to only select the values without heading).
Bin Range: The range where you have bins, yes the one we have just created (Make sure to only select the values without heading).
Output Option: Select the “New Worksheet” for the output and tick mark the “Chart Output” for the chart.

After that, click OK.

Once you click OK, it’ll insert a new worksheet with the frequency table and a histogram.

…download the final file from here to learn more.

Create a Histogram using the Pivot table and Pivot Chart

When it comes to control and flexibility there is nothing like a pivot table and pivot chart. I have found a simple way to create a histogram by combining a pivot table and a pivot chart. The below chart is a pivot chart, can you believe it?

create a histogram in excel using pivot chart and pivot table

Let me tell you how it works (with the same employee data here).

First of all, you need to create a pivot table (or you can also create a pivot table and a pivot chart in one go).
For this go to Insert Tab ➜ Charts ➜ Pivot Chart ➜ Pivot Chart and Pivot Table.
In the “Create PivotTable” dialog box, click on the “New Worksheet” and click OK.
At this point, you have a blank pivot table and pivot chart in your worksheet.
The next we need to add values, so now, add the column “Employment with Company (in years)” to rows area and into values as well.
Now, for values area, open the “Value Field Setting” and select “Count” instead of “Sum”.
After that, go to the pivot table and right-click on the “Row Label” and click on “Group”.
In the “Group By” window, enter 5 in the “By” and click OK.

Now you need to do a bit of customization in the chart to make it a perfect one.

Select the data series and open data series options. From here, make the “Gap Width” 0.
After that, remove the gridlines and chart title (if you want).
Click on the filter button and “Hide All”.
In the end, right-click on the right axis and open ‘Format Axis“.
In the format axis options, go to Axis Options ➜ Axis Options ➜ Units and enter 1 in the major and 0 in the minor.

Boom! your histogram is ready.

your histgram using a pivot chart and pivot table is ready

…download the final sample from here to learn more.

Dynamic Histogram using FREQUENCY Function

If you create a histogram using “Analysis Tool Pack” it won’t be a dynamic one.

But…

You can create a dynamic histogram by using the FREQUENCY function. To use this function all you need is to create bins in the data (I’m using the same employee data here) and then enter the below formula in the formula bar:

=FREQUENCY(InputDataRange,BinRange)

using freuency function to create a dynamic histogram in excel

Make sure to enter this formula as an array formula by using Alt + Shift + Enter.

After that, you just need to insert a normal column chart using the table we have just created.

once you create table with frequncy formula simply insert a cloumn chart to create a histogram

And don’t forget to change the gap width to 0% from “Format Data Series” options.

make gap width zero to make a histogram in excel using frequency function

…download the sample file from here to learn more.

When You Need to Create a Histogram

Creating a histogram in Excel is not rocket science, but you need to be 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. Looking at the below histogram, you can understand that most of the students scored between 40 and 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 the 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 the “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.

How to Create a HISTOGRAM in Excel – Step by Step