Excel Histogram Equation: Formulas, Bins, and Charts

Charts & pivot tables · Updated November 19, 2026

Excel has no single “histogram equation”: a histogram groups numeric values into intervals called bins and shows how many values fall in each one. In Microsoft 365 for Windows, select the data and choose Insert > Insert Statistic Chart > Histogram to make one; for explicit counts, use a formula such as =COUNTIFS($B$2:$B$11,">"&D2,$B$2:$B$11,"<="&E2), with the cell layout described below.

What a histogram equation means

A histogram is a frequency distribution displayed as adjoining bars. Each bar represents a range of numeric values, and its height represents the number of observations in that range. For example, a histogram of delivery times might use bins from 0 to 5 minutes, over 5 to 10 minutes, and over 10 to 15 minutes. A value of 8 belongs in the second bin, so it contributes one observation to that bar.

The underlying idea is simple: count the data points that meet each bin’s boundary rules. In statistics, the count for a bin is often called its frequency. If bins have equal widths, comparing their frequencies is straightforward. If widths differ, a taller bar can reflect a wider interval rather than a higher concentration of observations; in that case, density is more informative. Excel’s standard histogram chart displays counts, not a density equation.

So, when someone asks for the Excel histogram equation, they may mean one of three things: the statistical rule for counting values, a worksheet formula that calculates those counts, or the chart feature that builds the bars automatically. Which approach is best depends on whether you need a quick visual summary or a reusable, auditable table of results.

Prepare the data and define bins

Put the observations in one column, with a clear heading and one numeric value per row. Avoid including the heading in the range unless you tell the chart that the first row contains a label. Blank cells are generally ignored, but text mixed into the observations can cause confusing results. Check that values which look like numbers are stored as numbers.

Decide what each interval means before counting. A useful convention is to include the upper endpoint of each bin and exclude the lower endpoint, except for the first interval. That makes boundaries unambiguous: 5 goes in the first bin, while a value greater than 5 through 10 goes in the second. Write the limits in adjacent columns so readers can see the intervals, for example:

  • First interval: 0 through 5, including both endpoints.
  • Second interval: greater than 5 through 10.
  • Third interval: greater than 10 through 15.

This convention is not the only valid one. The important thing is to state it and apply it consistently. If you instead define intervals as lower-inclusive and upper-exclusive, change the comparison operators in the formulas accordingly. Pay particular attention to values exactly on a boundary, since those are where inconsistent rules create duplicated or missing counts.

Create a histogram chart in Excel

For a quick chart in Excel for Microsoft 365 on Windows, select the numeric observations, then choose Insert > Insert Statistic Chart > Histogram. Excel creates bins and plots the number of observations in each. If the selection includes a heading, Excel will usually use it as a series label; check the chart if the heading appears as a data value.

To control the grouping, right-click the horizontal axis and choose Format Axis. In the Format Axis pane, under Axis Options, set a Bin width or Number of bins. You can also set an Overflow bin to group values above a chosen limit, or an Underflow bin to group values at or below a chosen limit. Those options are useful when the tails of a distribution would otherwise produce sparse bars. The chart’s automatic grouping is convenient, but it may not match a reporting standard or a meaningful unit such as five-minute intervals.

Excel for Mac also offers a histogram chart in supported current versions, though ribbon details and available options can differ by release. Excel for the web may not expose every chart-formatting control available in the desktop app. If a specific bin setting is missing, use desktop Excel or calculate the bins and frequencies in worksheet cells, then chart that summary.

Count each bin with COUNTIFS

A formula table is often preferable when the exact count matters or the chart needs to update predictably as the data changes. For this worked example, assume the observations are in B2:B11 and contain 2, 4, 4, 5, 7, 8, 10, 12, 12, and 15. Put each bin’s lower limit in column D and upper limit in column E: row 2 has 0 and 5, row 3 has 5 and 10, and row 4 has 10 and 15.

With the observations in B2:B11, the lower limit in D2, and the upper limit in E2, enter this formula in F2 and fill it down for the later bins:

=COUNTIFS($B$2:$B$11,">"&D2,$B$2:$B$11,"<="&E2)

This counts values greater than the lower limit and less than or equal to the upper limit. For row 2, it returns 3, not 4, because this version excludes 0 through 5’s lower boundary but includes only values greater than 0: the observations 2, 4, 4, and 5 total four values. To include the first bin’s lower endpoint correctly, use a separate first-bin formula:

With the observations in B2:B11 and the first bin’s lower and upper limits in D2 and E2, use this formula in F2:

=COUNTIFS($B$2:$B$11,">="&D2,$B$2:$B$11,"<="&E2)

