Histogram in Excel Without ToolPak: A Step-by-Step Guide

Yes. You can make a histogram in Excel for Microsoft 365 without the Analysis ToolPak: select your numeric data and use Insert > Insert Statistic Chart > Histogram, or build a frequency table with formulas and chart it as columns. The built-in chart is the quickest option; the formula method gives you more control over bin boundaries and labels.

What a histogram shows

A histogram groups numeric observations into ranges called bins and shows how many values fall into each range. The bars touch because the ranges represent continuous intervals: a chart of ages might show counts for 0–9, 10–19, and 20–29, for example. This makes a histogram useful for seeing whether values cluster, spread out, or have a long tail.

It is different from a standard column chart, even though a column chart can display a histogram-style frequency table. A column chart often compares separate categories, such as sales by product. A histogram groups measured numbers into intervals. Excel’s dedicated Histogram chart works directly from raw data, while the manual method first counts observations in bins you define.

The Analysis ToolPak is an optional Excel add-in that includes statistical analysis tools, including a histogram tool. It is not required for either method here. You can create a suitable chart with Excel’s built-in chart feature, or use ordinary worksheet formulas to calculate the counts yourself.

Use Excel’s built-in Histogram chart

For a quick chart in desktop Excel for Microsoft 365 on Windows, put the observations in one column, with a descriptive heading at the top, and select the numeric cells. Then choose Insert > Insert Statistic Chart > Histogram. Excel creates bins automatically; you can change their width or number to suit the data.

Place the values in a single column, such as A2:A101. Keep the heading in A1 and avoid including it in the selection.
Select the cells containing the numbers, then choose Insert > Insert Statistic Chart > Histogram.
To change the intervals, right-click the horizontal axis and choose Format Axis. Under Axis Options, adjust the bin width or the number of bins. Depending on the chart and Excel build, you can also set an overflow or underflow bin.
Add a useful chart title and axis titles. For example, label the horizontal axis “Delivery time (days)” and the vertical axis “Number of orders.”

Automatic bins are convenient, but they are not necessarily the best bins for your question. If Excel picks many narrow intervals, the chart may look noisy; very wide intervals can hide meaningful variation. The axis settings let you revise the grouping after the chart is created.

On Excel for Mac, the Histogram chart is also available in current desktop versions, though the ribbon layout can differ; look under the Insert tab’s statistical chart options. Excel for the web has a different interface and may not expose the same chart-formatting controls in every build. If you cannot find Histogram or the bin options in the web app, use desktop Excel or create the frequency table below.

Build bins and count them with formulas

A manual frequency table is a reliable option when you want fixed, readable intervals or when the dedicated chart is unavailable. In this example, enter the 20 observations below in A2:A21: 12, 18, 22, 27, 31, 34, 38, 41, 45, 53, 59, 62, 68, 73, 77, 84, 91, 96, 104, and 117. Put upper bin limits 20, 40, 60, 80, 100, and 120 in C2:C7. The bins will be values up to 20, then greater than 20 through 40, and so on.

With the data in A2:A21 and the first upper limit in C2, enter this in D2 to count values up to and including 20:

=COUNTIF($A$2:$A$21,"<="&C2)

The result is 2, because 12 and 18 are at or below 20. For the next bin, with the data still in A2:A21, the previous upper limit in C2, and the current upper limit in C3, enter this in D3:

=COUNTIFS($A$2:$A$21,">"&C2,$A$2:$A$21,"<="&C3)

The result is 5: 22, 27, 31, 34, and 38 are greater than 20 and no greater than 40. Copy D3 down through D7. The resulting counts are 2, 5, 4, 4, 3, and 2, which total 20 and account for all the listed observations. Make sure the limits are in ascending order and that your formulas use the correct data range; otherwise, values may be missed or counted in the wrong interval.

For readable labels, enter labels such as “0–20,” “>20–40,” and “>40–60” in a separate column. Use Insert > Insert Column or Bar Chart > Clustered Column to chart the labels and counts. A regular column chart will leave gaps between bars by default. Select a bar, open Format Data Series, and reduce Gap Width to 0% or a small value to give the chart the familiar adjoining-bin appearance. This method is especially useful when your desired labels include units or clearly state whether an endpoint is included.

Use FREQUENCY as an alternative

Excel’s FREQUENCY function can calculate all bin counts at once. It takes a data range and a range of bin limits, then returns the number of values at or below each limit, plus one extra count for values above the highest limit. In Microsoft 365, the results spill into neighboring cells automatically.

With the 20 observations in A2:A21 and upper bin limits in C2:C7, enter this formula in D2:

=FREQUENCY(A2:A21,C2:C7)

It returns seven results: 2, 5, 4, 4, 3, 2, and 0. The first six are the counts through the six limits; the final zero is the count above 120. Leave enough empty cells below D2 for the results to spill, and do not type into the spill range. If the formula returns a spill error, check for existing values in those cells.

