In Microsoft Excel for Microsoft 365, the core histogram formula is =FREQUENCY(A2:A13,C2:C6): with observations in A2:A13 and bin upper limits in C2:C6, it returns a count for each bin plus one overflow count. Use FREQUENCY for a formula-driven frequency table; use Excel’s Histogram chart when you want Excel to group and display the raw data automatically.
What a histogram formula counts
A histogram summarizes numerical observations by counting how many fall into successive ranges, called bins. For example, a list of test scores might be grouped into 0–9, 10–19, 20–29, and so on. The resulting counts show the distribution: whether values cluster in one range, spread broadly, or have a long tail.
Excel’s FREQUENCY function calculates those counts. It is not the same as a formula that counts each distinct value separately. A histogram groups values into intervals, so the bin boundaries you choose directly affect the result and the story the chart communicates.
For a concrete example, suppose A2:A13 contains 12, 18, 21, 25, 27, 31, 34, 36, 42, 48, 51, and 57. If C2:C6 contains upper limits 19, 29, 39, 49, and 59, the frequencies are 2, 3, 3, 2, and 2, followed by 0 values above 59. The extra final count is part of FREQUENCY’s normal output.
How FREQUENCY treats bin limits
FREQUENCY treats each bin value as an upper limit. The first result counts values less than or equal to the first limit. Each following result counts values greater than the previous limit and less than or equal to the current one. A final additional result counts values greater than the last limit.
In Microsoft 365, enter the formula once in the first output cell, and the results spill into cells below it. Leave those cells empty so Excel can display the full result. With the example above, the six results correspond to 19 or less, greater than 19 through 29, greater than 29 through 39, greater than 39 through 49, greater than 49 through 59, and greater than 59.
Older Excel versions may require selecting the full output range first and confirming FREQUENCY as an array formula with Ctrl+Shift+Enter. Excel 365 does not need that legacy keystroke. If the results appear as only one number, check whether the formula was entered in a version that does not support dynamic array spilling, or whether cells below the formula are occupied.
Choose bin widths and boundaries
Bin width is the size of each interval. Narrow bins reveal more detail but can make a chart noisy; wider bins simplify the shape but may hide meaningful differences. There is no single width that is best for every dataset. Consider the range of the observations, how many values you have, and what comparison you need to make.
- Use intervals that make sense for the subject, such as 10-point score bands or five-minute time ranges.
- Keep widths consistent when comparing the shape of a distribution across bins.
- Check the minimum and maximum values before choosing the first and last boundaries, so observations are not accidentally left outside the chart.
- Decide how to handle values exactly on a boundary. FREQUENCY assigns a value equal to a limit to the bin ending at that limit.
In the example, limits of 19, 29, 39, 49, and 59 create familiar integer groups: up to 19, 20–29, 30–39, 40–49, and 50–59. If the data include decimals, remember that the underlying rule is based on the actual numeric boundaries, not on how a cell is formatted to display its value.
Build a frequency table with COUNTIFS
COUNTIFS is useful when you want explicit control over the lower and upper edge of every interval, or when you need labels and counts in a conventional table. For example, put each lower boundary in D2:D6 and its upper boundary in E2:E6: 10 and 20 in row 2, 20 and 30 in row 3, and so on. With observations in A2:A13, enter the following in the count cell for the first interval and fill it down through the penultimate interval:
With values in A2:A13 and the first interval’s lower and upper limits in D2 and E2, respectively, this counts values from the lower limit up to, but not including, the upper limit: =COUNTIFS($A$2:$A$13,">="&D2,$A$2:$A$13,"<"&E2).
For the example’s 10–20 interval, the result is 2. Filling the formula down for boundaries 20–30, 30–40, and 40–50 returns 3, 3, and 2. Using an inclusive lower boundary and exclusive upper boundary avoids counting a value on a shared boundary twice.
For the last interval, if the upper limit should be included, use a separate final-row formula. With values in A2:A13 and the final lower and upper limits in D6 and E6, this counts values from the lower limit through the upper limit: =COUNTIFS($A$2:$A$13,">="&D6,$A$2:$A$13,"<="&E6). It returns 2 for the 50–60 interval in the example. The criteria use straight quotation marks in Excel; the encoded quotation marks shown here are the HTML representation of those characters.
Make a histogram chart from raw data
If you want a chart rather than a formula table, Excel for Microsoft 365 on Windows can create a histogram directly from the source observations:
- Select the numeric data, including a heading if the column has one.
- Choose Insert > Insert Statistic Chart > Histogram.
- Select the horizontal axis and open Format Axis to adjust the bin settings, such as the bin width or number of bins.
- Add a chart title and axis titles that explain what the values and counts represent.
The chart groups the selected values for display, so you do not need a FREQUENCY formula just to make this chart. The formula approach is more useful when you need visible counts, a reusable table, or calculations based on those counts. The chart’s axis settings let you change the grouping without editing a formula. Interface labels can differ slightly by Excel platform; the steps above describe Excel for Microsoft 365 on Windows.
Another option is to calculate counts first and then chart them. A regular column chart can display a frequency table, but format it to look like a histogram by setting the gap width between columns low or to zero. Use meaningful category labels, such as 10–19 and 20–29, rather than leaving the reader to infer what each count represents. A column chart built from a table is especially convenient when you need custom intervals or want to compare two groups using the same boundaries.
Histogram charts versus column charts
A histogram is intended for numerical data grouped into ordered intervals. Its bars usually touch because the intervals are continuous or adjacent. A standard column chart is more general: it compares categories such as departments, products, or months, and its columns usually have gaps.
It is possible to make a column chart resemble a histogram, but the chart type alone does not determine whether the analysis is appropriate. The interval definitions matter. If a table uses uneven bin widths, comparing bar heights alone can mislead because a wider interval may naturally collect more observations. For a standard frequency histogram, equal-width bins are usually easier to interpret; for specialized analysis, density rather than raw count may be needed.
Common formula problems and checks
- The output has one more count than the number of bins. That is expected for FREQUENCY: the last result counts values above the final bin limit.
- Counts do not add up to the number of observations. Check for blanks, text stored in the data range, numbers outside the chosen boundaries, or a final overflow count that was overlooked. FREQUENCY counts numeric values, not text labels.
- A value on a boundary appears in a surprising bin. FREQUENCY includes the upper boundary in the bin that ends there. COUNTIFS can use explicit inclusive and exclusive criteria to make the convention clear.
- The formula returns an error or will not spill. Confirm that the data and bin ranges are valid numeric ranges, and clear any occupied cells in the spill area. In older Excel, use the array-entry method supported by that version.
- The chart looks different from the formula table. The chart may be using its own bin width or bin count. Set the chart’s bin options to match the boundaries used in the table before comparing results.
A useful final check is to add all the frequency counts, including the overflow count, and compare that total with the number of numeric observations that should be included. If the totals differ, inspect the data range and boundary choices before relying on the chart.
Frequently Asked Questions
What is the Excel formula for a histogram?
Use FREQUENCY with the numeric observations as its first argument and the bin upper limits as its second. It returns the count for each bin plus a final count for values above the last limit.
Does FREQUENCY include the last bin value?
Yes. Each listed bin limit is included in the bin ending at that limit. The additional final result counts only values greater than the last listed limit.
Why does FREQUENCY return an extra number?
The final number is an overflow bin for observations above the largest boundary. Include it in the table or chart if those values matter; it will be zero when no observation exceeds the last limit.
Can Excel make a histogram without a formula?
Yes. Select the numeric data and choose Insert > Insert Statistic Chart > Histogram in Excel for Microsoft 365 on Windows. Adjust the bin settings in Format Axis if the automatic grouping does not suit the analysis.
Should I use FREQUENCY or COUNTIFS?
Use FREQUENCY for a quick, compact distribution table based on upper limits. Use COUNTIFS when you need explicit interval rules, custom table layout, or formulas that can be filled into individual rows.
Which method should you use?
For a standard histogram and a quick visual summary, insert Excel’s Histogram chart and adjust its bins. For counts that need to feed other calculations, use FREQUENCY. Choose COUNTIFS when the boundaries must be explicit or when a custom frequency table is part of a report. Whichever method you choose, document the interval boundaries and verify that the counts account for the observations you intend to include.
Related
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Columns Side by Side: How to ShowTo show PivotTable categories side by side, select the PivotTable, open the PivotTable Fields pane, and drag the…
- Year-Long Gantt Chart Template in Excel: Setup GuideTo make a year-long Gantt chart in Excel, list each task with its start and finish dates, create…
- Rename a Trendline in Excel: Steps, Labels, and FixesTo rename a trendline in Excel for Microsoft 365 on Windows, select the trendline, open its formatting pane,…
- Excel Sparklines: Can You Use Multiple Data Ranges?In Excel, each sparkline shows one sequence of values in one cell; it does not combine several separate…
- Excel Pivot Chart Without a PivotTable: What WorksYou can create an Excel PivotChart from a normal cell range without first converting the source data into…
- DAX Formula in Pivot Table: Measures and ExamplesA DAX formula in an Excel PivotTable is usually a measure: a calculation stored in the workbook’s Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.