How to make a frequency distribution in Excel

Set up the bins and their labels

A frequency distribution counts how many values fall in each range, or bin. Here the data is the delivery time of 12 food orders, and the bins are 10-minute bands. Column D holds where each bin starts, column E where it ends for the label, and the last bin, 50+, is open so nothing above 59 can be lost.

Pick bins that make sense to the reader: round numbers, the same width, and enough of them to show a shape (5 to 10 is typical). Start the first bin at or below the smallest value and make sure the last one covers the largest; =MIN(B2:B13) and =MAX(B2:B13) give 18 and 57 here.

Build the labels with a formula instead of typing them. If you type 10-19 into a cell, Excel turns it into a date (19 October). A formula, a typed en dash (10–19) or a leading apostrophe ('10-19) keeps it as text.

Count each bin with COUNTIFS

COUNTIFS checks the same column twice: at least the bin's start, and less than the next bin's start. Joining with & puts the number from the cell into the condition, so ">="&D3 becomes ">=20". The dollar signs keep the data range fixed while you fill the formula down from G2 to G5.

The last bin only needs the lower test: =COUNTIFS($B$2:$B$13,">="&D6) counts everything from 50 up. For the share of orders in each bin, divide by the total count: =G3/COUNT($B$2:$B$13) gives 33%, and =SUM($G$2:G3)/COUNT($B$2:$B$13) filled down gives the cumulative share.

Inclusive edges: why values go missing

The obvious formula uses the To column: ">="&D4 and "<="&E4, at least 30 and at most 39. It works until a value falls between one bin's end and the next bin's start. Order #4110 took 39.5 minutes: it is above 39, so it misses 30–39, and below 40, so it misses 40–49 too. With that formula the bins add up to 11 orders, not 12.

The fix is to define each bin by where the next one starts: at least 30 and less than 40. Every value then lands in exactly one bin, whatever its decimals, and no value is counted twice. Keep the To column for the labels only.

Whatever method you use, check it: =SUM(G2:G6) must equal =COUNT(B2:B13). Here both are 12. A gap means values are lost between or outside the bins; a bigger sum means bins overlap.

All bins in one formula (Excel 365)

In Excel 365, COUNTIFS accepts a list of bin starts and returns one count per bin. SEQUENCE(5,1,10,10) makes the starts 10, 20, 30, 40 and 50, LET names them lo, and HSTACK puts labels and counts side by side. Change the 10s to resize every bin at once.

This version gives every bin an upper limit, so the last one is 50–59. If a value could be 60 or more, add a bin or use the table above with its open 50+ bin.

The FREQUENCY function

FREQUENCY(data_array, bins_array) is Excel's built-in version. The bins are upper limits: with 19, 29, 39 and 49 in E2:E5, =FREQUENCY(B2:B13,E2:E5) counts values up to 19, then above 19 up to 29, and so on, plus one extra count for everything above the last limit. It returns one more number than there are bins. In Excel 365 it spills; in older versions select five cells, type the formula and press Ctrl+Shift+Enter. It ignores blanks and text.

Because each bin runs from just above the previous limit up to and including its own, FREQUENCY never loses a value. But the edges differ from the labels: the 39.5-minute order is counted with 40–49, so the counts are 1, 4, 3, 3 and 1 instead of 1, 4, 4, 2 and 1. With whole numbers the two methods agree; with decimals, COUNTIFS with "less than the next start" matches labels like 30–39. To reproduce one FREQUENCY bin with COUNTIFS, use its edges: =COUNTIFS(B2:B13,">39",B2:B13,"<=49") gives the fourth count, 3.

Make a histogram

For a chart, the safest route is a column chart of your own frequency table, because it uses exactly your bins and labels. Excel's built-in histogram chart (Excel 2016 and later) is quicker but picks its own bins, starting at the smallest value, and its bins include their upper edge, so its counts can differ from your table.

Built-in chart: select B1:B13, go to Insert > Insert Statistic Chart > Histogram, then right-click the horizontal axis, choose Format Axis and set Bin width (10), and an Overflow or Underflow bin if you want open ends. Labels like (20, 30] mean "above 20, up to 30".

1. Select the labels and counts, F1:G6.
2. Go to Insert > Insert Column or Bar Chart > Clustered Column.
3. Right-click a column, choose Format Data Series and set Gap Width to 0% so the bars touch, as a histogram should.
4. Add a chart title that names the unit, such as "Delivery time (minutes)".