Introduction to How to Make a Histogram in Excel

Perhaps you have made a bar or column graph to signify some numerical data? I bet everyone else has. A histogram is just a particular usage of a column chart wherever each column represents the frequency of elements in a particular range.

Put simply, a histogram graphically exhibits how many elements within the sequential non-overlapping intervals or bins.

Ways to create Histogram in Excel

Creating a histogram is a subject of minutes and can be achieved in a number of ways - by using the particular Histogram tool of the “Analysis ToolPak”, “formulas” or “the PivotTable.” Further on in this informative article,

You will find the step by step description applying Analysis ToolPak method. You can analyse your data and show it in a histogram by using the Histogram tool of the Evaluation ToolPak in Microsoft Office Excel.

Originally, you may need to organize your data in to two columns on the spreadsheet: one column data and another for bin numbers. Input data is meant to analyses whereas bin numbers signify the intervals you want the Histogram tool to utilize these data.

Here in this article you could go through a typical example of how each one of these works and everything will become clearer if you are currently confused in generating a histogram.

You need to use histogram when:

Likewise, you can make a histogram to display the amount of days with a temperature between 20-30, 31-40, 41-50, etc. degrees, the amount of sales with amounts between $100-$200, $200-$300, $400-$500, the quantity of students with test scores between 41-60, 61-80, 81-100 percentage, and so on.

Note: To use the Histogram tool, you first need to recognize the bins (categories) that you intend to use to produce a frequency distribution. A histogram displays the same information in that column chart.

To make sure that the Analysis ToolPak Add-in is installed in Excel, follow these simple steps:

Firstly, On the File menu, click Options.

Histogram in excel 1

Then click Add- Ins in the navigation pane as below.

Histogram in excel 2

Finally, in the Manage list, select Excel Add-ins, and then click Go. Also make sure that the Analysis ToolPak check box under Add-Ins available is selected, and then click OK.

Histogram in excel 3

To create a histogram in excel, follow these simple steps

Step 1:  On a new spreadsheet, type the input data in one column, adding a label in the first cell if you want. In this example, I have Student Roll numbers in column D and their corresponding height in column E.
Histogram in excel 4

Step 2: Again, in the next column, type the bin numbers in ascending order with a particular range. It's advisable to utilize your personal bin numbers because they might be more helpful for your analysis. Here I have given a range of 5. Please see the screenshot below.

Histogram in excel 5

Step 3: On the Data tab, just click the Data Analysis button right corner. Thereafter, In the Data Analysis dialog, select Histogram and click OK.

Step 4: Following the completion of another step, you'll need to specify the Input range and the Bin range. To do this, you are able to place the cursor in the box, and then simply find the corresponding range on your own worksheet utilizing the mouse.

 Alternatively, you are able to click the Collapse Dialog button, select the number on the sheet, and then click the Collapse Dialog button again to return to the Histogram dialogue box.

Additionally, select the Output options.

Put the histogram on a single sheet, click Output Range, and then enter the upper-left cell of the output table.

To represent data in the output table in descending order, check the box.

To include a cumulative percentage line in your Excel histogram chart, select the Cumulative Percentage box.

Create an embedded histogram chart, select the Chart Output box.

Because of this example, I've configured the next options in the screenshot:

Step 5: And now, click OK, and you can review similar histogram graph underneath.

Customize the Histogram

As histogram display right through to your spreadsheet.  The following point perhaps you have the ability to complete would be to customize it somewhat to produce it more appealing.  You can replace the default Bins and Frequency with an increase of meaningful axis titles, customize the chart legend, etc

Additionally, if you click histogram column it'll make Chart Tools seem on the ribbon.  Including an overall total of three various tabs:  Design, Layout and Format. Several style types is seen in a drop down box when clicked. You have the ability to search up and down through the alternatives by hitting the arrows to the proper of the box.

Press any bar symbol to pick that model for the graph. You may also utilize the Chart Layouts and Chart Styles galleries to help expand refine the prevailing histogram.

How to Make a Histogram in Excel Add Data Labels on an Excel histogram chart

When creating a histogram in Excel with the Analysis ToolPak, Excel adds the data labels based on the bin numbers that you specify. For this, you'd need to Right-click any of the columns in the X axis, and click Add Data Labels

Finally, you’ll have your final histogram chart like this.


Done! As you've just seen, it's quite simple to generate a histogram in Exceed utilising the Analysis ToolPak.