How to Make a Histogram in Excel

A histogram in Excel shows how your numbers are spread out: how many fall between 50 and 60, between 60 and 70, and so on. Excel 2016 and later have a built-in Histogram chart that takes two clicks, and every version can build one from a COUNTIFS table. This guide shows both, plus FREQUENCY and PivotTable methods, so you can pick the one that fits your data and your Excel.

What is a histogram?#

A histogram is a chart that counts how many values fall into each range. Each range is called a bin, such as 60 to 69. The height of a bar is the number of values in that bin.

It looks like a column chart, but there are two differences:

• The horizontal axis shows number ranges, not categories like regions or products.
• The bars touch, because one range runs straight into the next.

Use a histogram to answer questions like "are most test scores high or low?", "how long do most deliveries take?" or "are there any unusual values?". For comparing separate categories, a bar chart is the better fit.

All the examples use these 15 test scores. In the sheet, the names are in A2:A16 and the scores in B2:B16; the table below is folded into three pairs of columns to save space:

How to make a histogram with the built-in chart#

In Excel 2016 and later, including Microsoft 365, the Histogram chart does the counting for you:

1. Select the scores, B1:B16 (include the header so it becomes the title).
2. Go to Insert > Insert Statistic Chart > Histogram. In Excel for Mac, click the statistic chart button on the Insert tab, then Histogram.
3. Excel draws the chart with bins it chooses automatically.

The automatic bins rarely have round edges, so set them yourself. Right-click the horizontal axis, choose Format Axis, and look under Bins:

For scores, set Bin width to 10 and Underflow bin to 59. The bars become ≤59, (59, 69], (69, 79], (79, 89] and (89, 99], with counts 2, 4, 5, 2, 2.

Read the labels carefully: a square bracket ] means the edge is included and a round bracket ( means it isn't. So (59, 69] is "above 59, up to and including 69", which for whole-number scores is 60 to 69. The chart updates when the scores change.

How to make a histogram with COUNTIFS#

A bin table with COUNTIFS works in every Excel version and shows exactly what each bar counts. Type the lower and upper edge of each bin, then count:

In F2, type this and fill down (Windows: Ctrl+D; Mac: ⌘D):

=COUNTIFS($B$2:$B$16,">="&D2,$B$2:$B$16,"<"&E2)

The first condition keeps scores at or above the lower edge, and the second keeps scores below the upper edge. Using >= at the bottom and < at the top means a score of exactly 70 is counted once, in the 70 bin, never twice. The $ signs keep the score range fixed as you fill down. The counts add up to 15, which is a quick check that no score fell through a gap. The COUNTIFS function guide explains each argument, and COUNTIFS and SUMIFS with multiple conditions has more examples.

Now chart it:

1. Add a label column, such as "50-59", or select F1:F6 on its own.
2. Go to Insert > Insert Column or Bar Chart > Clustered Column.
3. Right-click a bar, choose Format Data Series, and set Gap Width to 0% so the bars touch.

If you're new to charts, the basics of Excel charts covers titles, labels and axes.

How to use FREQUENCY for the counts#

FREQUENCY counts values into bins in one formula. You give it the data and the upper edge of each bin:

=FREQUENCY(B2:B16,H2:H6)

With 59, 69, 79, 89 and 100 in H2:H6, it returns 2, 4, 5, 2, 2, plus one extra value for anything above 100 (here 0). Each bin counts values above the previous edge, up to and including its own.

In Microsoft 365 and Excel 2021, the results spill down automatically. In older versions, select six cells first, type the formula, and press Ctrl+Shift+Enter to enter it as an array formula. The guide to making a frequency table covers FREQUENCY, percentages and running totals in detail.

How to make a histogram with a PivotTable#

A PivotTable can group numbers into bins, which is handy for long lists:

1. Select the data and go to Insert > PivotTable.
2. Drag Score to Rows and drag Score again to Values. Change the value to Count (right-click > Summarize Values By > Count).
3. Right-click any score in the Rows area and choose Group.
4. Set Starting at 50, Ending at 100 and By 10. Click OK.

The rows become 50-59, 60-69 and so on, with a count next to each. Add a PivotChart from the PivotTable Analyze tab and set the gap width to 0. Counting with a PivotTable explains Count vs Sum, and the PivotTable lesson lets you build one step by step.

Which method should you use?#

Each method suits a different situation:

The Analysis ToolPak's Histogram tool (Data > Data Analysis, once the add-in is turned on) writes a fixed table that doesn't change when your data does, so the other methods are usually better.

Common histogram mistakes#

Most wrong histograms come from the bins, not the chart:

• Overlapping bins. Writing "<=70" for one bin and ">=70" for the next counts 70 twice. Use >= and <.
• Gaps between bins. Bins like 50-59 and 60-69 miss 59.5 if your data has decimals. Use edges like 50 and 60 with <.
• Too many or too few bins. Five to ten bins of equal width is a good start. Round widths (5, 10, 100) are easiest to read.
• Numbers stored as text. COUNTIFS and the Histogram chart skip them, so totals come up short. Check that the counts add up to the number of values.
• A gap between bars. A column chart has gaps by default. Set Gap Width to 0%.

Practise it#

Try the exercise on this page: count the scores in one bin with COUNTIFS, exactly as each bar of a histogram does. Then practise COUNTIFS and SUMIFS in a live spreadsheet and build your first PivotTable. You do each step in a real sheet and get instant feedback.