Excel frequency distribution: bins and counts

An Excel frequency distribution counts the observations in each interval so you can see where order values or delivery times cluster. Use explicit upper limits, retain the overflow count, and prove that the bins contain every numeric observation exactly once.

Start with one observation per row

Take an order export with an order ID, order date, currency and net order value. Select the period and business unit before calculating the distribution. For order values, combine order lines into orders first; otherwise the result describes lines, with large multiline orders counted repeatedly.

The example is a US distributor's September order extract. Values exclude sales tax and canceled orders. Every amount is USD, and the export has already been reduced to these completed orders. The header is in row 1, with IDs in A2:A13 and values in B2:B13.

Keep the total outside the calculation range. Check that the order IDs are unique and the amounts are numeric. If an export contains text amounts or unexplained blanks, use the spreadsheet cleaning checklist before assigning bins.

Define bins before looking for a story

Use upper limits of 500, 1,000, 1,500 and 2,000 in D2:D5. These create equal-width intervals across the nonnegative values, plus an open overflow interval. The first bin includes everything at or below $500; the last includes everything above $2,000.

The boundaries must be unambiguous. An order of $500 belongs in the first bin. An order of $501 belongs in the second. For real amounts with cents, the second bin means greater than $500 and at most $1,000, rather than a label that appears to exclude amounts between whole dollars.

Choose intervals that support the decision: purchasing thresholds, service categories or a consistent operational range. A frequency distribution shows shape; the mean and spread belong with the existing descriptive statistics in Excel guide.

Calculate counts with FREQUENCY

Leave E2:E6 empty and enter this formula in E2:

=FREQUENCY(B2:B13,D2:D5)

In current Microsoft 365, press Enter and the results spill into the cells below. For legacy array entry, select E2:E6 first and confirm with Ctrl+Shift+Enter. Microsoft's FREQUENCY reference explains both entry methods, the extra overflow result, and the exclusion of text and blank cells.

The first count comes from 180, 250 and 500. The next comes from 501, 750 and 1,000. Each amount appears in one interval only.

For relative frequency, enter this in F2 and fill through F6:

=E2/COUNT($B$2:$B$13)

For cumulative frequency, enter this in G2 and fill through G6:

=SUM($E$2:E2)/COUNT($B$2:$B$13)

Format both columns as percentages with one decimal place. Retain full precision in the formulas. The cumulative result says that 9 / 12 = 75.0% of orders are at most $1,500; it does not say those orders generate that share of revenue.

Independently check boundaries and totals

Use COUNTIFS to verify an interior interval. For the second bin:

=COUNTIFS($B$2:$B$13,">"&D2,$B$2:$B$13,"<="&D3)

The result must be 3. For the first and overflow bins, respectively:

=COUNTIF($B$2:$B$13,"<="&D2)
=COUNTIF($B$2:$B$13,">"&D5)

These return 3 and 1. Microsoft's conditional counting guidance supports using COUNTIFS for values between limits and COUNTIF for a single boundary.

The main reconciliation is:

=SUM(E2:E6)-COUNT(B2:B13)

It must return zero: 3 + 3 + 3 + 2 + 1 = 12. Also compare the numeric count with the 12 source orders. A count reconciliation alone cannot detect text amounts that both COUNT and FREQUENCY ignore. Separately tie =SUM(B2:B13) to the export's $12,932 control total.

Turn the distribution into a chart

For the built-in chart, select B1:B13 and choose Insert > Insert Statistic Chart > Histogram. Open Format Axis > Axis Options and set the bin width and overflow deliberately; Microsoft's histogram instructions document these controls. Check every chart interval against the table rather than assuming automatic bins match your chosen limits.

Use the same boundaries in a comparison period. If its order volume differs, compare percentage columns and retain counts for context. The Excel chart selection guide covers presenting the comparison clearly.

Where it goes wrong

• Counting order lines. A line distribution answers a different question from an order distribution. Fix the observation before calculating.
• Moving boundaries between periods. An apparent shift may come entirely from regrouping the amounts. Save the limits beside the report.
• Hiding negative amounts in the first bin. Credits are at most $500 too. Review them separately or label a distribution that intentionally includes them.
• Comparing dollars with percentages of orders. Frequency measures how many orders sit in a range. Revenue concentration requires summing their amounts separately; order size and drop frequency connects that distinction to operating decisions.

Covirage is not affiliated with Microsoft.

Check your own order distribution in Covirage

Bring your order export and the interval definitions you want to compare. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Excel analysis with Covirage for the workbook context.