Excel Histogram Tutorial: Create and Customize Charts

To make a histogram in Microsoft Excel for Microsoft 365 on Windows, select the numeric data, then choose Insert > Insert Statistic Chart > Histogram. Excel creates bins and counts how many values fall into each one; use the horizontal axis’s Format Axis pane to change the bin width, number of bins, or end bins so the chart answers your question clearly.

What a histogram shows

A histogram summarizes the distribution of numerical data. It groups values into intervals called bins and uses touching columns to show how many observations fall in each interval. The overall shape can reveal a cluster, a long tail, gaps, or more than one peak. For example, a histogram of delivery times might show whether most orders arrive in a narrow range or whether a smaller group takes much longer.

A histogram is not the same as a bar chart. A bar chart compares separate categories, such as sales by product, and usually leaves space between category bars. A histogram groups measurements along a continuous number scale, so its columns touch. If your entries are names such as “North,” “South,” and “West,” use a bar or column chart rather than a histogram.

Prepare the data before charting

Put the observations in one column, with an optional descriptive header such as “Delivery time (days).” Each row should contain one numeric measurement. Remove titles or notes embedded in the data, check that values stored as text are converted to numbers, and decide how to handle blanks, errors, and implausible values before interpreting the result. A chart cannot distinguish a genuine extreme observation from a typing mistake.

For example, a column containing 2, 4, 5, 7, 8, 9, 11, 12, 14, and 18 is ready to chart. If the column has a header, selecting it with the values is usually fine: Excel can recognize the label. If it does not, select only the numeric cells. Avoid selecting unrelated columns, since Excel may interpret the selection as multiple data series instead of one distribution.

Create a histogram chart in Excel

Select the numeric observations, including the header only if you want Excel to use it as a series label.
On the ribbon, choose Insert > Insert Statistic Chart > Histogram. If the ribbon is condensed, look for the statistical chart options in the Charts group under Insert.
Excel inserts a histogram with automatically chosen bins. Add a descriptive chart title, and add axis titles if readers need to know the measurement and units.
Click the horizontal axis, right-click it, and choose Format Axis. In the Format Axis pane, expand Axis Options to adjust how values are grouped.

To edit the title, click it and type a replacement. To add axis titles, select the chart and use Chart Design > Add Chart Element > Axis Titles, then choose the horizontal or vertical title. The precise ribbon arrangement can vary with window size, but the chart elements are available from the Chart Design tab in desktop Excel.

Choose useful bins

Excel’s automatic bin choice is a starting point, not a guarantee of the best view. In Format Axis, Excel’s histogram options include Automatic, Bin width, Number of bins, Overflow bin, and Underflow bin. Choose Bin width when an interval has a meaningful interpretation, such as five minutes or ten dollars. Choose Number of bins when you want to control the chart’s overall level of detail without specifying the interval size directly.

Too few bins: meaningful differences and multiple clusters can disappear into broad columns.
Too many bins: random variation can look like a pattern, and many columns may have very small counts.
A sensible width: use a width that is easy to explain and suitable for the measurement. For whole-number test scores, one-point or five-point intervals may be sensible; for a large set of wait times, a wider interval may make the distribution easier to read.

Consider the scale as well as the shape. A bin width of 5 has a different meaning for ages measured in years than for distances measured in kilometres. There is no universally correct bin width: the useful choice depends on sample size, measurement precision, and the question. Try a couple of reasonable settings, then check whether the main pattern remains. If a conclusion appears only under one unusually narrow setting, describe that limitation rather than presenting the shape as unquestionable.

Set underflow and overflow bins

An underflow bin collects values at or below a cutoff, while an overflow bin collects values at or above a cutoff. These options can keep extreme observations from stretching the axis and squeezing the bulk of the data into a few narrow-looking columns. In Format Axis, enter a cutoff for the relevant bin option. Use these bins only when their meaning is clear: they combine values that may be far apart, so the chart no longer shows their individual spread at the ends.

For example, a chart of response times might use an overflow bin for times of 60 minutes or more if the practical question is how many responses exceeded an hour. State that cutoff in the chart title, axis label, or a nearby note. Do not silently group extremes if readers might assume the final column represents a narrow interval.

Build a histogram with the Analysis ToolPak

The Analysis ToolPak offers another histogram workflow, particularly useful when you want a frequency table alongside the chart. In desktop Excel for Windows, enable it through File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box, select Go, check Analysis ToolPak, and select OK. Then choose Data > Data Analysis > Histogram.

