How to make a histogram in Excel – 3 easy ways

Here are three easy methods to create a Histogram in Excel!

Reviewed By: Kevin Pocock

Last Updated on March 6, 2024
How to make a Histogram in Excel
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

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 

Screenshot of an Excel interface showing an open menu with various options, with the "Histogram" option highlighted in a red rectangle.

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

The image shows a screenshot of the add-ins window within the Excel application, with the 'Excel add-ins' option selected and the 'go...' button highlighted, indicating the user is about to manage Histogram

Doing this will open another, smaller window. 

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

This image shows a dialog box for adding add-ins in what appears to be an Excel environment. The 'Analysis ToolPak' option is highlighted, indicating that the user is selecting it to potentially make

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

A screenshot of an Excel spreadsheet displaying a dataset to make a Histogram, including columns for student names, test scores, total marks, final project, total, grade, and bins for grading criteria.

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.

An Excel spreadsheet displaying a dataset with student names, scores from various tests and a final project, total marks, a percentage, assigned grades, and grading criteria in the form of percentage bins to make a

Step 3: Insert the Histogram 

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

A screenshot of an Excel spreadsheet with a dataset for a classroom assignment. The "data" tab is highlighted in red, and the 'data analysis' tool is activated with a histogram option selected in the

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

A screenshot of an Excel program with an opened "find and replace" dialog box over a spreadsheet containing a dataset for credit evaluation, including columns for student names, marks, and grades.

Here is the histogram we get:

An Excel histogram displaying the frequency distribution of a dataset categorized into bins with percentages ranging from 60% to more than 100%, accompanied by a table listing the exact frequency of each bin.

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

A screenshot of an Excel application showing a histogram chart and its corresponding data set. The chart depicts frequency distribution of data categorized into bins, and the focus appears to be on making adjustments to chart design elements

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.

A screenshot showing a histogram titled "Make histogram - student marks" created in Excel, depicting the frequency distribution of grades F through A, within the program's editing interface.

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 

Screenshot of Excel showing a user in the process of making a histogram with a dataset titled "dataset for creating a histogram in Excel" on the left side of the screen and the "insert chart.

Here is the Histogram we get using this option:

Bar chart in Excel titled "Histogram" with two bars showing frequency of data within two range intervals: the first bar represents a range of [73%, 84%] with a frequency of 6

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.

Screenshot of a Microsoft Excel window with an open spreadsheet containing a dataset and a column histogram being edited, showing a context menu for adding vertical axis major gridlines.

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

A histogram in Excel displaying student test scores as a percentage, with an open formatting task pane showing options for 'axis options.'.

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:


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

A screenshot of a Microsoft Excel spreadsheet showing a grading system with various grades from `a` to `f` and the corresponding min and max percentage ranges, a `total marks` column, and a

Step 2: Insert a Clustered Column chart

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

A screenshot of an Excel spreadsheet showing a data set with student test scores, a histogram chart, and frequency distribution analysis using the countifs function.

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. 

A screenshot displaying a section of an Excel histogram chart titled "histogram - student marks," with a context menu open showing various options for modifying the chart, such as adding a trendline or formatting the

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

The image shows a computer screen displaying a histogram chart in Excel, with the chart titled "Histogram - Student Marks." There are data points to the left of the chart indicating student test scores for three.

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.

Kevin is the Editor of PC Guide. He has a broad interest and enthusiasm for consumer electronics, PCs and all things consumer tech - and more than 15 years experience in tech journalism.