Excel Histogram Template: Build One and Set Bins

Charts & pivot tables · Updated November 19, 2026

An Excel histogram template is a worksheet or chart setup that groups numeric data into ranges (bins) and shows how many values fall in each range. In Excel for Microsoft 365 on Windows, select your numeric values, then choose Insert > Insert Statistic Chart > Histogram; right-click the horizontal axis and choose Format Axis to set the bin width or number of bins.

What a histogram template does

A histogram summarizes the distribution of a numeric variable. Each bar represents a range of values, and the bar’s height represents the number of observations in that range. It can quickly show whether values cluster around a typical level, spread widely, bunch near a limit, or form more than one peak.

For example, a shop might group delivery times into ranges of 0–2 days, more than 2–4 days, and more than 4–6 days. The resulting chart makes it easier to see whether most orders arrive quickly or whether delays are common. A reusable template keeps the source-data area and chart settings ready so you can replace or extend the observations later.

A histogram is not the same as a bar chart of categories. A bar chart might compare sales by product name, where the categories can be arranged in any order. Histogram bins represent ordered numeric intervals, so their sequence matters. The bars usually touch because the intervals describe a continuous scale, although the chart’s gap settings can affect their appearance.

Make a histogram in Excel

For a quick chart, put one measured value in each cell of a single column. Include a clear heading, such as “Delivery days,” and keep unrelated notes or totals outside the selected range. Then create the chart:

  1. Select the heading and numeric observations, or select just the observations if you do not want Excel to use a series name.
  2. On the ribbon, choose Insert > Insert Statistic Chart > Histogram.
  3. Excel inserts a chart and chooses initial bins automatically. Move or resize the chart as needed.
  4. Right-click the horizontal axis and choose Format Axis. In Axis Options, set a Bin width or a Number of bins, depending on how you want to control the grouping.
  5. Add a descriptive chart title and, if useful, axis titles from the chart-design controls.

The automatic grouping is a starting point, not a universal statistical rule. Review the ranges and counts before sharing the chart. If the data has a meaningful measurement unit, use bins that make sense in that unit—for example, one week for waiting time or five dollars for transaction amounts.

On Excel for Mac, use the Insert tab’s statistical chart options to insert a histogram; exact ribbon labels and pane layout can vary by release. The chart’s axis formatting controls provide the bin settings. Excel for the web has a different, more limited interface than desktop Excel, so use the desktop app if the needed histogram or axis controls are not available in your browser.

Choose bins that tell a useful story

A bin is an interval that assigns each observation to a group. Excel’s chart bins are based on upper limits: values are grouped up to a bin boundary, with later bins covering values above the prior boundary. If a dataset contains 4, 7, 8, 12, and 18, bins ending at 10 and 20 would place the first three values in the first bin and the last two in the second.

Too few bins can hide important structure. If every value is compressed into only a couple of bars, a cluster or a long tail may disappear. Too many bins can make a modest dataset look noisy, with many empty or nearly empty bars. Try a few reasonable settings and choose one that reveals the pattern without implying more precision than the measurements support.

  • Use a meaningful scale: For ages recorded in whole years, boundaries at convenient age intervals are easier to explain than arbitrary decimal widths.
  • Consider sample size: A small dataset may not support many narrow bins; a large dataset can often show finer detail.
  • Check extreme values: An outlier can stretch the horizontal scale and make the main cluster hard to read.
  • Keep comparisons consistent: When comparing two groups, use the same bin boundaries so differences are not caused by different grouping choices.

Excel also offers overflow and underflow bins in the Format Axis pane. An overflow bin collects values greater than a chosen threshold, and an underflow bin collects values less than or equal to a chosen threshold. These options can keep a few extreme observations from creating a long string of sparsely populated bins, but they also conceal detail about where those observations lie. Label the chart or explain the grouping if you use them.

Build a reusable worksheet template

For a simple reusable template, keep the raw observations in one clearly labeled column and the histogram chart beside them. Use a separate instruction cell to tell the next user what belongs in the data column, the units to enter, and whether blanks are acceptable. Avoid mixing text labels into the numeric observations, because the chart is intended to summarize numbers.

To make the data area easier to extend, select the heading and sample rows and press Ctrl+T on Windows to create an Excel Table. Confirm that the table has headers. A table makes the input region easier to identify and can expand when users add rows. Check the chart’s source range after adding data, especially if you changed the original selection or pasted a large block; do not assume every chart setup will include every newly added row in every circumstance.

You can save the finished workbook as an Excel template file (.xltx) using File > Save As and choosing the Excel Template file type. This creates a starting point for new workbooks while leaving the template itself available for reuse. If the workbook includes macros, it requires a macro-enabled format such as .xltm; a basic histogram template does not need a macro.

A good template should make its assumptions visible. Include the data’s measurement unit, the date or group represented, and the chosen bin width or boundary approach. If the chart is intended for repeated monthly comparisons, keep the bin settings stable unless there is a clear reason to change them.

