Test Case Management Sheet

About this template

When to use it

One thing you intend to verify per row — what you do, what should happen, what did happen, and the defect it raised. Three sheets — Dashboard, Input data, Usage guide — and only Input data is typed into. The last column is deliberately left to point at the defect log, so the two sheets are used as a pair.

What the dashboard holds

Key metrics in B7:E7 — rows entered, Total time (min), Average time (min), Max time (min) — read column H of Input data. Summary by result in B12:F16 counts column F against the five result labels. The Cases run per cycle table (B20:C23) is typed in by hand.

Filling order

1. Rows 2–21 of Input data take TC No., Test case, Feature area, Preconditions, Expected result, Result, Date run, Time taken (min), Linked defect ID (A–I). Eight sample cases ship across Login, Orders, Payment, My account and Admin.
2. Before a run, set Result to Not run and leave Date run and Time taken blank. Do not enter 0 (note 8) — a zero drags the average time down.
3. After a run, record the result and, for a Fail or a Blocked, raise the defect first and put its ID in column I (note 7).
4. The result labels on the Dashboard are Pass, Fail, Blocked, Not run, On hold (B12:B16) and the formulas match them exactly.
5. Replace the four cycle counts (1st, 2nd, 3rd, Regression: 46 · 58 · 72 · 39) with your own.

Blocked and Fail are not the same

Note 6 draws the line and it is worth holding to. Blocked means an upstream feature stopped you checking at all; Fail means you did check and the behavior differed from the expected result. The shipped TC-PAY-01 is Blocked because no authorization response came back, so card payment was never actually observed — counting it as a failure would blame the wrong component, and counting it as a pass would hide it. Mixing the two distorts the pass rate in whichever direction happens to flatter the build.

How the formulas calculate

• =COUNT(‘Input data’!$H$2:$H$21) counts only cases that were actually timed — six of the eight shipped rows.
• The result block is =COUNTIF(‘Input data’!$F$2:$F$21,$B12), =SUMIF(‘Input data’!$F$2:$F$21,$B12,’Input data’!$H$2:$H$21) and the matching AVERAGEIF.
• The share column is a share of cases, not of runs. It is =IFERROR(C12/COUNTA('Input data'!$F$2:$F$21),0) — the status count over every case that has a status, so the five shares add up to 100% whether or not a case has been timed yet. Untimed rows still count toward the share; they are simply absent from the time totals above.
• Under the cycle table, =SUM(C20:C23) and =AVERAGE(C20:C23) — 215 and 53.75 as shipped.
• There is no pass rate cell. =IFERROR(C12/(C12+C13),0) gives Pass over Pass plus Fail, which reads 60.0% here.

Examples

• Row 3 — TC-LOGIN-02 · Enter the wrong password 5 times · Login · 1 fully registered account · Account locked for 10 minutes, with a message · Fail · 2026-06-15 · 12 min · DF-2026-014.
• The six timed runs total 53 minutes, so Average time (min) shows 9 — 8.8 before the cell’s rounding — and Max time reads 15.
• TC-PAY-02 (Not run) and TC-ADMIN-01 (On hold) have empty Date run and Time taken cells, which is exactly why the average covers the six executed cases only.
• Three defect IDs — DF-2026-014, 015 and 016 — tie this sheet to the defect log.

Common mistakes

• Packing two or three checks into one row, so a single result cannot say which one broke.
• Leaving Preconditions empty. Most “cannot reproduce” replies start here.
• Overwriting a Fail with Pass after the fix. Change the result but keep the defect ID in column I.
• Writing “Success”, “NG” or “Skip” in column F, none of which any summary row counts.