Excel guide · 5 min read
How to calculate order fill rate in Excel
=SUM(D2:D11)/SUM(C2:C11), but cap over-shipped lines first. On the sample data the three rates are 85.1%, 80% and 60%, 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 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Ordered | Shipped | Line complete | Order complete | SKU | Unit fill rate | |
| 2 | SO-101 | SKU-A | 20 | 20 | TRUE | TRUE | SKU-A | 1 | |
| 3 | SO-101 | SKU-C | 10 | 10 | TRUE | TRUE | SKU-B | 0.56756756757 | |
| 4 | SO-102 | SKU-B | 50 | 42 | FALSE | FALSE | SKU-C | 1.0689655172 | |
| 5 | SO-102 | SKU-A | 12 | 12 | TRUE | FALSE | SKU-D | 1 | |
| 6 | SO-103 | SKU-C | 8 | 8 | TRUE | TRUE | |||
| 7 | SO-104 | SKU-A | 30 | 30 | TRUE | FALSE | |||
| 8 | SO-104 | SKU-B | 24 | 0 | FALSE | FALSE | |||
| 9 | SO-104 | SKU-D | 6 | 6 | TRUE | FALSE | |||
| 10 | SO-105 | SKU-D | 15 | 15 | TRUE | TRUE | |||
| 11 | SO-105 | SKU-C | 40 | 44 | TRUE | TRUE |
Download this sheet as an Excel practice file (.xlsx)
Which fill rate to report
Fill rate measures how much of what customers ordered you shipped on the first go, from stock. The same shipments give three different answers depending on what you count, and the stricter the measure, the lower it gets:
- Unit fill rate: share of units shipped. Good for planners watching stock levels, but a big order of cheap items can hide many small misses.
- Line fill rate: share of order lines shipped in full. One short SKU on an order counts as one miss, whatever the quantity.
- Order fill rate: share of orders shipped complete. This is what the customer feels: one missing line means a partial delivery.
Set up the order-lines table
Use one row per order line, as most order systems export it: order ID in A, SKU in B, units ordered in C and units shipped in the first shipment in D. Ten lines across five orders are enough to see the three rates split apart.
Column E marks a line complete with =D2>=C2, which returns TRUE or FALSE. Fill it down. SO-102 shipped 42 of 50 units of SKU-B, and SO-104 shipped none of its 24 units of SKU-B, so E4 and E8 are FALSE.
Result: 0.8 shows as 80% with Percent format
0.8, line fill rate 80%: 8 of 10 lines shipped in full.
Unit fill rate, and the over-shipment trap
The quick version divides total units shipped by total units ordered: =SUM(D2:D11)/SUM(C2:C11) gives 87.0%. But SO-105 ordered 40 of SKU-C and the warehouse sent 44. Those 4 extra units don't make up for the 32 that were short elsewhere, yet the quick formula counts them.
Cap each line at the quantity ordered before adding up. IF compares the two columns row by row and takes the smaller value. MIN won't do this: MIN(C2:C11,D2:D11) returns the single smallest number in both ranges, not one per row.
Result: 0.8511627907 shows as 85.1% with Percent format
0.851, unit fill rate 85.1%: 183 of 215 units ordered were shipped, counting no line above its order quantity.
Does this work in older Excel?
In Excel 365 and 2021 it works as typed, and SUM in place of SUMPRODUCT does too. In Excel 2019 and earlier, an IF over ranges needs Ctrl+Shift+Enter even inside SUMPRODUCT. This version caps the lines without IF, so a plain Enter is enough in any version.
Result: 0.8511627907 shows as 85.1% with Percent format
0.851 again, 85.1%: on over-shipped lines it subtracts the extra units, so they count as the ordered quantity.
Order fill rate: is every line of the order complete?
An order is complete only when none of its lines is short. In F2, =COUNTIFS($A$2:$A$11,A2,$E$2:$E$11,FALSE)=0 counts the incomplete lines with the same order ID and returns TRUE when there are none. Fill it down: both lines of SO-104 show FALSE, because its SKU-B line shipped nothing, even though its other two lines were fine.
Because each order appears on several rows, you can't just count the TRUEs. In Excel 365, take the unique order IDs that are complete and divide by all unique order IDs.
Result: 0.6 shows as 60% with Percent format
0.6, order fill rate 60%: SO-101, SO-103 and SO-105 shipped complete, SO-102 and SO-104 didn't.
How do I do this without UNIQUE in older Excel?
Weight each line by 1 / the number of lines in its order, so every order adds up to exactly 1. COUNTIF(A2:A11,A2:A11) gives each line's order size.
Result: 0.6 shows as 60% with Percent format
0.6 again: 3 complete orders out of 5, in any Excel version.
Fill rate by SKU to find the stock-outs
The headline rate tells you there's a problem; the SKU breakdown tells you where. List the SKUs in H2:H5 and divide shipped by ordered for each with SUMIFS. Fill it down.
SKU-B is the stock-out: 42 of 74 units shipped. SKU-C shows 106.9% because of the over-shipped line, which is another reason to cap shipped at ordered before reporting by SKU. For the SKUs that keep missing, check the safety stock and reorder point and the weeks of supply on hand.
Result: 0.56756756757 shows as 56.8% with Percent format
0.568, 56.8% for SKU-B: 42 units shipped against 74 ordered across two orders.
Should backorders shipped later count as filled?
Not for fill rate. It measures what left with the first shipment, so record that quantity in column D, not the running total after backorders ship. If your export only has total shipped, the rate drifts up toward 100% as backorders clear, and last month's figure keeps changing. Track the delay separately, for example as on-time delivery against a target, the way SLA compliance does. To track what is still owed and how long it waits, use the backorder rate instead.
Common mistakes
Reporting "fill rate" without saying which one
Unit, line and order fill rates differ a lot (85.1%, 80% and 60% here). Label the measure on every report.
Letting over-shipments raise the rate
Cap each line at the ordered quantity with IF before adding up shipped units.
Counting TRUE rows for the order fill rate
Orders repeat on several lines. Count unique order IDs, or weight each line by 1 / lines in its order.
Using shipped quantities that include later backorders
Use the first-shipment quantity so the rate measures availability, and track delays separately.
FAQ
What is the formula for fill rate?
Unit fill rate is units shipped / units ordered. Line fill rate is complete lines / all lines, and order fill rate is complete orders / all orders.
What is the difference between line fill rate and order fill rate?
Line fill rate counts each order line separately. Order fill rate only counts an order as filled when every one of its lines shipped in full, so it's always the same or lower.
How do I calculate fill rate by customer?
Add a customer column and use the same SUMIFS pattern with the customer as the criterion, or add the customer as a second criterion in COUNTIFS for line fill rate.
Is fill rate the same as service level?
Not quite. Fill rate measures how much demand was met from stock. Cycle service level, used in safety stock formulas, is the chance of not running out during a replenishment cycle.
Functions used
SUMAdds numbersSUMPRODUCTMultiply then addIFPicks a result from a testCOUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsROWSNumber of rows in a rangeUNIQUERemoves duplicates, spillsFILTERKeeps matching rows, spillsCOUNTACounts non-empty cellsSUMIFSAdds with several conditions
Related guides
- How to calculate backorder rate in Excel
- How to calculate safety stock and reorder point in Excel
- How to calculate weeks of supply in Excel
- How to calculate SLA compliance in Excel
- How to calculate inventory record accuracy in Excel
- How to convert TRUE and FALSE to 1 and 0 in Excel
- How to calculate labor cost percentage in Excel
Updated