Home » Free Excel Tips » Data Validation

Defect Tracking with Microsoft Excel

How to Setup A Defect Tracking Spreadsheet

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

Solution: Use Excel to mistake-proof defect tracking.

Define What You Want to Track

First, figure out what you want to track and make a series of headings. To keep a log of defects that can be used in a PivotTable it needs a minimum of:

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

Put this in row 11 (we'll use 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

Next, select cells A12:A100 and choose Data-Data Validation. Then set the validation criteria to be Date with a start and end date (cells A1:A2):

Then, for cells B12:B100, C12:C100, D12:D100, select the cell range and choose Data-Data Validation, but this time choose "list" and 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

Then, specify an error message if they 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:

Then, select the first cell in row 12 and enter 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.