Histogram in Excel Using Formulas: Build Bins and Charts

To make a histogram in Excel with formulas, list your data, set bin limits, and use FREQUENCY to count values in each bin; then chart the counts with a column chart. In Excel for Microsoft 365 on Windows, enter the formula once and let its results spill into the cells below.

What a formula-based histogram shows

A histogram summarizes the distribution of numeric data. Instead of showing each individual value, it groups values into ranges called bins and displays how many observations fall in each range. For example, a histogram of delivery times might show how many deliveries took under 10 minutes, from 10 to under 20 minutes, and so on.

The formula does the counting; the chart makes the pattern easier to see. A concentration of values in a few neighboring bins suggests a different distribution from values spread evenly across many bins. Histograms are useful for measurements such as test scores, wait times, product weights, and monthly transaction amounts. They are generally not the right chart for categories such as department names or product types, because those categories do not form numeric intervals.

Build bins with FREQUENCY

FREQUENCY is Excel’s built-in function for counting values against bin limits. It treats each listed bin value as an upper boundary: the first result counts numbers less than or equal to the first limit, and each later result counts numbers greater than the previous limit and less than or equal to the current one. It returns one more count than the number of bin limits, with the last result counting values above the highest limit.

For a worked example, put the values 4, 7, 9, 12, 15, 18, 21, 24, 27, and 30 in A2:A11. Enter 10, 20, and 30 in B2:B4 as the upper bin limits. With the data in A2:A11 and those limits in B2:B4, enter this formula in C2:

=FREQUENCY($A$2:$A$11,$B$2:$B$4)

In Microsoft 365, Excel spills the four results down from C2. They are 3, 3, 4, and 0: three values are at most 10; three are above 10 and at most 20; four are above 20 and at most 30; none exceed 30. The counts add to 10, matching the number of data values. The fourth result is not an error or an empty bin; it is the overflow group.

Enter the formula and make a chart

Put the numeric observations in one column, with a heading if useful. Keep the data range limited to the observations you want included.
Enter ascending bin limits in a separate column. The limits should be numeric, and each one should be higher than the preceding limit.
Enter the FREQUENCY formula in a blank cell beside the bin list. In current Microsoft 365, leave enough empty cells below it for the spill results. If Excel shows #SPILL!, check whether those cells contain anything or are merged.
Add labels for the ranges, including a label for the final overflow count. For the example, labels could be “≤10,” “>10 to ≤20,” “>20 to ≤30,” and “>30.”
Select the labels and their matching counts, then choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column. The extra overflow result needs its own label if it is included in the chart.

A column chart is used here because the counts have already been calculated by the formulas. To make the columns touch, select a column, open Format Data Series, and reduce Gap Width in Series Options. A zero gap gives the plot a familiar histogram appearance. In contrast, Excel’s built-in histogram chart calculates bins from the source data itself; it is available under Insert > Insert Statistic Chart > Histogram. That built-in option is convenient, but a formula table makes the bin counts visible and reusable.

Use COUNTIFS for explicit interval boundaries

FREQUENCY uses upper limits and includes a final overflow group. Sometimes a table is clearer when you specify both the lower and upper edge of every range, especially when you need bins such as 0 to under 10, 10 to under 20, and 20 to under 30. In that setup, use COUNTIFS to count values greater than or equal to the lower edge and strictly less than the upper edge.

Keep the same observations in A2:A11. Enter lower edges 0, 10, 20, and 30 in B2:B5, and matching upper edges 10, 20, 30, and 40 in C2:C5. With data in A2:A11, lower edges in B2:B5, and upper edges in C2:C5, enter this formula in D2 and fill it down through D5:

=COUNTIFS($A$2:$A$11,">="&B2,$A$2:$A$11,"<"&C2)

The results are 3, 3, 3, and 1. The value 30 falls in the last interval, 30 to under 40, rather than being counted in the preceding interval. This convention prevents a value that lies exactly on a shared boundary from being counted twice. The formula counts values in the specified ranges; values below 0 or at least 40 would not be included in this table. Add lower or upper intervals if your data can extend beyond those limits.

Choose bin widths that answer the question

