Supplier Defect Rate %:
Find Your Worst Vendors Fast

How to Build a Supplier Defect Rate % Matrix in Excel

1. Create a unique list of part categories

• Start by identifying the key fields in your dataset: Part Category, Quantity Received, Quantity Defective, and Supplier.
• Use the UNIQUE function to generate a list of part categories.
• Wrap it with the SORT function if you want the categories in alphabetical order.
• Copy the results and paste them as values using Ctrl + Shift + V.

This gives you the row labels for the matrix.

2. Create a unique list of suppliers

• Use the UNIQUE function again, this time on the Supplier field.
• Use the table name directly if your data is already formatted as an Excel table. That makes it easier to reference columns without going back to the data sheet.
• Wrap it with SORT to alphabetize the supplier list.
• Use the TRANSPOSE function to place the supplier names across the top of the matrix.
• Copy and paste the final list as values.

This creates the column labels for the matrix.

3. Build the numerator using defective quantity

• In the body of the matrix, calculate the defective quantity by:
  • Part Category
  • Supplier
• Use SUMIFS to total the defective units for each category and supplier combination.
• The first range should be the defective quantity field.
• The criteria should match:
  • the part category on the left
  • the supplier across the top

This gives you the top half of the calculation for each cell.

4. Build the denominator using quantity received

• Copy the same structure from the numerator.
• Use SUMIFS again, but switch the summed field to Quantity Received.
• Keep the same two criteria:
  • Part Category
  • Supplier
• Format the result as a percentage using Ctrl + Shift + 5.

Now each cell shows the defect rate for that supplier and part category combination.

5. Fix the references before copying across the matrix

• Before dragging the formula across and down, adjust the references so they stay locked correctly.
• Use absolute and mixed references so:
  • the part category stays fixed by row
  • the supplier stays fixed by column
• This prevents the matrix from shifting to the wrong headers when copied.

Once that is done, copy the formula and paste it across the full grid.

6. Replace divide-by-zero errors

• Some cells will return errors where no units were received for that supplier and part combination.
• Wrap the calculation with IFERROR.
• Replace the error with a dash or another placeholder value instead of showing an Excel error.

This makes the matrix easier to read and present.

7. Add overall supplier totals

• Create an Overall row to show each supplier’s total defect rate across all part categories.
• Copy the existing formula structure and remove the part category criteria.
• Keep only the supplier criteria in place.
• Paste the formula across the row and format it as a percentage.

This shows the overall defect rate by supplier, regardless of part type.

8. Add overall part category totals

• Create an Overall column to show each part category’s total defect rate across all suppliers.
• Copy the same formula structure again.
• This time, remove the supplier criteria and keep only the part category criteria.
• Copy the formula down the column and format it as a percentage.

This gives you the overall defect rate by part category, regardless of supplier.

9. Highlight problem areas with conditional formatting

• Select the matrix and the overall totals.
• Hold Ctrl if you want to highlight multiple ranges at once.
• Go to Conditional Formatting and apply a Greater Than rule.
• Point the rule to a target cell that stores your threshold, such as 1.5%.

This creates a heat-map style view so high-risk supplier and part combinations stand out immediately.

10. Create a dynamic target cell for the formatting rule

• If you want the threshold to be easy for someone else to adjust, store it in a dedicated target cell.
• Keep the cell as a number, not text.
• Use Format Cells with a Custom number format so the cell displays something like “Target: 1.0%” while still behaving like a numeric value.

That way, changing the target updates the formatting without breaking the rule.

11. Finalize the matrix for review and sharing

• Center-align the percentages for readability.
• Make sure your headers are clearly labeled.
• Confirm that the matrix, overall row, and overall column all use the same percent formatting.
• Test the target cell by changing the threshold and confirming that the highlights update correctly.

This leaves you with a clean supplier defect-rate matrix that can be shared with operations, purchasing, or inventory teams for vendor review.