Excel guide · 4 min read
How to calculate scrap rate in Excel
=SUM(F2:F10)/SUM(E2:E10), formatted as a percentage. For one line or shift, use SUMIFS on both parts: =SUMIFS(F2:F10,B2:B10,"L1")/SUMIFS(E2:E10,B2:B10,"L1").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 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Line | Shift | Item | Units started | Scrap units | Reason | Scrap cost | Item | Std cost | |
| 2 | Oct 5, 2026 | L1 | Day | WID-A | 1200 | 30 | Setup | 126 | WID-A | 4.2 | |
| 3 | Oct 5, 2026 | L2 | Night | WID-B | 800 | 44 | Material | 345.4 | WID-B | 7.85 | |
| 4 | Oct 12, 2026 | L1 | Night | WID-A | 1150 | 52 | Machine | 218.4 | |||
| 5 | Oct 12, 2026 | L2 | Day | WID-B | 900 | 18 | Setup | 141.3 | |||
| 6 | Oct 19, 2026 | L1 | Day | WID-A | 1250 | 25 | Operator | 105 | |||
| 7 | Oct 26, 2026 | L2 | Night | WID-B | 780 | 61 | Material | 478.85 | |||
| 8 | Nov 2, 2026 | L1 | Day | WID-A | 1180 | 21 | Setup | 88.2 | |||
| 9 | Nov 2, 2026 | L2 | Day | WID-B | 860 | 39 | Material | 306.15 | |||
| 10 | Nov 9, 2026 | L1 | Night | WID-A | 1100 | 48 | Machine | 201.6 |
Download this sheet as an Excel practice file (.xlsx)
The basic scrap rate
Keep a production log with one row per run: date, line, shift, item, units started in E, scrap units in F and the main scrap reason in G. Units started means everything that went into the process, good and bad. If parts are scrapped for being out of tolerance, Cpk tells you whether the process can hold the spec at all.
Overall scrap rate is =SUM(F2:F10)/SUM(E2:E10). Format it as a percentage with Home > Number > Percent Style, then add a decimal with Increase Decimal, since scrap rates live in the low single digits and 4% hides the difference between 3.6% and 4.4%.
Result: 0.036659436009 shows as 3.67% with Percent format
0.0367: 338 scrapped out of 9,220 started, a 3.67% scrap rate.
Use units started as the denominator, not good units
A common shortcut divides scrap by good units, because good units are what the ERP reports as "produced". That overstates the rate and makes it impossible to add up: the denominator shrinks as scrap grows.
If your log only has good units, rebuild started as good + scrap. And don't average the scrap % of each row: =AVERAGE(F2:F10/E2:E10) gives every run the same weight, so a short 780-unit run counts as much as a 1,250-unit one. Always sum the scrap and the units first, then divide.
Result: 0.038054492231 shows as 3.81% with Percent format
0.0381: 3.81% when scrap is divided by good units. The average of the row percentages gives a third answer, 3.89%. The correct rate is 3.67%.
Scrap rate by line, shift and month with SUMIFS
Put the same criteria in both SUMIFS so the scrap and the units cover exactly the same runs. By line: =SUMIFS(F2:F10,B2:B10,"L1")/SUMIFS(E2:E10,B2:B10,"L1") is 2.99% for L1, and the same with "L2" gives 4.85%. By shift, Night runs at 5.35% against 2.47% for Day.
Add a criteria pair to drill down: line L1 on nights alone is 4.44%, so the night problem isn't only an L2 problem. For months, use a date range so the formula keeps working next year: =SUMIFS(F2:F10,A2:A10,">="&DATE(2026,10,1),A2:A10,"<"&DATE(2026,11,1)) for October scrap, and the same criteria on column E for October units. October comes to 3.78%, November so far 3.44%.
In practice, put the line, shift and month in cells and point the criteria at them, so one formula fills a whole summary table.
Result: 0.053524804178 shows as 5.35% with Percent format
0.0535: night shift scraps 5.35% of what it starts, more than double the day shift.
Scrap cost with a standard cost lookup
Units hide the money. A scrapped WID-B costs 7.85 at standard; a WID-A costs 4.20. Keep standard costs in a small table (J:K) and price each row in H2 with =F2*XLOOKUP(D2,$J$2:$J$3,$K$2:$K$3). In Excel 2019 or earlier, use VLOOKUP(D2,$J$2:$K$3,2,FALSE) instead of XLOOKUP. To build a scrap allowance into a product's cost and order quantities, use the free bill of materials template.
Use the standard cost from your costing system, not the selling price. Scrap cost tells you what the scrap used up in material and labour; lost margin is a separate question.
Result: 2010.9
2010.9: total scrap cost at standard. L2's WID-B accounts for 1,271.70 of it even though it scrapped fewer units than L1.
Top scrap reasons, ranked by cost
In Excel 365, list each reason once and sort by cost in one formula: =SORTBY(UNIQUE(G2:G10),SUMIFS(H2:H10,G2:G10,UNIQUE(G2:G10)),-1). The SUMIFS inside gets the cost of each unique reason, and SORTBY orders the reasons by it, largest first.
Rank by cost, not just by count. Material problems are 144 of the 338 scrapped units (42.6%) but 1,130.40 of the cost (56.2%), because they hit the expensive item. Machine (100 units, 420), Setup (69 units, 355.50) and Operator (25 units, 105) follow. In older Excel, use a PivotTable with Reason in Rows and Scrap cost in Values, then sort largest to smallest.
Result: spills 4 rows × 1 column
| Material |
| Machine |
| Setup |
| Operator |
Material, Machine, Setup, Operator: the scrap reasons from most to least expensive.
Common mistakes
Dividing scrap by good units
Divide by units started (good + scrap), so the rate can't exceed 100% and adds up across lines.
Averaging the scrap % of each run
Sum scrap and units separately with SUMIFS, then divide once.
Ranking scrap reasons by units only
Add a cost column with a standard cost lookup and rank by cost too.
Different criteria in the top and bottom SUMIFS
Copy the criteria pairs exactly, or point both at the same criteria cells.
FAQ
What is the formula for scrap rate?
Scrap rate = scrap units / total units started x 100. In Excel: =SUM(F2:F10)/SUM(E2:E10) with the cell formatted as a percentage, so you don't need the x 100.
How do I calculate scrap percentage by month in Excel?
Use SUMIFS with a date range on both scrap and units: =SUMIFS(F:F,A:A,">="&DATE(2026,10,1),A:A,"<"&DATE(2026,11,1)) divided by the same SUMIFS on the units column.
What is the difference between scrap rate and yield?
They're two sides of the same count: if units started is the base, yield is good units / units started and scrap rate is scrap / units started. Rework complicates it; first pass yield counts reworked units as not passing the first time.
How do I calculate the cost of scrap in Excel?
Multiply scrap units by the item's standard cost, looked up from a cost table: =F2*XLOOKUP(D2,$J$2:$J$3,$K$2:$K$3). Then total or SUMIFS the cost column.
Functions used
SUMAdds numbersSUMIFSAdds with several conditionsAVERAGEMean of numbersXLOOKUPModern lookup in any directionVLOOKUPVertical lookupSORTBYSorts by other columns, spillsUNIQUERemoves duplicates, spillsDATEBuilds a date
Related guides
- How to calculate first pass yield (FPY) in Excel
- How to calculate OEE in Excel
- How to calculate product return rate in Excel
- How to calculate percentage of total in Excel
- How to calculate Cp and Cpk in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate contribution margin by product in Excel
Updated