To apply a formula to histogram data in Microsoft Excel for Microsoft 365 on Windows, put your numeric observations in one column and bin upper limits in another, then enter =FREQUENCY($A$2:$A$13,$D$2:$D$6) in the first results cell. Excel returns a count for each bin and one extra count for values above the final limit; you can chart those results or use Excel’s built-in Histogram chart instead.
What a histogram formula calculates
A histogram groups numeric observations into intervals, called bins, and counts how many values fall in each interval. For example, a set of ages could be grouped into ranges ending at 10, 20, 30, and 40. The formula does not usually calculate a single summary statistic; it produces a sequence of frequencies that describes the distribution.
That distinction matters because a histogram is not the same as a column chart of categories. Histogram bars represent numeric intervals, and adjacent bars usually touch to show that the intervals form a continuous scale. A formula-driven histogram is useful when you need the counts as worksheet values, want to control the intervals precisely, or need to reuse the counts in another calculation. If you only need a quick visual, Excel can create a histogram directly from the observations without a separate frequency table.
Set up the data and bin limits
Keep the raw measurements in one column, with one observation per cell. In this example, the values are in A2:A13: 4, 7, 12, 15, 18, 21, 23, 27, 31, 34, 38, and 42. Put the bin upper limits 10, 20, 30, 40, and 50 in D2:D6, in ascending order. Headings such as Value, Upper limit, and Frequency help make the table easier to understand, but do not include headings in the formula ranges.
With observations in A2:A13 and bin limits in D2:D6, enter this formula in E2:
=FREQUENCY($A$2:$A$13,$D$2:$D$6)
In Microsoft 365, FREQUENCY returns an array that spills into the cells below E2. With the example values, the six results are 2, 3, 3, 3, 1, and 0. The first count is values at or below 10; the next is values greater than 10 and at or below 20; the following counts cover the corresponding intervals through 50. The sixth result is the number greater than 50, which is zero in this example.
FREQUENCY always returns one more result than the number of bin limits. The additional result is an overflow bin for values greater than the last limit. Label that row accordingly, or omit it from a chart if you have confirmed that it contains no values and do not want to display an overflow category. Do not mistake the extra result for a duplicate of the final regular bin.
How FREQUENCY assigns values to bins
Each value is counted once. Given limits of 10, 20, and 30, the first group is less than or equal to 10; the second is greater than 10 and less than or equal to 20; the third is greater than 20 and less than or equal to 30; and the final, extra group is greater than 30. Thus, a value exactly equal to a limit belongs to the bin ending at that limit.
FREQUENCY is a worksheet function, not a chart command. It works well when the bins are stored in cells and you want the frequency table to update as the source values change. The dollar signs in the example make the source and bin ranges absolute references, so they stay fixed if you copy a formula elsewhere. In the Microsoft 365 version, the formula spills automatically; leave the output cells below the formula empty so they are available for the results.
Older Excel versions may require selecting the full output range and confirming FREQUENCY as an array formula with Ctrl+Shift+Enter. Microsoft 365 generally uses dynamic arrays and Enter instead. If you open a workbook in a version without dynamic arrays, check that the formula has been entered for the full result range rather than expecting it to spill.
Use COUNTIFS for custom intervals
COUNTIFS is an alternative when you want each bin count in its own cell, need different interval rules, or prefer to see the counting logic directly. For the first bin, with observations in A2:A13 and its upper limit in D2, enter this in E2:
=COUNTIF($A$2:$A$13,"<="&D2)
The example returns 2 because 4 and 7 are the only observations at or below 10. For the next bin, with observations still in A2:A13, the preceding upper limit in D2, and the current upper limit in D3, enter this formula in E3:
=COUNTIFS($A$2:$A$13,">"&D2,$A$2:$A$13,"<="&D3)
This returns 3 for the interval greater than 10 and at or below 20: the values are 12, 15, and 18. Copy the E3 formula down through E6 to count the later intervals; the relative references to the limits advance while the observation range remains fixed. If you want to count values above the last limit, add a separate COUNTIF formula for that overflow group or use FREQUENCY, which supplies it automatically.
These examples use upper limits to define intervals. If your bins instead have explicit lower and upper bounds, a COUNTIFS formula can test both boundaries. Decide whether a boundary belongs to the lower or upper bin before building the formulas; using greater-than-or-equal conditions on both neighboring bins can count a boundary value twice.
Turn the frequency table into a chart
To make a chart from formula results, create a label for each interval and select the labels alongside their counts. For the example, labels might read 0–10, over 10–20, over 20–30, over 30–40, and over 40–50. Select those five labels and their five corresponding counts, then choose Insert > Insert Column or Bar Chart > Clustered Column. The extra FREQUENCY result should be represented separately only if you want to show values above 50.
A formula-built frequency table gives you control over the category labels, but the resulting chart is technically a column chart. To make it look like a histogram, select a bar, open Format Data Series, and reduce the Gap Width so the bars touch or nearly touch. Use clear interval labels; labeling every bar only with its upper limit can make the boundaries less obvious.
For a direct histogram chart, select the original numeric observations and choose Insert > Insert Statistic Chart > Histogram. Excel calculates the bins for the chart rather than using your FREQUENCY table. To change the grouping, right-click the horizontal axis, choose Format Axis, and adjust the bin settings, including bin width or number of bins. The available options can vary with the chart and Excel version. On Mac, the equivalent chart is available from the Insert tab, though ribbon layout and context-menu wording can differ. Excel for the web may have fewer chart-formatting controls than the desktop app.
Choose formula bins or Excel’s Histogram chart
Neither approach is universally better. A built-in Histogram chart is convenient when you want Excel to choose a reasonable grouping and need a quick view of the distribution. A formula table is more transparent and portable: the bin limits are visible in cells, the counts can feed other formulas, and you can define unusual intervals explicitly.
- Use FREQUENCY when you have a sorted list of numeric upper limits and want all counts, including the overflow count.
- Use COUNTIFS when you want separate formulas, custom interval logic, or counts that sit beside other calculations.
- Use the built-in Histogram chart when a visual is the main goal and automatic binning is acceptable.
- Use the Analysis ToolPak’s Histogram tool when you want a generated frequency table and chart through a data-analysis command. If it is not available, enable the Analysis ToolPak Excel add-in in Excel options.
Remember that changing bin width can change the apparent shape of a distribution. A narrow width reveals more local variation but can make the chart look noisy; a wide width makes the overall pattern easier to see but may hide clusters. For a fair comparison between two groups, use the same bin boundaries for both rather than letting each chart choose different bins.
Common formula and chart problems
- #SPILL!: With FREQUENCY in Microsoft 365, one or more cells needed for the output may contain data, merged cells, or another obstruction. Clear the cells below the formula or move the formula to an empty area.
- Counts do not add up: Check that the bin limits are ascending, the source range excludes headings, and the final FREQUENCY overflow result has not been overlooked. For COUNTIFS, confirm that adjacent intervals use consistent boundary rules.
- Numbers seem to be ignored: Values stored as text may not behave like numbers. Check for leading apostrophes, imported text, or unexpected spaces, and convert the source values to numeric data before counting.
- Unexpected zeroes: Confirm that the bin limits use the same units and scale as the observations. Dates are stored as serial numbers in Excel, so date bins must be genuine dates or equivalent serial values, not unrelated text labels.
- Formula shows as text: The result cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Change the format to General, then re-enter the formula.
- Chart includes the wrong rows: A spilled FREQUENCY formula has one extra output. Select only the intended bins and counts, and make sure the labels align with the count rows.
Blank cells and nonnumeric entries should be reviewed before interpreting a histogram. FREQUENCY counts numeric values; it does not turn a text entry such as “n/a” into a measurement. If some observations are missing, decide whether to exclude them or correct the source data, and keep that decision consistent across comparisons.
Frequently Asked Questions
Can I apply a formula directly to an Excel Histogram chart?
A Histogram chart groups the selected raw data itself; you do not enter a worksheet formula into the chart. Use FREQUENCY or COUNTIFS in a worksheet table when you need formula-based counts, then chart that table if desired.
Why does FREQUENCY return one more number than I expect?
It returns one frequency for each bin limit plus an extra frequency for values greater than the final limit. With five bin limits, expect six results. The final result may be zero, but it is still part of the function’s output.
Can I use COUNTIF instead of FREQUENCY?
Yes. COUNTIF can count observations up to a single upper limit, while COUNTIFS can count between lower and upper limits. For several intervals, you need a count formula for each one; FREQUENCY calculates the full set from a bin list.
How do I change the width of an Excel histogram bin?
For a built-in Histogram chart, right-click the horizontal axis, choose Format Axis, and adjust Bin width or the number of bins. For a formula-based histogram, change the values in the bin-limit cells and let the frequency formulas recalculate.
Do histogram bins include their endpoint?
FREQUENCY includes each bin’s upper limit in that bin. For example, with limits 10 and 20, a value of exactly 20 belongs to the bin greater than 10 and less than or equal to 20. COUNTIFS follows the operators written in its criteria, so set those operators deliberately.
Will the formula update when I add more data?
Only if the new observations fall within the referenced range. A fixed range such as A2:A13 will not automatically include values entered in A14. Extend the reference, use an Excel table as the source, or use a suitably sized range so new records are included.
Related
- Excel Heat Mapping: How to Create Useful Color Scales
- Excel Heatmap Formatting: Color Scales and Tips
- Excel Heat Map Over a Map: Methods, Data, and Setup
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Add a Formula in an Excel Pivot Table: Step by StepTo add a formula directly to a PivotTable in Excel for Microsoft 365 on Windows, select the PivotTable,…
- Microsoft Excel Combo Charts: How to Create and FormatA Microsoft Excel combo chart displays two or more data series as different chart types, such as columns…
- Google Sheets Chart Tutorial: Create and Customize ChartsTo create a chart in Google Sheets, select the cells containing your data, choose Insert > Chart, then…
- Excel Change Pivot Table Data Range: Steps and FixesTo change a PivotTable’s source range in Excel for Microsoft 365 on Windows, click inside the PivotTable, choose…
- Spider Web Chart in Excel: How to Create and Read ItA spider web chart—usually called a radar chart in Microsoft Excel—plots several categories around a circle so you…
- Excel Pie Chart Labels: Add, Format, and TroubleshootTo add labels to an Excel pie chart, select the chart and choose Chart Design > Add Chart…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.