How to Make a Histogram in Excel (and Set the Bins Right)

A histogram looks like a bar chart, but it answers a different question. Instead of comparing named things, it shows how often values fall into each range. How many orders came in at small, medium or large quantities? How many of your videos got under a minute of watch time, one to two minutes, two to three? The tallest bars show where most of your results sit.

Microsoft describes a histogram simply as a column chart that shows frequency data. Excel has a built-in chart type for it, so you don't need to count anything yourself. The skill is in choosing the ranges, which Excel calls bins, and that's where I'll spend most of this guide.

Getting your data ready for an Excel histogram

You need one column of numbers, one value per row. Each row is one thing you measured: one order, one post, one customer.

• Keep the numbers in a single column with a heading at the top.
• Remove totals or averages from the bottom of the list, or they'll be counted as data.
• Make sure the numbers are stored as numbers, not text.

You don't need to work out the ranges first. The built-in chart creates them for you, and you can adjust them afterwards.

Inserting a histogram chart in Excel

Here are Microsoft's steps for Excel on Windows:

1. Select your column of numbers.
2. Go to the Insert tab.
3. Select Insert Statistic Chart, the icon that looks like a small bar chart.
4. Select Histogram.

Microsoft says you can also find it under Recommended Charts, on the All Charts tab. Click the chart and the Chart Design and Format tabs appear, so you can add a title and change colours. If those tabs aren't showing, click anywhere in the histogram.

Changing histogram bins in Excel

The default chart uses an Automatic setting, and Microsoft says it works out the bin width using something called Scott's normal reference rule. That's a sensible starting point, but the ranges often come out as awkward numbers. To set your own:

1. Right-click the horizontal axis along the bottom of the chart.
2. Select Format Axis.
3. Open Axis Options in the panel that appears.
4. Choose one of the bin settings below.

Bin setting	What it does
Automatic	Excel picks the bin width, the default
Bin width	You choose how wide each range is
Number of bins	You choose how many bars appear
Overflow bin	Groups everything above a value into one bar
Underflow bin	Groups everything at or below a value into one bar
By Category	Groups text categories instead of number ranges

Microsoft notes that Number of bins includes the overflow and underflow bins, so if you set it to 6 with both switched on, you'll get four ordinary ranges plus the two end bars.

Why overflow and underflow bins help

If one or two results are far bigger than the rest, the automatic ranges stretch out to fit them and squash everything else. An overflow bin gathers those big ones into a single bar at the end, so the rest of the chart has room to show its shape. Underflow does the same at the low end.

Counting text with a histogram in Excel

Histograms normally count numbers, but Microsoft offers a neat trick for text. To count how often each word appears, such as how many enquiries came from each platform:

1. Add a column next to your text and fill every row with the value 1.
2. Select both columns and insert the histogram.
3. In Format Axis, set the bins to By Category.

Each bar now shows how many times each category appears.

How Excel decides which bin a value goes into

Microsoft explains that a value lands in a bin if it's greater than the bin's lower edge and equal to or less than its upper edge. So if one bin runs from 5 up to 10 and the next starts at 10, a value of exactly 10 falls into the lower bar, not the higher one. That matters when lots of your results land on round numbers.

Histogram or column chart in Excel?

• Use a histogram when you have lots of individual numbers and want to see how they're spread out.
• Use an ordinary column chart when you already have totals for named things, like posts per platform.

If you're not sure which chart fits your data, my walkthrough on building charts and graphs in Excel compares the main options. And to put a number on how spread out the values are, the standard deviation guide pairs nicely with a histogram.

The older Histogram tool in Excel

Microsoft's help also describes a Histogram tool that uses two columns: your input data and a column of bin numbers you write yourself. It counts how many values fall in each bin and puts a table and chart on a new worksheet. If you leave out the bin numbers, Microsoft says Excel creates evenly spread bins between the smallest and largest values. For most people, the chart route above is quicker and updates when the data changes.

Histogram ideas for small business owners and creators

• Order quantities, to see whether most customers buy one item or several.
• Response times to enquiries, to spot how many take longer than a day.
• Video watch times, grouped into ranges.
• Ages or locations from a survey, using the By Category trick for text answers.

A histogram is a brilliant reality check against an average. If most bars bunch up at one end with a few strays at the other, the average is being dragged by those strays.

If your Excel histogram looks odd

• One giant bar: the bins are too wide. Set a smaller bin width or more bins.
• Lots of tiny bars: the bins are too narrow. Increase the bin width.
• Squashed chart: a few extreme values. Switch on an overflow bin.
• Histogram missing from the menu: menus vary by version, so try Recommended Charts, then the All Charts tab.

Frequently asked questions

How do I make a histogram in Excel?

Select your column of numbers, go to Insert, Insert Statistic Chart and choose Histogram. Microsoft says you can also find it under Recommended Charts on the All Charts tab.

How do I change the bins on an Excel histogram?

Right-click the horizontal axis, select Format Axis, open Axis Options, then set a Bin width or a Number of bins.

What are overflow and underflow bins?

Overflow groups every value above a set number into one bar, and underflow groups every value at or below a set number. They stop extreme values squashing the chart.

Can an Excel histogram count text?

Yes. Microsoft suggests adding a column filled with 1, plotting both columns as a histogram and setting the bins to By Category.

Which bin does a value on the boundary go into?

Microsoft says a value is included if it's greater than the lower bound and equal to or less than the upper bound, so it falls into the lower range.