Quality dashboard in Excel: defects and rework

A quality dashboard in Excel compares defective units, first-pass yield and rework cost from the same production cohort. Keep the original inspection result when a unit is repaired, and calculate every rate from its underlying counts. The useful dashboard proves where the units went before it ranks production lines.

Define the production population

Start with inspection and production reports for a defined process stage. Record the line, product family, period, units entering that stage and unique units passing the initial attempt. Attach approved disposition totals and recorded rework cost for those same units.

The quality assurance dashboard guide covers the wider review. This workbook template focuses on formulas and reconciliation. Its example is a completed June 2026 cohort at a US industrial component manufacturer, with every unit's disposition resolved by the review cutoff.

Keep two concepts separate. NIST distinguishes defective items from individual defects. A unit can have multiple recorded nonconformities, so counting defect-code rows is not a valid substitute for counting unique failed units.

For this workbook, a first-pass success is a unit accepted at the defined stage without rework or another test. A first-pass failure is a unique unit that does not meet that rule. The example assumes every failed unit is either successfully reworked or scrapped; an unresolved cohort needs an additional open-hold column.

Use this input layout

Create sheets called Inputs, Dashboard and Checks. Use columns A through I on Inputs, with one row per line, product and cohort. The values below are invented operating figures, not an industry benchmark.

Enter only the three detail rows as source inputs; calculate the displayed total separately. If production reports contain both shift details and daily subtotals, choose one level. Copying both into the workbook doubles the inspected population and cost.

For rework cost, this example includes the recorded incremental labor and materials charged to the recovered units. Scrap cost is outside that measure. Label the boundary in the workbook so a later report does not silently add scrap or normal production labor.

Add the dashboard formulas

Defective-unit rate = First-pass failed units / Units entering the process

First-pass yield = First-pass good units / Units entering the process

Rework cost per recovered unit = Recorded rework cost / Units recovered by rework

With the input table beginning in row 2, put these formulas in J2 through L2 and fill down:

=IF(C2>0,E2/C2,NA())
=IF(C2>0,D2/C2,NA())
=IF(F2>0,I2/F2,NA())

Format J and K as percentages and L as dollars. The IF function returns different results according to a logical condition; here a missing denominator stays visible rather than becoming zero cost or perfect quality.

Line B's failure calculation is 48 / 600 = 8.0%. Its rework cost is 1,800 / 36 = $50.00 per recovered unit. The cost per recovered unit is identical across these lines, while first-pass results differ.

For the combined measures, divide sums:

=SUM(Inputs!E2:E4)/SUM(Inputs!C2:C4)
=SUM(Inputs!D2:D4)/SUM(Inputs!C2:C4)
=SUM(Inputs!I2:I4)/SUM(Inputs!F2:F4)

They return 120 / 2,000 = 6.0%, 1,880 / 2,000 = 94.0% and 4,600 / 92 = $50.00. Percentages use one decimal place and dollar averages use two; formulas retain full precision.

Reconcile units and separate the questions

Checks should return zero for both identities below:

=C2-D2-E2
=E2-F2-G2

For line A, 1,000 − 940 − 60 = 0 and 60 − 48 − 12 = 0. Across the company, 1,880 first-pass good units plus 92 recovered units plus 28 scrapped units equals the original 2,000.

Final acceptable yield is therefore (1,880 + 92) / 2,000 = 98.6%. That does not replace first-pass yield of 94.0%. They answer different questions: eventual acceptable output and output achieved without extra work.

Defect events give another result: 152 / 2,000 × 100 = 7.6 defects per 100 entered units. Label it as events per units, not the percentage of units failing inspection. The denominator and unit of measure must remain visible.

For a product view, SUMIFS can sum values matching a product name. With the selected product in Dashboard A2, the valve failure rate is:

=SUMIFS(Inputs!E2:E4,Inputs!B2:B4,A2)/SUMIFS(Inputs!C2:C4,Inputs!B2:B4,A2)

The valve result is (48 + 12) / (600 + 400) = 6.0%. Read lines B and C separately before attributing the product total to either line. Use the quality management report to connect the findings to accountable follow-up; workforce incidents belong in the separate safety dashboard workbook.

Where it goes wrong

• Repeat inspections counted as new units. Rework attempts inflate volume if each attempt is treated as another original unit.
• Open work hidden in scrap. An unresolved hold needs its own disposition. Do not force the reconciliation by changing its status.
• Different cost boundaries. Comparing labor-only costs with labor-and-material costs creates a misleading line ranking.
• Costs from another cohort. Rework posted this month may relate to last month's production. State whether the view follows the production cohort or posting period.
• Mixed process stages. A final assembly acceptance rate and an incoming inspection rate describe different populations.

Covirage is not affiliated with Microsoft.

Check your own production file in Covirage

Bring the aggregate production, inspection and rework figures with their product, line and cohort fields. 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 export.

See Covirage for industrial manufacturers for the wider operating context.