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

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.

Utilizing Analysis ToolPak to Make Histogram

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-by-Step Procedures to Make Histogram Using Analysis ToolPak

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.

Setting Up Analysis ToolPak to Make Histogram

  • 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.

Showing Frequency Table Using Analysis ToolPak

  • 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.


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.

Utilizing Analysis ToolPak to Make Histogram

  • 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.

Utilizing Analysis ToolPak to Make Histogram

  • 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.

Utilizing Analysis ToolPak to Make Histogram

Read More: How to Use Data Analysis Toolpak in Excel 

Download Practice Workbook

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


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.

Related Articles

<< Go Back to Data Analysis with Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF