Creating Excel histograms is easy and convenient when you learn how to make a histogram in Excel by following the methods mentioned in this Excel tutorial.

If you’ve been using Excel bar or column charts to compare categorical data visually—such as tracking the highest temperatures over the last 7 days, sales amounts within 15 days, or students’ scores across different subjects — have you considered visualizing the distribution of values in a continuous dataset?

Imagine showcasing the number of discounts ranging from 5% to 25% for a product across various retailers or graphically representing the distribution of students with SAT scores falling into specific ranges. In such scenarios, Excel histograms come to the rescue.

Join me as I explain different approaches to plot histograms in Excel, accommodating both older and the latest Excel editions to ensure compatibility with any version you might be using. Let’s make learning Excel histograms an enjoyable and accessible experience!

📒 Read More: Best Ways To Make a Pie Chart in Microsoft Excel

What Is an Excel Histogram?

A histogram is a statistical chart that shows how numbers are spread out on the X and Y axis. It has bars showing the count of values within specific ranges or “bins.”

The bars can be arranged horizontally or vertically based on the data and what you want to see. The height of each bar shows how often data falls into that range.

Histograms are useful for seeing shapes and patterns in data. This helps you find trends, outliers, and central tendencies.

People often use histograms in statistics and data analysis to explore data sets. They are easy for any audience to understand.

In short, histograms give a visual summary of data, helping with data storytelling. This makes them valuable for making decisions and getting insights from data.

Excel Histogram Using Excel Charts

Before you can begin creating a histogram, the dataset should be structured properly. You can create a histogram from almost any dataset that has enough numerical values in continuation. For example, you want to find out how many students scored between 600 to 800, 800 to 1000, 1000 to 1200, 1200 to 1400, and 1400 to 1600 from the following input dataset:

SAT Scores:
420, 550, 720, 900, 620, 780, 660, 800, 430, 710,
690, 520, 590, 480, 720, 560, 650, 540, 760, 610,
590, 500, 670, 720, 800, 530, 480, 820, 670, 910,
1050, 1120, 1200, 1300, 1250, 1400, 1150, 1320, 1450, 1500  

So, if you’ve got a dataset similar to this structure, go to that worksheet and highlight the whole dataset.

Go to Insert > Recommended Charts to open the Insert Chart dialog.

Go to the All Charts tab from the Recommended Charts tab.

On the left side of the All Charts tab, you should see various Excel chart types. Find and click on the Histogram option.

On the right side, you should see two histogram chart types. These are Histogram and Pareto.

Click on the Histogram chart type and hit the OK button to populate the chart on your worksheet.

After you create the chart, Excel generates bins from the input dataset randomly. You might need a specific range for the bins as explained above in the SAT score example, like, less than 400, 600 to 800, 800 to 100, and so on.

To customize the bin widths, add overflow and underflow bins, etc., right-click on any of the bars on the histogram chart. Click on the Format Data Series option from the context menu.

On the right side of the worksheet, you should see the Format Data Series navigation panel. There, click on the Series Options drop-down and choose the Horizontal Axis option.

The Axis Options configuration will open. There, select the Bin widths option and enter the range you’re looking for. In this example, it’s 200, like 600 to 800, 800 to 100, etc.

If your data is exhaustive and you don’t know the bin width, you can enter a value in the Number of bins field too. In that case, the Bin width option will become inactive.

Now, if you want to create bins for the smallest and largest categories, checkmark the Underflow bin and Overflow bin checkboxes. Then, enter values for each of these fields, like SAT scores less than 600 in one bin and SAT scores above 1400 in another bin.

Finally, to make the columns more discrete than the default style, go to Format Data Series and choose Series Options. Change the Gap Width value to 5% or more.

Excel Histogram Using Data Analysis ToolPak

The previous method is only valid for Excel 2016 and newer editions. If you’re on Excel 2007 to Excel 2013 editions, you need to use the Analysis ToolPak add-in.

First, activate the Excel add-in from the Developer tab > Excel Add-ins > Add-ins dialog box. Checkmark the Analysis ToolPak VBA add-in and click OK to activate the tool.

On the worksheet where you’ve got the dataset, create a list of bin references as shown in the screenshot. It could be any range that you want to see in the final histogram chart.

Now, go to the Data tab and click on the Data Analysis button inside the Analysis commands block.

When you see the Data Analysis dialog, scroll down to the Histogram option and click OK.

Enter the relevant data or values for the following on the Histogram dialog:

• Input Range: The entire dataset you want to use for the histogram plot.
• Bin Range: Select the cell range where you created the manual bin reference earlier.
• Checkmark the Chart Output checkbox.
• For the Output Options, choose New Worksheet Ply.

Hit OK on the Histogram dialog to populate a frequency table and histogram chart on a new worksheet.

You may want to delete the More row on the frequency table to get rid of the same from the histogram.

Excel Histogram Using the FREQUENCY Function

You can create a manual bin range and populate the number of items falling into each bin using the FREQUENCY function. Once you get the number of items for each bin, you can create a bar chart that will work as a histogram.

First, create a column for BIns and enter bin range values as shown in the above screenshot.

Now, select the cell to the right of the lowest bin and enter the following formula into it. For Excel 2016 and newer, hit Enter to generate the frequencies of the bin values. For Excel 2013 and older, press Ctrl + Shift + Enter to generate the bin frequencies.

=FREQUENCY(A2:A41,C2:C8)

Once you’ve got a table like the one in the above screenshot, highlight the table and go to Insert > Recommended Charts.

Choose the recommended clustered column and click OK to generate a bar chart.

Now, right-click on any of the columns inside the chart and go to Format Data Series > Serie Options.

Reduce the Gap Width from the default value to 5% to give the bar chart the look of a histogram chart.

Conclusions

Making a histogram in Excel is easy if you’re in the latest Excel desktop app. You just need to highlight the input data and call the Histogram chart from the Insert > Change Chart Type dialog.

However, if you’re using a dated Excel desktop app, you can use the other methods I described above.

If the article helped you to learn a new Excel data visualization skill, comment below. If I missed any method that would add value to our readership, do mention that in your comment.