How to make a histogram in Excel – 3 easy ways

If you want to learn how to make a histogram in Excel, we’ve got your back.

A histogram is a graph similar to a bar chart that represents numerical data. These graphs can be used for continuous and discrete data, making them valuable tools for companies to see data distribution over a set of predetermined ranges.

On a standard bar chart, which measures discrete data, all bars are kept separate, while on a histogram, they often touch each other. This means that you can use a histogram to show the distribution of numerical data across several categories or demographics (often described as bins). 

This guide will list three simple methods through which you can create histograms in Excel. So, without wasting another moment, let’s get right to it.

Creating a Histogram in Excel

Scenario on hand: We have a dataset with student marks over the term and their final percentage.  

What we want to accomplish: Explore how to make a Histogram in Excel in the following ways:

• Create a Histogram using the Data Analysis ToolPak
• Create a Histogram using the Recommended Charts 
• Create a Histogram using a simple bar chart  

Create a Histogram using the Data Analysis ToolPak

In this first method, we create a Histogram using the Data Analysis ToolPak. 

Step 1: Download the Data Analysis ToolPak

You will first have to download the Analysis ToolPak for the first method. This will create a new tab under ‘Data’ called ‘Data Analysis’.

Head to File > More… > Options 

This will open a window where you select Add-ins > Manage > Excel Add-ins > Go.

Doing this will open another, smaller window. 

Here, you will check the ‘Analysis ToolPak and click ‘OK.’

This will unlock a ‘Data Analysis’ option under the Data tab on the ribbon:

Step 2: Prepare the dataset

Before you insert a Histogram using the new Add-in, you should make sure that your dataset is ready. 

For our demonstration, we have nine students with the marks they secured in different assessments throughout the term and the total percentage they secured. 

For this first method, we have already defined the bins or the grading criteria for the class.

Step 3: Insert the Histogram 

After preparing the data, you can insert the Histogram. Simply head to Data > Data Analysis > Select Histogram > Click ‘OK.’

Now define the Input Range, Bin Range, and Output Range. 

For our demonstration, we chose the final percentages of the students as the Input Range, numeric grading criteria in the Bin Range, and a cell beside the data as the Output Range. 

Enable the ‘Chart Output’ to get a histogram chart along with the calculated bin frequencies. 

Lastly, click ‘OK.’

Here is the histogram we get:

Step 4: Editing the Histogram

Even though we have used the Histogram option from the Data Analysis ToolPak, we have to make a couple of adjustments to make sure that the chart looks like a histogram, where the bars are touching each other. 

Once you have the bar chart, select one of the blue bars. This will open a format menu on the left. 

Alternatively, you can also right-click on the chart and click ‘Format Data Series’ to open this menu. 

In the ‘Format Data Series’, head to the Series options and set the Gap Width to 0%. 

You can also change the data labels on the chart by changing the generated bin and frequency table. For our demonstration, we want to change the labels to the grade.

Create a Histogram using the Recommended Charts 

You can also create a Histogram using the Recommended Charts option. 

Step 1: Select the data 

The first step is selecting the data for which you want to plot the histogram chart. 

Step 2:Insert the Histogram 

Go to Insert > Recommended charts > Histogram 

Here is the Histogram we get using this option:

Step 3: Defining the bin size 

The histogram created using this method will only be accurate if you define the bin size. Since we only selected the data to create a histogram, Excel automatically calculated bin sizes, which were inaccurate. 

In our data, five students got a C, two got a B, and two got an A. However, we do not see that accurately depicted on the Histogram through the Recommended Charts option. 

To define the bin size, we right-click on the data labels at the bottom and select ‘Format Axis.’ This will open up an editor window on the left that lets you define the bins.

Here, under the bar option, we define the number of bins, the overflow bin, and the underflow bin. We get this result:

Although creating a histogram using the ‘Recommended Charts’ option might seem like the easiest way, it is not the most efficient. 

You cannot define the bins on your own; therefore, you are confined by the tool’s abilities. 

Create a Histogram using a simple bar chart

A great way to create a histogram is by calculating the frequencies using a COUNTIFS formula. 

Step 1: Define the bins and calculate the frequency

We first define the min and max range of the bins and then calculate the frequency using the formula:

=COUNTIFS({Data column with $},”>=”&{Cell with minimum value},{Data column with $},”<=”&{Cell with maximum value})

Here’s what this formula looks like for our data:

=COUNTIFS($I$6:$I$14,”>=”&L6,$I$6:$I$14,”<=”&M6)

Since we have added the $ sign to the data values for an absolute cell reference, we can extend this formula to the rest of the table using the ‘Fill Handle Tool.”

Step 2: Insert a Clustered Column chart

Next, select the frequency column and head to Insert> Charts > Clustered Column.

Step 3: Edit the chart

Once you have the bar chart, select one of the blue bars. This will open a format menu on the left. 

Alternatively, right-click on the chart and click ‘Format Data Series’ to open this menu. 

In the ‘Format Data Series’, head to the Series options and reduce the Gap Width to 0%.

Frequently asked questions

What is a bin range when creating a Histogram?

Creating Bins is a must-must step in creating a Histogram. Bins are buckets of different sizes. You have to distribute the dataset into those buckets to calculate the frequency, and then, you can plot a bar chart with bin size vs frequency. 

In short, the bins are described as continuous and not overlapping intervals of the variable. The intervals can be homogenous or not to create a histogram, but they should be continuous. 

What is the difference between a Histogram and a Bar Chart?

On a standard bar chart, which measures discrete data, all of the bars are kept separate, while on a histogram, the data is continuous; therefore, the bars touch each other.

How do you define the number of bins in a Histogram?

You can determine the number of bins for your histogram based on the size of your dataset. Although the automatic Histogram chart calculates the number of bins on its own, you should give it the number of bins yourself to get the best representation of your data. 

Can I use a Histogram on small data sets?

You can use Histograms for both large and small datasets. However, Histograms are mostly used for larger datasets because they can show patterns in the data. 

Wrapping up

This marks the end of our guide. Hopefully, you now have a better understanding of how to create histograms and the value they can provide. Whether you want to visualize multiple data sets across regions for a meeting or use it for data analysis, Excel and its capabilities, such as creating Histograms, are incredibly useful. That said, if you are interested in expanding your knowledge about Excel, we recommend you check out some of the guides below.

• How to use VLOOKUP with COUNTIF in Excel
• Finding the second match with VLOOKUP in Excel
• How to use VLOOKUP to return multiple values horizontally in Excel