Use a frequency table when you need exact counts

The built-in histogram chart is convenient, but a frequency table is useful when you need to inspect counts, label each interval precisely, or use the grouped results elsewhere. In Microsoft 365 for Windows, you can create a table of upper boundaries and count observations with COUNTIF and COUNTIFS, then chart that table as a column chart.

For a concrete example, suppose the observations are in D2:D11 and are 4, 7, 8, 12, 14, 15, 18, 21, 24, and 29. Put the upper bin limits 10, 20, and 30 in A2:A4. With those ranges and upper limits, enter =COUNTIF($D$2:$D$11,"<="&A2) in B2; it returns 3, the number of values at or below 10. In B3, enter =COUNTIFS($D$2:$D$11,">"&A2,$D$2:$D$11,"<="&A3); it returns 4, counting values greater than 10 and at or below 20. In B4, enter =COUNTIFS($D$2:$D$11,">"&A3,$D$2:$D$11,"<="&A4); it returns 3. The counts total 10, matching the ten observations.

These formulas define bins as (previous limit, current limit], except for the first bin, which includes every value up to its first limit. This convention avoids counting a boundary value twice. If your data can be negative, add a suitable first lower boundary or define the first group explicitly rather than assuming all observations below the first upper limit belong there.

To chart the frequency table, select the interval labels and counts, then insert a column chart. Because this is now a regular chart based on categories, check the horizontal labels and format the bars so the display reads as a histogram. The table is often more transparent than a chart alone, especially when a reader needs to verify a particular count.

Use the Analysis ToolPak for a frequency output

Excel’s Analysis ToolPak provides a separate Histogram tool that creates a frequency output from input data and optional bin limits. It is useful when you want a table of counts as well as a chart. If Data Analysis is not visible on the Data tab in Windows, enable the add-in through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins and choose Go, then select Analysis ToolPak and choose OK.

To run it, arrange the observations in one column and enter ascending bin limits in another. Choose Data > Data Analysis > Histogram, supply the input range and bin range, and select an output location. Select Labels if the selected input range includes headings. Choose Chart Output if you want Excel to create a chart along with the frequency results. The output bins use the supplied limits as upper boundaries, and the tool also reports values above the final limit in a “More” group.

The ToolPak output is a calculated result, not the same thing as the built-in histogram chart’s automatic binning. If you change the source observations, rerun the analysis to refresh the output. For a chart that remains linked to a data range, the built-in chart or a formula-based frequency table may be more convenient.

Common problems and ways to fix them

  • The chart option is unavailable or the chart looks wrong: Confirm that the selected range contains numeric observations. A column of category names is suited to a bar or column chart, not a histogram.
  • Counts seem too low: Check for numbers stored as text, accidental spaces, or a selection that stops before the last row. Also inspect the final bin: values above the last upper limit may be grouped separately or omitted from a manually defined table.
  • There are too many empty bars: Increase the bin width or reduce the number of bins. Then check that the chart still conveys useful variation.
  • Two charts look incomparable: Set the same bin width and boundaries for both datasets. Automatic bins can choose different groupings, which can make a visual comparison misleading.
  • The axis starts or ends at an unexpected point: Review the bin settings and any underflow or overflow threshold in Format Axis. Also check for genuine extreme values before excluding them.

Do not remove an unusual value just because it makes the chart less tidy. First determine whether it is a data-entry error or a real observation. If it is real, decide whether to show it directly, use an overflow bin, or provide a second view—and state the choice so readers are not left with a distorted impression.

Histogram chart, ToolPak, or manual table?

Choose the built-in histogram chart when you want a quick visual summary and expect to adjust the bin width interactively. Use the ToolPak when you specifically need its frequency output and can rerun it after the source data changes. Use a formula-based table when exact interval labels and auditable counts matter, or when the grouped counts must feed another worksheet calculation.

None of these approaches decides what the data means. A histogram shows frequency, not cause, quality, or statistical significance. For instance, a chart of customer wait times may reveal a long tail, but it does not explain why delays happen. Check the source, population, time period, units, and grouping choices before drawing conclusions.

Frequently Asked Questions

Does Excel have a histogram template?
Excel includes a built-in histogram chart, and you can save a prepared workbook as an .xltx template. The chart is created from your numeric data; Excel does not require you to download a separate template.

How do I change the bin size in an Excel histogram?
Right-click the horizontal axis, choose Format Axis, and set Bin width under Axis Options. You can instead specify the Number of bins if you want Excel to divide the range into a chosen count of groups.

Can I make a histogram without the Analysis ToolPak?
Yes. Select numeric data and use Insert > Insert Statistic Chart > Histogram. The ToolPak is an alternative for producing a frequency table and optional chart.

Why does my histogram have a “More” bin?
In a ToolPak output, “More” counts observations above the largest supplied bin limit. Increase the final limit or retain the group and explain what it contains.

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

Related

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.