Excel Histogram Frequency Table: How to Make One

Charts & pivot tables · Updated November 19, 2026

An Excel histogram frequency table shows how many numeric values fall within each interval, or bin. In Microsoft 365 for Windows, enter ascending bin limits and use =FREQUENCY(A2:A11,C2:C5) to return the counts, including a final count for values above the largest limit; you can also create a table with the Analysis ToolPak or make a histogram chart.

What a histogram frequency table shows

A frequency table summarizes the distribution of a numeric list. Instead of showing every individual measurement, it groups measurements into ranges and reports how many fall in each range. A histogram presents the same grouped information visually, with adjacent bars whose heights represent frequencies.

For example, a list of test scores can be grouped into ranges ending at 20, 30, 40, and 50. The resulting counts help show whether most scores cluster in the middle, whether the distribution is spread out, or whether a few unusually high or low values are present. The table is useful on its own when you need exact counts; the chart makes the overall shape easier to see.

A bin is not necessarily a range label. In Excel’s FREQUENCY function, each bin value is an upper limit. If the bins are 20, 30, and 40, the first count is values less than or equal to 20, the second is values greater than 20 and less than or equal to 30, and the third is values greater than 30 and less than or equal to 40. The function returns one additional count for values greater than the last bin.

Build a frequency table with FREQUENCY

This example uses ten measurements in A2:A11: 12, 18, 21, 24, 27, 31, 33, 38, 42, and 49. Enter the upper bin limits 20, 30, 40, and 50 in C2:C5. With the measurements in A2:A11 and those limits in C2:C5, enter this formula in D2:

=FREQUENCY(A2:A11,C2:C5)

In current Microsoft 365 for Windows, the formula spills into the cells below it. Leave D3:D6 empty so the results can spill. It returns 2, 3, 3, 2, and 0: two values are at most 20; three are above 20 and at most 30; three are above 30 and at most 40; two are above 40 and at most 50; and none are above 50. The five results correspond to four bin limits plus the overflow count.

  1. Put the raw numeric observations in one column, with a heading in the row above if desired.
  2. Enter ascending numeric bin limits in a separate column. Keep the limits in the same order you want the table to display.
  3. Enter the FREQUENCY formula in a clear output column, using only the data cells and bin cells in its ranges.
  4. Add interval labels beside the results so readers can understand the bounds, and label the final result as greater than the largest bin.

For the example, descriptive labels could be “20 or less,” “Over 20 to 30,” “Over 30 to 40,” “Over 40 to 50,” and “Over 50.” Those labels make clear that each upper boundary is included in its bin. Do not label a bin simply “20–30” unless you clarify the boundary convention, because that label does not tell a reader where a value of exactly 20 belongs.

Choose useful bins and labels

Bin choice affects what the table reveals. Very wide bins can hide meaningful variation, while many narrow bins can make a small data set look erratic. There is no single best bin width for every list: consider the number of observations, the measurement scale, the question you are trying to answer, and whether readers need to compare the table with another report.

  • Use meaningful boundaries. For ages, whole-number groups may make sense; for response times, intervals in seconds may be more useful.
  • Cover the data range. Set the largest limit above the largest expected value if you want the overflow count to be zero, or keep the overflow count visible if values above the stated range matter.
  • Keep limits ascending. This makes the intervals and output easy to audit and interpret.
  • Choose consistent widths when appropriate. Equal-width bins make comparisons easier, although a subject-specific grouping can be more useful when the data have natural thresholds.

For a comparative report, use the same bin limits for each group. If one class is grouped into score ranges ending at 20, 30, 40, and 50 while another uses different cutoffs, their frequencies are not directly comparable. Also consider showing percentages as well as counts when the groups have different sample sizes; a group of 100 will often have higher raw counts than a group of 20 even if their distributions are similar.

Use the Analysis ToolPak instead

The Analysis ToolPak can produce a static frequency output from a data range and a separate bin range. On Windows, if the add-in is not enabled, choose File > Options > Add-ins. In the Manage box, select Excel Add-ins, click Go, check Analysis ToolPak, and click OK. Then choose Data > Data Analysis > Histogram.

In the Histogram dialog, select the input range containing the observations and the bin range containing the upper limits. Choose an output location, and select the chart option if you want Excel to create a histogram chart with the output. Select the labels option only if the selected input or bin ranges include headings. Check the generated output: it reports the bin categories and frequencies, including a “More” category for observations above the final bin.

