Home » Free Excel Tips » Defect Tracking

Need to Mistake-Proof Data Input in Excel but Don't Know How?

Learn about Excel's data validation features and QI Macros ready-made defect tracking template.

Save Time Using QI Macros Defect Tracking Template

Accurate and consistent data is key to running pivottables to analyze your data. That's why we created a defect tracking template for Excel. It's part of QI Macros add in which includes more than 100 time saving templates and advanced charts. Download a 30 day trial.

If you want to set up a template yourself follow the instructions below.

How to Setup A Defect Tracking Spreadsheet Yourself

While most companies do some tracking of their defects, mistakes and errors, the data is usually too inconsistent for easy analysis. Luckily, Excel has some helpful features to mistake-proof data input.

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:

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.

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:

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.

The Defect Tracking is one of many tools included in QI Macros add-in for Excel.

QI Macros adds a new tab to Excel's menu, making it easy to find any tool you need. If you can't locate a tool, use the find tools feature on the far right side of QI Macros menu.

Other Charts Included in QI Macros for Excel