Excel guide · 6 min read
How to calculate backorder rate in Excel
=SUM(F2:F15)/SUM(D2:D15). Line backorder rate = lines with a backorder / all lines, and order backorder rate = orders with any backordered line / all orders. On the sample data they are 20.6%, 35.7% and 62.5%, so always say which one you report.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Order date | SKU | Ordered | Shipped first | Backordered | Final ship date | Days on BO | Open age | Order has BO | Report date | Nov 13, 2026 |
| 2 | SO-2201 | Nov 2, 2026 | SKU-A100 | 40 | 40 | 0 | TRUE | Earlier report date | Nov 8, 2026 | |||
| 3 | SO-2201 | Nov 2, 2026 | SKU-B220 | 25 | 10 | 15 | Nov 9, 2026 | 7 | TRUE | |||
| 4 | SO-2202 | Nov 3, 2026 | SKU-A100 | 60 | 60 | 0 | FALSE | |||||
| 5 | SO-2202 | Nov 3, 2026 | SKU-C310 | 12 | 12 | 0 | FALSE | |||||
| 6 | SO-2203 | Nov 4, 2026 | SKU-B220 | 30 | 0 | 30 | Nov 12, 2026 | 8 | TRUE | |||
| 7 | SO-2204 | Nov 5, 2026 | SKU-D450 | 8 | 8 | 0 | FALSE | |||||
| 8 | SO-2204 | Nov 5, 2026 | SKU-C310 | 20 | 20 | 0 | FALSE | |||||
| 9 | SO-2205 | Nov 6, 2026 | SKU-C310 | 20 | 14 | 6 | Nov 10, 2026 | 4 | TRUE | |||
| 10 | SO-2206 | Nov 9, 2026 | SKU-A100 | 50 | 50 | 0 | TRUE | |||||
| 11 | SO-2206 | Nov 9, 2026 | SKU-D450 | 10 | 4 | 6 | 4 | TRUE | ||||
| 12 | SO-2207 | Nov 10, 2026 | SKU-B220 | 15 | 0 | 15 | 3 | TRUE | ||||
| 13 | SO-2207 | Nov 10, 2026 | SKU-A100 | 30 | 33 | 0 | TRUE | |||||
| 14 | SO-2208 | Nov 11, 2026 | SKU-D450 | 6 | 6 | 0 | FALSE | |||||
| 15 | SO-2208 | Nov 11, 2026 | SKU-A100 | 24 | 24 | 0 | FALSE |
Download this sheet as an Excel practice file (.xlsx)
Backordered units on each order line
The sheet is two weeks of order lines exported from an order system: order, order date, SKU, units ordered and units in the first shipment (D and E). When the remaining units finally ship, the date goes in G; while they are still waiting, G stays blank. SKU-B220 ran short for most of the period, so it shows up again and again.
In F2, =MAX(0,D2-E2) gives the units still owed on that line. Fill it down. MAX with 0 matters on row 13: SO-2207 ordered 30 of SKU-A100 and the warehouse sent 33. Without MAX that line would show -3 and cancel out 3 units of real backorders elsewhere.
Don't try to do the whole column in one go with =MAX(0,D2:D15-E2:E15). MAX returns one number for the whole range, the single biggest shortfall (30 here), not a value per row. Fill the row formula down, or use the SUMPRODUCT version in the next section.
Result: 72
72 units on backorder across the 14 lines: 15 + 30 + 6 + 6 + 15.
Unit backorder rate, and the over-shipment trap
Divide backordered units by units ordered. The quick version people type first, =(SUM(D2:D15)-SUM(E2:E15))/SUM(D2:D15), says 19.7%: it lets the 3 extra units on SO-2207 cancel 3 units that customers are still waiting for. The per-line MAX in column F gives the right answer.
Without a helper column, the formula below does the same in one cell and works in any Excel version: (D2:D15>E2:E15) is 1 only on short lines, so over-shipped lines add nothing.
If every shortfall is backordered, the unit backorder rate is 1 minus the unit order fill rate (with over-shipments capped): 79.4% filled, 20.6% backordered. The two split apart when customers cancel the missing units. A cancelled shortfall is a lost sale, not a backorder, so give cancellations their own column and keep them out of F.
Result: 0.20571428571 shows as 20.6% with Percent format
0.206, a 20.6% unit backorder rate: 72 of the 350 units ordered went on backorder.
Backorder rate by lines and by orders
Units hide small misses behind big orders. Count lines instead and each short line counts once, whatever the quantity: =COUNTIF(F2:F15,">0")/ROWS(F2:F15) is 5 of 14 lines, 35.7%.
The order rate is what customers feel: an order with one backordered line arrives in two boxes. Each order sits on several rows, so count unique order IDs. The formula below shows all three rates side by side; the third part keeps the IDs of lines with a backorder, removes repeats with UNIQUE and divides by the number of unique orders (Excel 365 and 2021).
In older Excel, use helper column J: =COUNTIFS($A$2:$A$15,A2,$F$2:$F$15,">0")>0 marks every line of an order that has any backorder. Then =SUMPRODUCT(J2:J15/COUNTIF(A2:A15,A2:A15))/SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15)) weights each line by 1 / its order's line count, so each order counts once: 62.5% again. Weighting column F directly instead of J gives 43.8%, because an order with one short line out of two would count as half an order.
Result: spills 1 row × 3 columns
| 0.20571428571 | 0.35714285714 | 0.625 |
20.6% of units, 35.7% of lines and 62.5% of orders: 5 of the 8 orders had at least one line backordered.
Open backorders on a report date
The rate tells you how often it happens. The open backlog tells you what is still owed today. A line is open on a report date when it was ordered on or before that date and its final ship date is blank or later than the date. That is two SUMIFS added together: one for blank ship dates ("" as the criteria matches empty cells) and one for ship dates after the report date.
With the report date in L1 (13 November), 21 units are still open: 6 of SKU-D450 on SO-2206 and 15 of SKU-B220 on SO-2207. Point the same formula at L2 (8 November) and it says 51 units on 3 lines, the backlog as it stood that day, because the lines filled on 9, 10 and 12 November were still waiting then. A line filled on the report date itself counts as closed.
Swap SUMIFS for COUNTIFS with F2:F15,">0" as an extra condition to count open lines instead of units. Keep the report date in a cell rather than using TODAY(): a weekly report should still say the same thing when you open it next month.
Result: 21
21 backordered units still open on 13 November.
Average days on backorder (and why open lines matter)
How long customers wait is often a better measure than how often. In H2, =IF(G2="","",G2-B2) gives the days from order date to final shipment for lines that have been filled. If the result shows as a date, set the column's number format to General. The formula below averages only rows with a final ship date.
Closed lines took 7, 8 and 4 days. But the average leaves out the two lines still waiting, and they get older every day. Column I tracks them: =IF(AND(F2>0,G2=""),$L$1-B2,"") is the age of each open backorder on the report date, and =MAXIFS(I2:I15,F2:F15,">0") shows the oldest, 4 days. Report the closed average and the oldest open age together. Averaging closed and open ages into one number (5.2 days here) makes the wait look shorter than it is.
Measuring from the first shipment date instead of the order date shows how long the backordered units themselves waited. Pick one, write it in the report heading and keep it.
Result: 6.3333333333 shows as 6.3 with Number format
6.3 days on average from order to final shipment, for the 3 backordered lines that have been filled.
Which SKUs cause the backorders
List each SKU with its backordered units and its own backorder rate, biggest first. UNIQUE builds the SKU list, SUMIFS takes that list as its criteria so it returns one total per SKU, and SORTBY sorts by units and then by rate (Excel 365).
SKU-B220 accounts for 60 of the 72 backordered units and missed 85.7% of what was ordered: a stock problem, not bad luck. Check its safety stock and reorder point, the supplier's real lead time and how many days it was out of stock with the stockout rate. SKU-D450 and SKU-C310 have 6 units each; D450 ranks higher because 6 of 24 is a bigger share than 6 of 52.
Result: spills 4 rows × 3 columns
| SKU-B220 | 60 | 0.85714285714 |
| SKU-D450 | 6 | 0.25 |
| SKU-C310 | 6 | 0.11538461538 |
| SKU-A100 | 0 | 0 |
SKU-B220 60 units (85.7%), SKU-D450 6 (25%), SKU-C310 6 (11.5%), SKU-A100 0 (0%).
Common mistakes
Subtracting total shipped from total ordered
Over-shipped lines cancel real shortfalls (19.7% instead of 20.6% here). Use MAX(0, ordered - shipped) on each line, or SUMPRODUCT with (ordered>shipped).
Counting cancelled shortfalls as backorders
If the customer cancels the missing units, nothing is owed any more. Record cancellations in their own column so the backorder rate and the open backlog stay honest.
Overwriting the first-shipment quantity when the backorder ships
If E becomes the total shipped, every filled backorder disappears from the history and last month's rate keeps falling. Keep the first shipment in E and the final ship date in G.
Reporting average days on backorder without the open lines
Lines still waiting are left out of the average. Show the oldest open backorder (MAXIFS on the open-age column) next to it.
FAQ
What is the formula for backorder rate?
Backordered units / units ordered for the same period. In Excel: =SUMPRODUCT((D2:D15>E2:E15)*(D2:D15-E2:E15))/SUM(D2:D15), with units ordered in D and units shipped in the first shipment in E. Use lines or orders instead of units for the line and order backorder rates.
Is backorder rate the same as 1 minus fill rate?
Only when every shortfall is backordered and over-shipments are capped. When customers cancel missing units, those units lower the fill rate but are not backorders, so the two no longer add up to 100%.
How do I count open backorders in Excel?
Sum the backordered units where the order date is on or before the report date and the final ship date is blank or after it: two SUMIFS added together, one with "" as the ship-date criteria and one with ">"&report_date.
How do I calculate days on backorder?
Subtract the order date from the date the backordered units shipped: =G2-B2, and average the filled lines with AVERAGEIFS. For lines still open, subtract the order date from the report date to get their age.
What is a good backorder rate?
There is no universal number: it depends on the product, the season and what customers accept. Track your own trend by week or month, by SKU, and against the fill rate target you have agreed with sales.
Functions used
MAXLargest numberSUMAdds numbersSUMPRODUCTMultiply then addCOUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsROWSNumber of rows in a rangeHSTACKJoins arrays side by sideCOUNTACounts non-empty cellsUNIQUERemoves duplicates, spillsFILTERKeeps matching rows, spillsSUMIFSAdds with several conditionsAVERAGEIFSAverages with several conditionsMAXIFSLargest value that matchesIFPicks a result from a testANDTrue if all tests are trueLETNames values inside a formulaSORTBYSorts by other columns, spills
Related guides
- How to calculate order fill rate in Excel
- How to calculate stockout rate in Excel
- How to calculate days of inventory on hand in Excel
- How to calculate OTIF (on time in full) in Excel
- How to calculate occupancy rate in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to count duplicates in Excel
Updated