In the dialog, specify the Input Range and, if you have created one, the Bin Range. A bin range contains upper-boundary values, not pairs of lower and upper limits. For example, boundaries 5, 10, 15, and 20 define successive upper limits; the tool reports counts up to each boundary and places values above the last boundary in a final category. Select Labels if the input or bin range includes a heading. Choose an output location and select Chart Output if you also want a chart.

On a Mac, the Analysis ToolPak is also an Excel add-in, but its setup is under Tools > Excel Add-ins; select Analysis ToolPak and confirm. The Data Analysis command then appears on the Data tab when the add-in is available. Excel for the web does not provide the same desktop add-in workflow, so use the chart method in a supported desktop version if the ToolPak command is unavailable.

Make a frequency table with formulas

A formula-based table gives you more control over interval boundaries and makes the counts easy to reuse in reports. Suppose the ten example observations are in A2:A11, lower bin limits are in D2:D5, upper limits are in E2:E5, and you want the frequencies in F2:F5. With that layout, enter this formula in F2, then fill it down through F4:

=COUNTIFS($A$2:$A$11,">="&D2,$A$2:$A$11,"<"&E2)

For the intervals 0–5, 5–10, and 10–15, the formula counts each lower boundary inclusively and each upper boundary exclusively. The resulting counts are 2, 4, and 3: values 2 and 4 are in the first interval; 5, 7, 8, and 9 are in the second; and 11, 12, and 14 are in the third. To make the last interval include its upper limit, use this formula in F5 for the 15–20 interval:

=COUNTIFS($A$2:$A$11,">="&D5,$A$2:$A$11,"<="&E5)

With 15 and 20 as the final limits, that count is 1, because 18 is the only example value in the interval. Select the bin labels and frequency counts, then insert a clustered column chart. Reduce the gap width in Format Data Series if you want the columns to touch and resemble a histogram. Keep the interval convention consistent: mixing inclusive upper bounds with inclusive lower bounds can count a boundary value twice.

Improve readability and avoid common errors

A useful histogram makes the data and its scale understandable without a lengthy explanation. Label the unit, use a clear title, and avoid decorative effects that make column heights harder to compare. The vertical axis normally represents frequency, or the number of observations in each bin. If readers need percentages rather than counts, calculate relative frequencies in a separate table and make that choice explicit.

Unexpected bars: check that the selected range contains only the intended observations and that numbers are numeric rather than text.
A missing header or strange label: select the range again and include the header only when it is a real column label; otherwise, select data cells alone.
One very tall or very short column: inspect the bin width and the distribution’s extreme values before changing the scale. Do not remove a valid value just because it affects the chart.
Columns do not touch: confirm that the chart is a histogram rather than a regular column chart. For a formula-built chart, adjust the series gap width.
Different results after adding data: confirm that the new observations are inside the chart’s source range. A chart based on a fixed selection may not include rows added later.

For a reusable workbook, consider storing observations in an Excel table. Tables expand when rows are added, making them a convenient source for charts and summaries. Still check the chart after adding data, because chart source behavior can depend on how the chart was created and edited.

When to use a different chart

Use a histogram to examine the shape of one numeric variable. Use a bar chart to compare categories, a line chart to show values over time, or a box-and-whisker chart to compare medians and spread across groups. If you need to compare several distributions, separate histograms can be useful, but keep the bin boundaries consistent; otherwise, apparent differences may come from the binning rather than the data.

A histogram also does not show individual observations. If the sample is small, a dot plot or a table may communicate the exact values more honestly. For large datasets, the histogram gives a compact overview, but it should not replace checks for data quality or a closer look at unusual observations.

Frequently Asked Questions

Does Excel make histogram bins automatically?
Yes. The built-in Histogram chart starts with an automatic bin setting. You can change the number of bins or the bin width in the Format Axis pane if the automatic grouping is not useful.

Why can’t I find Histogram under Insert?
Check that you are using a desktop Excel version with the statistical chart option and that you selected numeric data. In Microsoft 365 for Windows, look under Insert > Insert Statistic Chart. In Excel for the web, chart availability and controls can differ; use desktop Excel if the command or formatting options are missing.

Can I use text categories in a histogram?
No. A histogram groups numeric measurements into intervals. For text categories such as departments or product names, use a bar or column chart.

What is the difference between bin width and number of bins?
Bin width sets the size of each interval, such as five units. Number of bins sets how many intervals Excel uses across the data range. Changing either can alter the chart’s appearance, so choose based on a clear scale or a reasonable level of detail.

Can I make a histogram without the Analysis ToolPak?
Yes. The built-in Histogram chart does not require that add-in. You can also create a frequency table with formulas and chart its counts when you need explicit control over interval boundaries.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.