How to Make Histogram Using Analysis ToolPak (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

With a continuous set of data, histograms show the shape of a frequency distribution. A histogram aids in the analysis of data graphically for many different applications. A bar chart compares two variables (X and Y), whereas a histogram only analyzes one variable. This is how a histogram varies from a bar chart. You need to get a data set and a sense of how you will bin those values in order to be able to generate a histogram. In this article, we will show you how to make a histogram using the analysis toolpak.

Histogram in Excel

A histogram is a visual depiction that arranges a number of data points into user-specified intervals and is structurally identical to a bar chart. The histogram groups several data points into logical ranges or bins in order to reduce a data series into a widely comprehensible graphic.

Create Histogram Using Analysis ToolPak (Complete Guideline)

You can visually summarize your data with diagrams and graphs. The officials will run through your study if you merely submit a draft with a few key findings. Therefore, extensive charts, graphs, and other visualization techniques are used to generate database and Excel dashboards. In the following steps, we will demonstrate a particular type of bar chart called a histogram, and how to make a histogram using the analysis toolpak. Letâ€™s suppose we have a sample data set.

Step 1: Setting Up Analysis ToolPak

Letâ€™s now demonstrate how to create a histogram utilizing this data using the Data Analysis ToolPak. You will find a command called Data Analysis in the Analysis set of commands under the Data tab. So, at the start of this section, we will set up the Analysis ToolPak in Excel.

• Here, go to the File tab.

• Firstly, select the Add-ins tab from the Excel Options dialog box.
• Secondly, choose the Analysis ToolPak from the Inactive Application Add-ins.
• Besides, you can now select Excel Add-ins from the Manage drop-down menu.
• Now, click on the Goâ€¦ option.

• Here, an Add-ins dialog box will appear.
• Then, choose the Analysis ToolPak from the Add-ins available.
• And, click OK.
• You will now get the Data Analysis command in the Data tabâ€™s Analysis set of commands.

Step 2: Showing Frequency Table

The list has a total of 18 values. And the range is between 18 and 65. So Iâ€™ll use bin size 20 to create the bins. My first bin will therefore range from 0 to 20, and I enter the top value of the bin, which is 20, into cell C5. Data Analysis TookPak requires only the upper value of the bin when making a histogram chart. Then, I put the number 30 in cell C6 because the next bin has a value greater than 20 and less than or equal to 30. I then place the 6 upper values in the bins by selecting all the bins for the 18 values in the dataset.

• At the start of this section, you will see our data table to show the frequency of the data table.

• Now, click on the Data tab first.
• Then, click on the Data Analysis from the Analysis group.

• Here, a Data Analysis dialog box will appear to you.
• Now, select the Histogram tools.
• Then, click OK.

• Firstly, select the Input Range from the above data table.
• Next, enter the Bin Range from the above data table.
• Then, you can choose any cell for the Output Range. Here we choose the E4 cell.
• Now, click OK.

• Finally, you will see the frequency table here.

Step 3: Using Analysis ToolPak to Make Histogram

In this section, we will create a Histogram utilizing the Analysis ToolPak from the Data tab. At first, we will discuss several features that are important to creating a Histogram.

Input Range

It is necessary to enter all the information for the histogram. The data setâ€™s range is the input range.

Bin Range

These basically specify what is put into each bar. Care should be taken when choosing these bins. Select the range of bins to input, making sure that the range is in increasing order.

Labels

Only if the data ranges are chosen along with the data set header and bin data, should this box be checked.

Print Options

Where the histogram prints are regulated by these options. By naming a new worksheet, selecting a range in the present worksheet, or choosing a new workbook, the output can be printed.

Print Output Options

What data is shown depends on these choices. If you need to examine the data in a certain way, you might take a look at these possibilities.

• Here, you will see our data table used to make the data tableâ€™s histogram at the beginning of this section.

• Now, navigate to the Data tab first.
• Then, click on the Data Analysis from the Analysis group.

• Here, you will see a Data Analysis dialog box.
• Now, choose the Histogram tool.
• Then, click OK.

• First, pick an Input Range from the data table above.
• Next, enter the Bin Range from the data table mentioned above.
• Thirdly, choose the New Worksheet Ply to see the histogram in another worksheet.
• After that, choose the Chart Output option.
• Finally, click OK.

• Consequently, you will observe the final histogram bar chart in the below image with the frequency table.
• Here, you will see that we receive two values between 0 and 20, six values between 20 and 30, four values between 30 and 40, and so forth. The figure makes it obvious that there is no pattern in the data. In data analysis, the histogram graphic is a key idea. This method will be helpful to you once you understand how to make a Bell Curve in Excel.

You may download the following Excel workbook for better understanding and practice it by yourself.

Conclusion

In this article, Iâ€™ve covered the step-by-step process of how to make a histogram using Analysis ToolPak. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed

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 a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF