Defect Tracking Template for Excel

Mistake-proof data collection with this easy to use template.

While most companies do some tracking of their defects, mistakes and errors, the data is usually too inconsistent for easy analysis.

That is why we created QI Macros Defect Tracking Template. It lets you set data validation criteria and mistake-proofs the collection process. You'll find it located under Lean Six Sigma Templates -> Improvement Tools.

defect tracking template explained

Step1: Define What You Want to Track

First, figure out what you want to track. The defect tracking template has many of the common ones (row 11), but you can tailor them to your needs. To keep a log of defects that can be used in a PivotTable it needs a minimum of:

  1. A date or time - When did it happen?
  2. Location - Where did it happen?
  3. Type - What was the defect, mistake or error?
  4. Cause - Why did the error occur?
  5. Description - Free form description that can be analyzed with QI Macros Word Count.

Step 2: Define What Constitutes Valid Data

The whole purpose of this template is to make sure that people enter defect data consistently. Without consistent data, analysis tools like PivotTables and Pareto charts cannot be used.

The template uses rows 1-10 and Excel's data validation function to define the validation criteria.

  • Cells A1:A2 contain the valid start/end dates or times for any dates input in A12 and below.
  • Cells B1:D10 contain lists of data inputs that are considered "valid" for each column. For example, there are only three valid "causes" (machine drift, setup, and excess glue). If a fourth cause is identified, add it to cell D7.
  • Cells E1:E10 are blank because there is no validation criteria for this column.

Validation Criteria Tips:

  • Adding items from labels up allows Excel's Auto-fill function to simplify data entry.
  • Don't allow an "other" category; it's useless. Make users enter something useful.

defect tracking template

Step 3: Input Your Data Using the Validation Menus

When you click on a cell you will see an arrow to the right.

validation arrow

Click on the arrow to view the list of valid entries, then select the appropriate entry from the list.

input list

Completed data input will look like this and be consistent.

completed input

If you input data that is not valid, you will receive an error message. If you select, yes, the invalid field will be added. Therefore, it is best to select "Cancel". If the new item is a valid entry, then add it to the criteria in rows1-10 first.

error message.

When you are done collecting data use:

Create these charts and diagrams in just seconds using QI Macros for Excel...