In older Excel versions without dynamic arrays, FREQUENCY may need to be entered as a legacy array formula: select the output range first and confirm with Ctrl+Shift+Enter. For current Microsoft 365 on Windows, enter it normally in one cell. The COUNTIF and COUNTIFS approach is easier to audit row by row and works well if you want to add labels or other calculations alongside each bin.

Choose useful bin boundaries

Bins affect the story a histogram tells. If a dataset covers 0 to 120 and the question concerns broad performance bands, intervals of 20 may be easy to interpret. If you are inspecting small changes around a target, narrower intervals might show more detail. There is no single bin width that is best for every dataset; choose a scale that makes sense for the measurement and the decision you need to support.

Use consistent widths. Equal-width bins make the bar heights easier to compare. Unequal widths can be valid, but the visual meaning of bar height becomes more complicated.
Keep endpoints clear. The example counts values greater than the previous limit and less than or equal to the current limit. State this convention in labels or notes when a boundary value matters.
Check for outliers. A few extreme values can stretch the axis and make the main cluster appear compressed. An overflow bin can keep those values visible without creating many nearly empty intervals.
Consider the sample size. With very few observations, a detailed histogram can suggest patterns that the data cannot support. Try a simpler grouping and show the number of observations.

For dates or measurements with meaningful thresholds, use boundaries tied to those units rather than choosing numbers just to make the chart look balanced. For example, monthly ranges may make more sense for elapsed time than arbitrary intervals of 37 days.

Format and check the finished chart

A useful histogram should identify what is being measured and what each count represents. Give the chart a descriptive title, include units in the horizontal-axis label, and use “Frequency” or “Number of observations” on the vertical axis. If the values represent a sample rather than every member of a population, make that context clear in a note or chart title.

Before sharing the chart, compare the sum of the bin counts with the number of valid numeric observations. In the example, the counts add to 20, matching the 20 data values. If the totals do not match, investigate blank cells, text stored as numbers, values outside the selected range, and boundaries that leave a gap. Also check whether the chosen bin limits include the smallest and largest values.

Excel’s built-in Histogram chart and a manually charted frequency table can look similar, but they behave differently when the source data changes. A chart based on raw data recalculates its bins according to its settings. A manual table uses the limits you set, so its grouping stays consistent as new observations are added, provided the formulas and chart ranges include those observations.

Common problems and fixes

Histogram is missing from the Insert menu: Confirm that you are using a desktop Excel version that includes statistical charts and that you selected numeric data. If the web interface does not show the same command, try desktop Excel or use the formula method.
The chart has unexpected bins: Right-click the horizontal axis, choose Format Axis, and review the bin width or number of bins. Automatic choices may change as the data changes.
The counts do not add up: Check the source range, blank or nonnumeric entries, and whether the highest bin covers the maximum value. With FREQUENCY, remember its extra overflow result.
Boundary values appear to be missing or duplicated: Use a consistent rule. The formulas above count each bin as greater than the previous upper limit and less than or equal to the current one, so a value equal to 40 belongs in the bin ending at 40, not the next bin.
The manual chart looks like a bar chart with gaps: It is a clustered column chart built from counts, not Excel’s dedicated Histogram chart. Set the series gap width to zero or a small value and ensure the bin labels are in the intended order.

Frequently Asked Questions

Can I make a histogram in Excel without the Analysis ToolPak?
Yes. Use Insert > Insert Statistic Chart > Histogram in desktop Excel, or calculate bin counts with COUNTIF, COUNTIFS, or FREQUENCY and chart the resulting table. The ToolPak is optional for these methods.

What is the easiest way to make a histogram in Excel?
Select the numeric data and choose Insert > Insert Statistic Chart > Histogram. Excel creates an initial chart with automatic bins; use Format Axis to adjust the bin settings if needed.

Can I create a histogram with formulas only?
You can calculate the frequencies with formulas, but a visual chart still requires inserting a chart. COUNTIFS is useful for one formula per interval, while FREQUENCY returns the full set of counts from one formula in Microsoft 365.

Why does FREQUENCY return one more number than my bin list?
Its final result counts values greater than the largest bin limit. That overflow count is part of the function’s design, so include it in your chart only if you want to display an “Above” category.

Is a column chart the same as a histogram?
Not exactly. A dedicated histogram groups raw numeric data into bins. A column chart usually compares categories, but it can display a manually calculated frequency table with adjoining bars to create a histogram-style view.

Which method should you use?

Choose Excel’s built-in Histogram chart when you want a fast view of raw data and are comfortable adjusting Excel’s bin settings. Choose a formula-based frequency table when fixed limits, explicit endpoint rules, custom labels, or a repeatable layout matter more than speed. Neither method requires enabling the Analysis ToolPak, and both can be checked against the number of numeric observations to catch missing or misclassified values.

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.