Want a Defect Tracking Template for Excel?
Mistake-proof data collection with this ready-made template in QI Macros.
Why Data Collection Matters:
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.
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:
- 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.
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.
Step 3: Input Your Data Using the Validation Menus
When you click on a cell you will see an arrow to the right.
Click on the arrow to view the list of valid entries, then select the appropriate entry from the list.
Completed data input will look like this and be consistent.
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.
.
When you are done collecting data use:
- QI Macros Improvement Project Wizard to create control charts and Pareto charts.
- QI Macros PivotTable Wizard to create a PivotTable.
- QI Macros Word Count Wizard to summarize text descriptions input in column E.
Haven't you waited long enough?
Start creating your Defect Tracking in just minutes.
Download a free 30-day trial. Get the Defect Tracking now!
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.
