Defect Tracking Template for Excel

Defect Tracking with QI Macros

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

Solution: Use QI Macros Defect Tracking Template for Excel to mistake-proof defect tracking. You'll find it located under Fill-in-the-blank Templates->Improvement Tools.

QI Macros Defect Tracking Template

Define What You Want to Track

First, figure out what you want to track. The defect tracking template has many of the common ones, 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.

The template uses rows 1-10 to establish some data validation.

Define the Data Validation Criteria

Next, we need to make sure that people enter the defect data consistently. To do this, we'll use Excel's data validation function. Enter validation criteria as shown below. Adjust criteria to meet your needs:

Cells A1:A2 contain the valid start/end dates or times for dates in column A.

Rows 1-10 have lists of valid locations and types of defects
- adding items from labels up allows Excel's Autofill function to simplify data entry.
- don't allow an "other" category; it's useless. Make users enter something useful.

Set Data Validation Criteria for Each Set of Input Cells

Cells A12:A100 use Data-Data Validation to ensure proper formats for dates:

Cells B12:B100, C12:C100, D12:D100 use Data Validation, and "lists" to specify cells 1-10 above the heading:

This will give users a choice of valid inputs which will make it easier to summarize using an Excel Pivottable.

Define Warning Message if Data is Incorrect

The templates have an error message if users enter something incorrect. You can choose "stop" or just a warning. A warning will allow them to add new criteria in rows 1-10 which will be useful as new criteria arise:

The first cell in row 12 has an input message. This will give new users some starting help. It may not be useful on every cell in the column:

Now, when data is entered, it will be entered correctly and criteria can be updated as needed. Users have a choice of clicking on the arrow next to a cell to select a value, or simply typing and letting Excel's autofill work it's magic:

To extend data validation to more than 100 rows, just select row 100-110+, one column at a time, and Data Validation will ask if you want to extend the validation. Click Yes.

This will ensure data is entered and formatted correctly:

So, there a simple system for tracking defects in a consistent manner for later analysis.

When you are done collecting data:

  • use the QI Macros Pivottable wizard to summarize the data.
  • select the row and column totals from the pivottable and run control charts and pareto charts.

Learn More...

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