Bin width affects the story a histogram tells. Very wide bins can hide clusters or unusual gaps; very narrow bins can make random variation look like a meaningful pattern. There is no single width that is best for every dataset, so choose a range that is understandable for the measurement and useful for the decision or comparison you are making.

Start with the units. For delivery time measured in minutes, 5-minute or 10-minute bins may be easier to interpret than fractions of a minute. For a score out of 100, bins of 10 points may be natural.
Consider the spread and sample size. A small dataset can produce many empty bins if the intervals are too narrow. A large dataset may need more detail to show its shape.
Use consistent boundaries when comparing groups. Two histograms are difficult to compare fairly if they use different bin widths or different starting points.
Check the extremes. Include bins that cover the observed minimum and maximum, or make a deliberate choice about how to show values outside the main range.

It can help to try two reasonable widths and compare the resulting shapes. Keep the underlying observations and counting rules unchanged so the comparison reflects the bin choice rather than a different sample.

Common errors and boundary issues

A frequent source of confusion is whether a boundary value belongs to the lower or upper interval. FREQUENCY includes a value equal to a listed limit in that limit’s bin. The COUNTIFS example instead uses inclusive lower edges and exclusive upper edges. Either convention can work, but write labels that match the formula and use the same convention throughout the table.

Counts do not add to the expected total: check for numbers outside the selected intervals, blank cells, text-formatted numbers, or an omitted overflow group. With FREQUENCY, include its final result when checking the full count.
Unexpected zero counts: inspect the bin limits for typos, wrong order, or an interval too narrow for the data.
#SPILL! appears: clear the cells where the dynamic result needs to go, and check for merged cells in the spill area.
A number appears to be ignored: confirm that it is stored as a number, not text. Imported values can look numeric while remaining text, which numeric counting may not treat as expected.
The chart looks like separate categories: verify that the labels are ordered numeric intervals and reduce the column gap width. A column chart does not automatically validate that the bins are sensible.

Also decide how to handle blanks and errors in the source. A blank observation is not a measured zero, so do not replace blanks with zero unless zero is genuinely the recorded value. Correct or exclude error cells before relying on the counts.

Formula histogram or Excel’s built-in chart?

A formula-based histogram is a strong choice when you want to audit the counts, control exact interval boundaries, reuse a summary table, or feed counts into other calculations. The bin limits and counts remain visible in worksheet cells, which makes it easier to explain how a chart was constructed.

The built-in Histogram chart is quicker when you mainly need a visual summary and are comfortable adjusting its bin settings. Select the numeric source data and use Insert > Insert Statistic Chart > Histogram; Excel creates the chart from the data. The chart’s horizontal axis can be formatted to change bin width or the number of bins, and its bin options can include an underflow or overflow bin. Its settings are part of the chart, rather than a separate formula table. For a report that requires exact, inspectable counts, formulas often make the logic easier to verify.

Neither method changes the underlying data. If you revise observations, formula counts recalculate when Excel recalculates the workbook, and a built-in chart reflects its source range. If new rows will be added regularly, consider using an Excel Table for the source data so the input range can expand as the table grows; check that the chart or formulas include the added observations.

Frequently Asked Questions

Can Excel make a histogram without the Analysis ToolPak?
Yes. FREQUENCY and COUNTIFS are worksheet functions and do not require the Analysis ToolPak. The ToolPak is an optional add-in, not a requirement for counting bins with formulas.

Why does FREQUENCY return one more result than the number of bins?
The last result counts values greater than the highest bin limit. With three limits, Excel returns four counts: three bounded groups and one overflow group.

Can I change the bin width?
Yes. Change the bin limits and the formula recalculates the counts. For a COUNTIFS table, update the lower and upper edges together so intervals remain continuous and do not overlap.

Does a histogram need touching columns?
A histogram represents continuous ranges, so adjacent columns commonly touch. A formula-based chart is a regular column chart, however, and its gap width may need to be set manually.

What if my data contains negative values?
FREQUENCY counts negative values in the first group if they are at or below the first bin limit. For more control, choose a first limit that suits the data or use explicit lower and upper bounds with COUNTIFS, making sure the intervals cover the negative 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.