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.
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.
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.
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:
- 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.
- You can also show the output in a new worksheet, by default this option is selected.
- 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.
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.
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.