It returns 4 for 0 through 5. For the next bin, use the greater-than lower-bound formula above and fill it down from row 3. The second bin returns 3 (7, 8, and 10); the third returns 3 (12, 12, and 15). The counts add to 10, matching the number of observations. If your data contains values below the first limit or above the last limit, add bins for those values or check them separately.

Use FREQUENCY for upper-bound bins

Excel’s FREQUENCY function is another way to count observations. It takes a data range and a list of bin upper limits. Values are counted in successive groups up to each limit; it also returns an extra result for values above the final limit. This differs slightly from a table of lower and upper bounds, so make sure its boundary convention matches what you intend.

With observations in B2:B11 and upper limits 5, 10, and 15 in D2:D4, enter this formula in F2 in Microsoft 365:

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

In current Microsoft 365, the results spill into four cells: 4, 3, 3, and 0. The first three are the counts at or below 5, then above 5 through 10, then above 10 through 15. The final 0 counts values above 15. Leave the spill area empty so Excel can display all results. In older Excel versions that do not support dynamic arrays, FREQUENCY may need to be entered as a legacy array formula by selecting the output range first and pressing Ctrl+Shift+Enter. The Mac equivalent uses Control+Shift+Return in versions requiring legacy array entry.

Choose bin width and interpret the bars

There is no universally correct number of bins. Too few bins can hide useful variation; too many can make random bumps look like meaningful patterns. Choose limits that suit the question, the sample size, and the units readers understand. For example, a monthly total might use bins of 100 units, while a measurement recorded to tenths may need narrower intervals. If you compare multiple groups, use the same bin boundaries for each group.

For a simple evenly spaced table, the width is the upper limit minus the lower limit. With the lower limit in D2 and upper limit in E2, this formula in G2 returns 5:

=E2-D2

If the first bin has frequency 4 and width 5, its frequency density is 0.8 observations per unit. With the count in F2 and width in G2, this formula in H2 calculates that value:

=F2/G2

Frequency density is useful for comparing bins of unequal widths, because the bar area rather than just its height corresponds to frequency. Excel’s built-in histogram chart does not automatically turn unequal-width bins into a density histogram. For that case, calculate the density table yourself and make a column chart with the appropriate formatting, or use a statistical tool that supports density histograms.

Histogram chart or formula table?

The built-in chart is the quickest choice when you want to explore a single numeric column and are comfortable with Excel’s bin controls. It adjusts the display as the data changes and requires little setup. A formula table is more transparent: you can inspect every limit and count, include results in reports, and reuse identical bins across different datasets. It also makes it easier to identify values outside the intended range.

The Analysis ToolPak provides another worksheet-based option. If Data Analysis is not on the Data tab, enable the Analysis ToolPak through File > Options > Add-ins, select Excel Add-ins in the Manage box, choose Go, and check Analysis ToolPak. Then choose Data > Data Analysis > Histogram, specify the input range and bin range, and select an output location. You can request a chart in the dialog. ToolPak output is a calculated report rather than the same automatically updating chart experience; rerun the analysis if the underlying data changes. Add-in setup and menu labels can differ on Mac.

Common histogram problems

  • A boundary value appears in an unexpected bar: confirm whether your limits include the lower endpoint, upper endpoint, or both. Apply one convention consistently.
  • The total count is smaller than the number of observations: check for blanks, text-formatted numbers, and values outside the first and last bins. With a frequency table, the bin counts should account for all numeric observations you intend to include.
  • The chart has a very long tail or many empty bars: adjust the bin width or use underflow and overflow bins. Do not hide unusual values without deciding whether they are errors or meaningful observations.
  • The bars do not touch: a histogram represents continuous numeric intervals, so adjoining bars are normally appropriate. A column chart of category labels may show gaps; that chart is not necessarily configured as a histogram.

Frequently Asked Questions

What is the equation for a histogram? There is no single equation that creates every histogram. Its core calculation is the number of observations inside each bin. In Excel, COUNTIFS can calculate that number from explicit lower and upper limits, while the Histogram chart can group selected numeric data automatically.

Does Excel have a histogram function? Excel has a built-in Histogram chart and the FREQUENCY worksheet function. FREQUENCY returns counts based on upper-bound bins; COUNTIFS is often easier when you want to spell out both ends of each interval.

How do I change the bins in an Excel histogram? Right-click the chart’s horizontal axis, choose Format Axis, and adjust Bin width or Number of bins under Axis Options. Underflow and Overflow bins can group values beyond selected thresholds.

Why does my histogram count seem wrong? Check whether values equal to a bin boundary belong in the lower or upper interval, and make sure every intended observation falls within the bin range. Also look for numbers stored as text and for a heading accidentally included as data.

Should histogram bars show frequency or percentage? Frequency is the raw count in each bin and is the usual default. To show a percentage, divide each bin count by the total number of observations and format the result as a percentage; use the same denominator for every bin.

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.