The ToolPak is useful when you want a one-time analysis or a chart created alongside the output. Its results do not automatically recalculate like a worksheet formula when source data change; run the analysis again to refresh them. On Mac, the Analysis ToolPak is also available in supported desktop Excel versions, but add-in setup is accessed through Tools > Excel Add-ins rather than Windows’ File > Options path. Excel for the web does not provide the desktop Analysis ToolPak dialog, so use worksheet formulas or a chart there.

Make a histogram chart from the data

To create a chart directly from raw observations in Microsoft 365 for Windows, select the numeric data and choose Insert > Insert Statistic Chart > Histogram. Excel groups the values into bins for the chart. Right-click the horizontal axis and choose Format Axis to adjust the bin settings, including bin width or number of bins, and to set overflow or underflow bins where appropriate.

A chart and a frequency table are related but are not interchangeable in every workflow. The chart’s automatic binning may use different boundaries from a table you created manually. If exact categories matter, set the chart’s bin options deliberately or use a frequency table with your chosen limits. You can also chart the table’s bin labels and counts as a column chart, but keep in mind that a conventional histogram uses touching bars to represent continuous intervals.

On Mac, the Insert tab also offers a Histogram chart in supported desktop editions, although the ribbon layout and axis-formatting pane can differ. Excel for the web has fewer chart-formatting controls than desktop Excel, and some options may not be available. If you need consistent bins across reports or need to preserve exact frequency values, keep the table as the source of truth.

Alternative formulas and dynamic updates

FREQUENCY is concise, but COUNTIF or COUNTIFS can make interval logic visible in individual table rows. For the same example, with observations in A2:A11, enter this formula for the interval above 20 through 30:

=COUNTIFS($A$2:$A$11,">20",$A$2:$A$11,"<=30")

The result is 3, because 21, 24, and 27 meet both conditions. COUNTIFS is useful when bins have different widths or when you want to write a formula for each labeled interval. But each formula must use the correct lower and upper boundaries, and you must decide how to handle the first interval and values above the final interval. FREQUENCY handles the extra overflow category for you.

When the source data are likely to grow, avoid ranges that stop just above the current last observation. You can use an Excel Table for the source data and update the formula’s data range as needed, or allocate a sufficiently large range while ensuring it contains only the intended values. A blank cell is not counted as a numeric observation by FREQUENCY, but text accidentally included in the source range can make the selected range harder to audit. Check for errors and non-numeric entries before interpreting the results.

Common problems and checks

  • The formula does not spill: Clear cells below the formula where results need to appear. A blocked spill range produces a spill error.
  • There is one more result than bin limits: This is expected. The last frequency counts observations greater than the largest bin.
  • A boundary value seems misplaced: FREQUENCY includes each bin’s upper limit. A value of exactly 30 belongs in the bin ending at 30, not the next one.
  • Counts do not add up to the number of records: Check the source range for missed rows, text values, error cells, and numbers outside the last bin. Add all FREQUENCY outputs, including the overflow count, before comparing with the numeric observations.
  • Results change after sorting or editing: Sorting numeric observations does not change frequencies, but editing the data or limits does. Formula-based results update automatically when their referenced cells change.

A quick validation is to add the returned frequencies and compare that sum with the number of numeric observations in the input range. In the ten-value example, 2 + 3 + 3 + 2 + 0 equals 10. If the total differs, investigate the ranges and data types before relying on the chart or summary.

Frequently Asked Questions

Does FREQUENCY include the bin limit?
Yes. Each bin count includes values less than or equal to its limit and greater than the preceding limit. The first bin counts values at or below its limit; a final extra result counts values above the last limit.

Why does my frequency table have an extra row?
FREQUENCY returns one more result than the number of bins. That extra result is the count of values above the largest bin limit, not an error. Label it clearly, even if its count is zero.

Can I make an Excel histogram without the Analysis ToolPak?
Yes. Use FREQUENCY in a worksheet, or select the raw observations and choose Insert > Insert Statistic Chart > Histogram in supported desktop Excel. The ToolPak is one option, not a requirement.

Should I use a frequency table or a histogram chart?
Use a table when exact counts and explicit interval labels matter. Use a chart when you want to see the distribution’s shape quickly. For careful analysis, keep both and ensure that the chart uses the same bin boundaries as the table.

Can Excel count percentages instead of frequencies?
Yes. First calculate the bin counts, then divide each count by the total number of numeric observations. Make sure the denominator represents the observations included in the frequency table, and format the resulting cells as percentages.

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.