3 Ways to Make a Histogram in Microsoft Excel

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?

An example of 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.

Overflow and underflow bins
Overflow and underflow bins

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

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

Choosing histogram
Choosing histogram

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.

Format data series
Format data series

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.

Horizontal axis
Horizontal axis

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.

Bin width
Bin width

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.

Number of bins
Number of bins

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.

Overflow and underflow bins
Overflow and underflow bins

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.

Gap width
Gap width

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.

Add-ins dialog
Add-ins dialog

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.

Bin range
Bin range

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.

Get histogram tool
Get histogram tool

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.

Histogram dialog
Histogram dialog

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.
Create histogram
Create histogram

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

Generated a histogram
Generated a histogram

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.

Manual bin column
Manual bin column

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

Bin frequencies
Bin frequencies

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)
Inserting a chart in Excel
Inserting a chart in Excel

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.

Formatting a column
Formatting a column

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

Reducing gap width
Reducing gap width

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.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

2 Comments

  1. Duncan Williamson

    Setting the Gap Width to 5% is a no no because a histogram must, by definition, have all bars touching. Why so? If you are going to find the area under the curve, the gap will interfere with that and if you are going to overlay a probability density function, that would be compromised, too.

    Reply
    • Jon Peltier

      That’s being a bit picky, don’t you think? A 5% gap isn’t much, and it’s the same for all bars. The area under the bars is the sum of the heights times the width of each bin.

      The built-in histogram (which I don’t use; don’t get me started) sets a gap width of 0% and uses a thin white border on the bars.

      My own preference is a gap width of 0%, a border that uses the bar fill color, then making the bars 50% transparent.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