Defect Log Template

About this template

When to use it

One defect per row from the moment it is found to the moment it is verified, rejected or closed. Three sheets — Dashboard, Input data, Usage guide — and only Input data is typed into. The Defect ID column is the value that ties this sheet to the test case sheet, so agree the numbering before anyone starts logging.

What the dashboard holds

Key metrics in B7:E7 — rows entered, Total days to close, Average days to close, Max days to close — read column H of Input data. Summary by status in B12:F17 counts column G against the six status labels. The New defects per week table (B21:C24) is typed in by hand.

Filling order

1. Rows 2–21 of Input data take Defect ID, Date found, Stage found, Module, Defect summary, Severity, Status, Days to close, Owner (A–I). Eight sample defects ship, DF-2026-014 to DF-2026-021.
2. The status labels on the Dashboard are New, Analyzing, Fixing, Fixed, Verified, Rejected (B12:B17) and the formulas match them exactly.
3. Fill Days to close only after verification is finished, counting from the date found (note 6). A defect that is still open stays blank, or the average is inflated by work that has not happened yet.
4. Severity in column F runs Critical, High, Medium, Low. It is captured but never summarized, so add your own COUNTIF row if you triage on it.
5. Replace the four weekly counts (Jun W1–W4: 14 · 21 · 17 · 11) with your own.

How the formulas calculate

• =COUNT(‘Input data’!$H$2:$H$21) counts only closed defects — four of the eight shipped rows.
• The status block is =COUNTIF(‘Input data’!$G$2:$G$21,$B12), =SUMIF(‘Input data’!$G$2:$G$21,$B12,’Input data’!$H$2:$H$21) and the matching AVERAGEIF.
• The share column is a share of defects, not of closed defects. It is =IFERROR(C12/COUNTA('Input data'!$G$2:$G$21),0) — the status count over every defect that has a status, so the six shares add up to 100% while defects are still open. Open defects have no days-to-close value, so they are absent from the day totals but present in the share.
• Under the weekly table, =SUM(C21:C24) and =AVERAGE(C21:C24) — 63 and 15.75 as shipped.
• Severity has no block of its own; =COUNTIF(‘Input data’!$F$2:$F$21,”Critical”) is the one line that gives you it.
• The ranges stop at row 21; widen $G$21 and $H$21 before the log passes twenty defects.

Keeping it aligned with the test cases

Note 7 is the rule that makes the pair work: the value in Defect ID here must be the same string that appears in the Linked defect ID column of the test case sheet. DF-2026-014, 015 and 016 in the shipped rows are exactly the three IDs the test cases point at, and each one describes the same failure from the other side — the test case says the account was not locked after five wrong passwords, this log says the same and adds the module, severity and owner. Note 8 adds the other half: do not delete rejected defects. DF-2026-019 stays in the log with “rejected as intended design” appended to its summary, which is what stops the same report coming back next cycle.

Examples

• Row 6 — DF-2026-015 · 2026-06-16 · Integration test · Orders · Out-of-stock item goes through to payment · Critical · Verified · 3 days · Lee Su-min.
• The four closed defects took 2, 3, 4 and 5 days, so Total days to close reads 14, the average cell shows 4 (3.5 before rounding) and the max 5.
• The four open rows — New, Analyzing, Fixing and Rejected — have an empty Days to close, which is what keeps that average honest.
• Three of the eight are Critical, all of them in Payment or Orders.

Common mistakes

• Writing a status as “In progress” or “Closed”, which no summary row counts.
• Entering 0 days for a defect that is still open, which pulls the average down.
• Deleting rejected or duplicate defects, so the same report is filed again a month later.
• Letting the ID here drift from the one recorded on the test case sheet.