How to Make a Histogram in Excel Using Data Analysis ToolPak

This is a step by step guide on how to make a histogram in Excel using Data Analysis ToolPak.

Charts and graphs help you to summarize your data visually. If you publish a report with some values only, the policymakers will stumble on it to get the insight from your report. So Reports or Excel Dashboards are made of huge charts and graphs and other data visualization tools.

In this post, I will talk about a special type of Excel chart. It is histogram chart or you can call it a frequency distribution chart. There are a good number of ways to make a histogram chart. In this blog post, I have shown total 7 ways to make a frequency distribution table. In most of the ways, I have used Excel functions, in one method, I have used Pivot Table. You will also get my FreqGen Excel template to make frequency distribution tables automatically.

But in this blog post, I am going to show you how to make a histogram in Excel in the easiest way: using the Analysis ToolPak Excel add-in.

How to Make a Histogram in Excel

To build a histogram in Excel, I have used these data.

How to make a histogram in Excel Img1

This data is used to make the histogram.

Download the above Excel Workbook!

There are total 40 values in this list. And the values range from 3.24 to 96.76. So, I am going to make the bins with bin size 10. So my first bin will be 0 to 10, I input the upper value of the bin, 10, in cell B2. While creating histogram chart, Data Analysis TookPak needs only the upper value of the bin. Th next bin is greater than 10 and less than or equal to 20, so I input value 20 in cell B3. Now I select these two cells and drag down up to cell B11. That places the 10 upper values of the bins.

How to make a histogram in Excel Img2

Bins are created

Now let’s show you how to use the Data Analysis ToolPak to make a histogram using this data. In the Data tab, and in the Analysis group of commands, you will get a command named Data Analysis. If you don’t see the command there, then the ToolPak is not activated.

Installing Analysis ToolPak

Let me show you how to add Data Analysis Toolpak. Click on the File tab, then Options, and click on the Add-ins tab on the left side of the Excel Options dialog box. On the right side and under the list of Active Application Add-ins, you will not find the Analysis ToolPak. You will find it under the Inactive Application Add-ins.

How to make a histogram in Excel Img3

Installing Analysis TookPak

Let’s activate the add-in. [Above image] under Manage drop-down, Excel Add-ins is already selected. Click on the Go… button on the right. Add-ins dialog box appears. Analysis ToolPak check box is not selected. Just select the checkbox. And click OK.

Now you find the Data Analysis command will be showing under the Analysis group of commands in the Data tab.

Creating Histogram Using Analysis ToolPak

Click on the Data Analysis command in the data tab. Data Analysis dialog box appears. You will find several Data Analysis tools in the window. I select histogram and click OK. Or I can double click on the histogram option. Histogram dialog box appears.

In the Input range, I select these values from cell A2 to A41. In the bin range, I select values in the cells B2 to B11. There are Output options here. You can see your output in three ways:

  1. You can show your output in the same worksheet, in that case, you will select the Output range option and on the right side field, you will set the cell where you want to see the output.
  2. You can also show the output in a new worksheet, by default this option is selected.
  3. Or you can see the output into a new workbook.

I want to see my output in a new worksheet. New Worksheet Ply is selected, it is OK. I want to see the result in a chart also, so I select this Chart Output option. Now I click OK.

Histogram Options

Histogram Options

You see a new worksheet is created. In the worksheet, you get the Bin, the frequency distribution table and you get the histogram chart on the right side of the worksheet.

How to make a histogram in Excel Img5

Histogram with Chart

Let’s analyze the above chart a little bit. Between 0 and 10, we get 3 values, between 10 and 20, we get 6 values, between 20 and 30 we get 4 values and so on. It is clear from the chart that the data does not show any pattern.

Histogram chart is an important concept in data analysis. When you will learn how to create Bell Curve in Excel, this technique will be useful to you.

Histogram chart can show great insights from your sales and other business related data.

This is a sample lecture of my course: Data Analysis in Excel with Statistics: Get Meanings of Data where you will learn Data Analysis with 52 case studies, problems, and their solutions!

Read More



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply