How to Make a Histogram Chart in Excel [StepByStep]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A histogram is the best chart you can use to illustrate the frequency distribution of your data.

Before Excel 2016, making a histogram is a bit tedious. But now, you can make one in a matter of seconds.

In this article, we’ll look a bit deeper at what histogram is, its advantages, when not to use it, and how to make one.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s hit it!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

What Is a Histogram?

A histogram is a popular analysis tool used in the business world.

Basically, it is a representation of data points into ranges. It compresses the data for easy interpretation by grouping the points into ranges or bins.

To use a histogram, you have to make sure you can “bin” the values of your data (or divide them into ranges).

Kasper Langmann, Co-founder of Spreadsheeto

With this, you’re able to see and interpret the frequency — the underlying distribution of your data. 

Histograms answer the question: “how many points belong to what range”.

Why Use a Histogram?

Despite the different visuals and charts in Excel, why use a histogram? What can you possibly gain from it?

Here are some advantages of a histogram:

1. Spot and track special trends

If you’re plotting a time-related data, a histogram can help you spot and track trends.

For example, you noticed that the complaints you received every month seem to have a pattern. So, you started tracking the number of complaints you receive over a 12-month period.

Plotting the distribution frequency of the complaints might reveal specific months when the number of complaints spike.

2. Checking Distribution Equality

Histograms are a great way to verify the equality of data points distribution.

From a glance, you’ll immediately see whether you had equal distribution or not.

3. Data Spread

Basically, the bars in a histogram represents the data points belonging to that range.

One look and you’ll know how the data is spread among the ranges.

Now that you know why histograms are useful, let’s talk about how to create one.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Using the Built-In Histogram Chart

This is the easiest and fastest way to make a histogram in Excel. However, only Excel 2016 version and above have this capability.

Histogram charts belong to one of the 6 new charts introduced in Excel 2016.

Kasper Langmann, Co-founder of Spreadsheeto

To start, let’s say you’re someone who just took over a software company called ‘Digital Maker’ with 150 employees. 

Now, you wanted to see the salary distribution on the company so you asked the HR to give you a copy.

Here’s a copy of the spreadsheet they gave you (the HR asked to blot out the names of the employees for their sake 😉):

data overview for this exercise

The first step is to highlight the range of data including the headers. In this case, it’s A2:B152.

highlighting the data for this exercise

Pro Tip: Extend the Selection to the Last Cell Down

If you have big data, using a mouse would only slow you down. Use keyboard shortcuts instead.

Hold down Shift and click the first cells of the columns that have your data. In this case, it’s A2 and B2.

Then, while still holding down Shift, hold Ctrl (Command for Mac) + Arrow Down.

extend the selection to the last cell down by holding control, shift, and arrow down

After you highlight the data, click ‘Insert’ from the tab list.

insert option on the tab list

After that, click on the ‘Insert Statistic Chart’ and select Histogram’.

histogram under the insert statistic chart option

Now you have a histogram based on the data set.

excel 2019 histogram

Changing the Axis Formats

There are times when you have to change how the values are presented in the chart.

Excel automatically computes the best values to show in the vertical axis and horizontal axis of your histogram.

Kasper Langmann, Co-founder of Spreadsheeto

If you need to change them, you can do so easily

For instance, you want to change the maximum value displayed on the vertical axis to 50.

Right-click on anywhere on the vertical axis and select ‘Format Axis’ from the dropdown. This will open a window on the right-hand side of your screen.

right-click anywhere on the axis and select format axis from the dropdown

Then, change the ‘Maximum’ value to 50 and press Enter.

change the maximum value and press enter

If you decide later on to restore the value to what Excel initially computed, go back to this window and press the ‘Reset’ button beside it.

Kasper Langmann, Co-founder of Spreadsheeto

Of course, you can also change the number of bins and the bin width of your histogram.

To do so, right-click on anywhere on the horizontal axis and select ‘Format Axis’ from the dropdown. Like earlier, this will open a window on the right-hand side of your screen.

right-click anywhere on the axis and select format axis from the dropdown

To change the bin width, replace the number beside ‘Bin width’ with what you have in mind and press Enter.

Let’s say you want to change it to 500.

replace the value beside bin width and press enter

The bin width is the size of your bins. It’s the difference between the ranges showed.

The default is 590 which gives you bins like [2,031, 2,621], [2,621, 3,211], etc…

bin width on a histogram

After changing the width to 500, it becomes [2,031, 2,531], [2,531, 3,031], etc…

difference in bin width after changing to 500

You can change the number of bins in the same way.

Let’s say you want to have 7 bins, then simply change the current ‘Number of bins’ and press Enter.

change the current number of bins and press enter

Of course, doing this will change the bin width and sometimes, the frequency too.

Here’s what it currently looks with 6 bins:

with only 6 bins

After changing it to 7 bins, here’s how it looks now:

chart with 7 bins

When Not to Use a Histogram?

Knowing when to use one is important. But it’s also imperative to understand when you shouldn’t.

Because histograms are designed to show the distribution of frequencies (only), you can’t expect to see individual data on specific points.

Kasper Langmann, Co-founder of Spreadsheeto

If that’s what you need, you’re better off using a bar graph.

Histograms are different from pie charts. Although both offer visualization of groupings, they don’t have the same purpose.

Pie charts are used to illustrate the relationship of a point against the total whole.

Lastly, a histogram can’t be used to show changes over time. You can only see the distribution of points over a time period. Line charts are the best tool for such a job.

Conclusion

Histograms outshine other graphs at presenting the distribution frequency of a data. And with the recent addition of Excel’s built-in Histogram chart, you can create one in just a matter of seconds.

When in doubt, check first if the purpose you have in mind is related to your data’s distribution frequency. If yes, then a histogram is what you need.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-02T12:54:40+00:00