A histogram shows how often values fall into ranges, not individual data points

A histogram is a chart that groups numbers into buckets and shows how many values land in each bucket. If you have a list of test scores, a histogram might show you how many students scored 60–70, how many scored 70–80, and so on. Excel can build this for you in a few steps, though the process depends on which version you have and whether you want to let Excel decide the bucket sizes or set them yourself.

The fastest route in Excel 2016 and later is the built-in histogram chart type. In older versions, you build one using a helper column and a bar chart. Both methods start with your raw data in a single column.

Key Takeaways

  • Excel 2016 and later have a histogram chart type you can insert directly; older versions require you to create one manually using a helper column.
  • Your data must be in a single column, and each cell should hold one number — no headers or text mixed in with the values.
  • Excel automatically chooses bucket sizes, but you can change them by right-clicking the horizontal axis and editing the bin width or number of bins.
  • A histogram works best when you have at least 20 to 30 data points; smaller datasets often look misleading because the buckets are too sparse.

Setting up your data column

Open your spreadsheet and put all the numbers you want to chart in a single column. For example, if you have test scores, put them in column A starting at A1. Do not include a header row — Excel's histogram tool works better when you select only the numbers themselves.

If your data has a header (like "Test Scores" in A1), select only the cells below it. Click on A2, then hold Shift and click on the last cell with data. This selection tells Excel which numbers to use.

Creating a histogram in Excel 2016 or later

Select your data column. Go to the Insert tab at the top. Look for the Charts section and click the small arrow next to the chart icons to see all chart types. Scroll down until you see "Histogram" and click it. Excel will create a chart and place it on your sheet.

The chart appears with automatic bucket sizes. If the buckets are too wide or too narrow, you can adjust them. Right-click on the horizontal axis (the numbers along the bottom) and select "Format Axis". A panel opens on the right. Under "Bins", you can change the bin width (the size of each bucket) or the number of bins (how many buckets total). Experiment until the chart shows what you need to see.

Building a histogram manually in Excel 2013 and earlier

If you have an older version, you need a helper column. First, decide what bucket sizes you want. If your data ranges from 40 to 100, you might use buckets of 10: 40–50, 50–60, 60–70, and so on. Write the upper limit of each bucket in a new column — for example, 50, 60, 70, 80, 90, 100 in column B.

In column C, use the COUNTIFS function to count how many values from your data column fall into each bucket. For the first bucket (40–50), the formula in C1 would be =COUNTIFS($A$2:$A$100,"<="&B1,$A$2:$A$100,">"&40). For the second bucket, change the last number to 50, and so on. Copy the formula down for each bucket.

Once your counts are ready, select the bucket labels (column B) and the counts (column C), then insert a bar chart. This creates the same visual as a built-in histogram.

Adjusting bucket size to show the right level of detail

The number of buckets changes how the histogram looks. Too few buckets and you lose detail — everything gets lumped together. Too many buckets and the chart becomes noisy, with some buckets empty and others holding just one or two values.

A common starting point is the square root rule: take the square root of how many data points you have. If you have 100 scores, the square root is 10, so try 10 buckets. If that looks too sparse or too crowded, adjust up or down by one or two buckets and regenerate the chart. There is no single right answer — it depends on what story your data tells.

When a histogram is the wrong choice

A histogram only works for continuous numbers — measurements like height, temperature, or time. Do not use it for categories (like "red," "blue," "green") or for counting how many times each exact value appears. For categories, use a bar chart instead. For exact value counts, use a column chart.

Also, histograms need enough data to be meaningful. If you have fewer than 15 or 20 data points, the buckets will be too sparse and the chart will not show a real pattern. In that case, list the individual values in a table instead.

Labeling and formatting your histogram

Click the chart to select it. At the top, a "Chart Design" tab appears. Click it and then "Add Chart Element" to add a title, axis labels, and a legend if needed. Give the horizontal axis a label like "Score Range" and the vertical axis a label like "Number of Students".

Right-click the bars themselves to change their color or remove the gap between them — histograms often look better with bars touching, since the buckets are continuous ranges rather than separate categories.

Frequently Asked Questions

Can I create a histogram from data in multiple columns?

No. A histogram needs one column of numbers. If your data is spread across multiple columns, copy it into a single column first, then build the chart.

What if my data includes negative numbers?

Histograms work fine with negative numbers. Excel will set the bucket range to fit your lowest and highest values automatically. If you are building one manually, just make sure your bucket limits cover the full range of your data.

How do I change the bucket size after I create the chart?

Right-click the horizontal axis and select "Format Axis". In the panel on the right, find the "Bins" section and change either the bin width (size of each bucket) or the number of bins (how many buckets total). The chart updates when ready.

Can I add a trend line or average line to my histogram?

Yes, but it is not the default option. Right-click the bars, select "Add Trendline", and choose the type you want. For an average line, use a linear trendline with order set to 0.

Why does my histogram look empty or have huge gaps?

Your bucket size is probably too small, creating many empty buckets. Right-click the horizontal axis, select "Format Axis", and increase the bin width or decrease the number of bins until the chart fills in.