How to make a histogram in Excel

Published 2026-11-11 · 5 min read

A histogram shows how your values are spread out: where most of them sit, how wide the range is and whether there are outliers. Excel can draw one in three ways. Here is when to use each, step by step, and how to pick bins that tell the truth about your data.

What you need before you start

A histogram needs one column of numbers: exam scores, order values, delivery times, ages. Each row is one observation. Remove totals and averages from the bottom of the column first, or they will be counted as data points.

Method	Excel version	Updates when data changes	Best for
Built-in histogram chart	Excel 2016 and later, Microsoft 365, Mac 2016+	Yes	Most people
Analysis ToolPak	Any desktop version	No, rerun it	Getting a frequency table too
FREQUENCY function + column chart	Any version	Yes	Exact control of bins, older Excel

Method 1: The built-in histogram chart

Select the column of numbers, including its header.
Go to Insert › Insert Statistic Chart (the icon with blue bars) › Histogram.
Excel draws the chart and picks the bins for you.

To change the bins, right-click the horizontal axis, choose Format Axis, and under Axis Options › Bins pick one of:

Bin width: every bar covers the same range, for example 10 points or $50.
Number of bins: Excel works out the width.
Overflow bin and Underflow bin: group everything above or below a value into one bar, so a few extreme values don't stretch the chart.

The labels read like [50, 60] and (60, 70]. A square bracket means the value is included, a round one means it is not, so a score of exactly 60 lands in the first bar.

Method 2: The Analysis ToolPak

The ToolPak gives you a frequency table and a chart in one go, in any desktop version of Excel.

Turn it on: File › Options › Add-ins, choose Excel Add-ins in the Manage box, click Go, tick Analysis ToolPak and click OK. On a Mac, use Tools › Excel Add-ins.
In a spare column, type the upper limit of each bin, for example 50, 60, 70, 80, 90, 100.
Go to Data › Data Analysis › Histogram.
Set Input Range to your numbers and Bin Range to the limits you typed. Tick Chart Output and click OK.

The result is a static copy: if your data changes, run it again. The chart comes out as a column chart with gaps between the bars; to close them, right-click a bar, choose Format Data Series and set Gap Width to 0%.

Method 3: The FREQUENCY function

Use this when you want the counts to update automatically and you need full control over the bins, or when your Excel has no histogram chart.

Type your bin upper limits in a column, for example in D2:D7.
In the next column, enter =FREQUENCY(A2:A121, D2:D7). In Microsoft 365 the results spill down on their own. In older versions, select E2:E8 first and press Ctrl+Shift+Enter.
FREQUENCY returns one more number than you have bins: the last one counts values above your highest limit.
Select the counts and insert a Clustered Column chart, then set Gap Width to 0%.

Give the bars readable labels such as 50–60 in the column next to the counts and use them as the axis labels.

How to choose the number of bins

The bins decide what your histogram says. Too few bars hide the shape; too many turn it into noise.

Start with a rule of thumb. Sturges’ rule suggests about log₂(n) + 1 bins: 8 bins for 120 values, 11 for 1,000. Excel’s automatic setting uses Scott’s rule, which also takes the spread of the data into account.
Then round the width to a number people understand. Bins of 10 points, 5 minutes or $25 are easier to read than 9.37.
Try two or three widths. If the shape changes completely between 8 and 12 bins, say so when you share the chart.
Keep the bins equal. Unequal widths make wide bars look more important than they are.

How to read what you made

One peak in the middle: values cluster around a typical value. Check the box plot to compare groups.
A long tail on one side: the average is pulled toward the tail, so the median describes a typical value better.
Two peaks: you probably have two groups mixed together, such as two classes or two product lines. Split the data and compare.
Isolated bars far from the rest: outliers, or typing mistakes worth checking.

Histogram or bar chart?

A histogram shows how one numeric measure is distributed, so its bars touch and their order is fixed. A bar chart compares separate categories, such as sales by region, so its bars have gaps and can be sorted. If your horizontal axis is made of names rather than ranges of numbers, you want a bar graph.

Common problems

There is no Histogram option. Your Excel is 2013 or older, or the file is open in compatibility mode. Use the ToolPak or FREQUENCY.
The chart has one huge bar. A few very large values are stretching the range. Set an overflow bin.
Numbers are ignored. They are stored as text (look for a green triangle in the cell corner). Select the column and use Data › Text to Columns › Finish to convert them.
Data Analysis is missing from the Data tab. The ToolPak isn't turned on; see step 1 of Method 2.

A faster way without Excel

If you only need to see the distribution and share the chart, you can skip the setup. Open the free histogram maker, drop your Excel or CSV file and pick the column. You get the histogram with sensible bins, a slider to change the number of bins, and summary statistics (mean, median, spread) underneath. The file is read in your browser and never uploaded.