You can easily create a histogram using the Data Analysis ToolPak or the built-in histogram chart in Excel.
A histogram is a graphical representation of the frequency distribution of discrete or continuous data. It is similar to a vertical bar graph. However, Histograms are used to show distributions of data while bar charts are used to compare data. A histogram, unlike a bar chart, shows no gaps between the bars.
A histogram is the best chart you can use to illustrate the frequency distribution of your numerical data. In Excel, you can create a histogram using the Data Analysis ToolPak or a built-in histogram chart. Now, let’s see how to create a Histogram in Excel.
Installing the Data Analysis Tool Pack
The Histogram tool is not available in Excel by default. To access it, you need to install Analysis ToolPak Add-in on Excel. Once the Add-in is installed, the Histogram will be made available in the list of Analysis Tools or in the charts group.
To install Analysis ToolPak Add-in, open the ‘File’ menu in Excel.
In the ‘File’ menu, open ‘Options’ dialog box.
In ‘Excel Options’, Click on the ‘Add-ins’ tab on the left side.
Here, you can view and manage your Microsoft Excel Add-ins. Select ‘Excel Add-in’ from the ‘Manage:’ drop-down at the bottom of the window and click ‘Go’.
Then, check ‘Analysis ToolPak’ checkbox in the Add-ins dialog box and click ‘OK’.
Now, histogram tool is available in Excel, let’s see how to create to one.
Creating a Histogram using Charts
First, create a dataset and select the range of cells containing the data to be presented using the histogram.
For example, let’s create a dataset for number of students in 10 classes as shown below:
Select the range of cells and go to the ‘Insert’ tab. The histogram chart type is now available in the ‘Charts’ group of the Insert tab.
Click on the histogram icon and select your histogram chart type.
Now, the histogram is created and the distribution of data grouped into bins as shown below.
Once the histogram is created, you can further customize it in the ‘Design’ tab of Excel. You can add chart elements, change the colors of the bars, change chart styles, and switch rows and columns.
To format the X-Axis and Y-axis of the chart, right-click anywhere on the axis and select ‘Format Axis’ from the dropdown.
This will open a window on the right-hand side of your screen. Here, you can further customize your axis to fit your need. You can change bin width, bin grouping, number of bins on the chart, etc.
For example, when we created the chart, Excel automatically made data into three-bin groupings. If we change the number of bins to 6, the data will be grouped into 6 bins.
The result is shown in the following picture.
Creating Histogram using Data Analysis Tool
Another way we can create a histogram is using the Data Analysis toolpak. For creating a histogram, first, we need to create a data set and then data intervals (bins) at which we want to find the data frequency.
In the following example, column A and B contain the data set, and column D contains bins or mark intervals. We need to specify these bins separately.
Then, go to the ‘Data’ tab and click ‘Data Analysis’ in the Excel Ribbon.
In the Data Analysis dialog box, select ‘Histogram’ from the list and click ‘OK’.
A Histogram dialog box will appear. In the Histogram dialog window, you need to specify the Input range, Bin range, and Output range.
Click on the ‘Input range’ box and select the range B2:B16 (which contains Marks). Then, click on the ‘Bin Range’ box and select the range D2:D9 ( which contains data intervals)
Click the Output Range box and select a cell where you want the frequency distribution table to appear. Then, check ‘Chart Output’ and click ‘OK’.
Now, a Frequency Distribution table is created in the specified cell address along with the Histogram chart.
You can further improve the histogram by replacing the default Bins and Frequency with more relevant axis titles, changing chart style, customizing the chart legend, etc. Also, you can do the formatting of this chart like any other chart.
That’s it. Now, you know how to make a histogram in Excel.