How to Build a Requirements Traceability Matrix in Excel
A requirements traceability matrix shows which test cases cover which requirements, and whether they passed. Build one in Excel for an online shop: count the tests per requirement, set a status with a formula, and colour the gaps and failures.
Try it yourself
Copy this table into a blank sheet, then follow the steps below.
Paste at cell A1:
| Req ID | Requirement | TC-01 | TC-02 | TC-03 | TC-04 | TC-05 | Tests | Status |
|---|---|---|---|---|---|---|---|---|
| R-01 | Search for a product | Pass | ||||||
| R-02 | Add item to cart | Pass | ||||||
| R-03 | Pay by card | Pass | Fail | |||||
| R-04 | Get email receipt | |||||||
| R-05 | Track an order | |||||||
| R-06 | Use a discount code | Pass |
Step by step
- Traceability matrix. A requirements traceability matrix shows which tests cover each requirement and whether they passed, all on one sheet, so nothing slips through. Here is one built in Excel.
- The matrix. The example is an online shop, in A1:I7. Each row is a requirement and columns C to G are test cases. The last 2 columns, Tests (H) and Status (I), will be filled with formulas.
- Where they meet. Each cell is where a requirement meets a test. Test case 4 checks card payments and it failed, so F4 says Fail.
- Link a test. Test case 5 checks the receipt email, so it covers requirement 4. Type Pass in G5, where they meet.
G5
Pass - Count the tests. To count the tests for each requirement, type =COUNTA(C2:G2) in H2. COUNTA counts the cells that are not empty, so requirement 1 shows 1. Drag the fill handle down from H2 to H7. Requirement 3 shows 2 tests.
H2
=COUNTA(C2:G2) - Find the gap. Requirement 5, track an order, shows 0 in H6. No test covers it. That gap is the most important thing the matrix finds.
- Set the status. In I2, the status formula checks 2 things. If the count in H2 is 0, it shows Gap. If any cell in C2:G2 says Fail, it shows Failing. Otherwise it shows Passed. Fill it down to I7. Requirement 3 is Failing, requirement 5 is a Gap and the rest show Passed.
I2
=IF(H2=0,"Gap",IF(COUNTIF(C2:G2,"Fail")>0,"Failing","Passed")) - Colour the results. Select C2:I7, then go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Type Pass and choose Green Fill with Dark Green Text. Passed in the status column turns green too. Add a second Text that Contains rule on the same range for Fail, with Light Red Fill with Dark Red Text. Failing contains Fail, so the status column is caught as well.
- Colour the gaps. Then select only the status column, I2:I7, and add an Equal To rule (also under Highlight Cells Rules) for Gap. In the format list, choose Custom Format, then set a yellow fill and bold text. Now the gap stands out.
- Bold the count. Select H2:H7 and make it bold, so the number of tests stands out next to each requirement.
- Make it a table. Last, select A1:I7 and use Home > Format as Table, with My table has headers ticked. Name it Traceability in the Table Name box. The filter buttons let you show only the Gap or Failing rows.
- It updates itself. The matrix stays live. When the card payment bug is fixed and retested, type Pass in F4. Requirement 3 changes to Passed by itself, and its colour changes with it.
F4
Pass - 3 matrix rules. 3 rules: every requirement needs at least 1 test, let formulas set the status instead of typing it, and chase the gaps first.
Common mistakes
- Typing the status by hand. A typed Passed stays Passed even when a retest fails. Let the IF formula in column I work out the status from the results.
- Results spelled in different ways. COUNTIF(C2:G2,"Fail") only matches cells that say exactly Fail (in any case), so Failed or Fail with an extra space counts as no failure and the row shows Passed. A Data Validation drop-down list with Pass and Fail keeps everyone typing the same words.
- Cells that look empty but aren't. COUNTA counts a cell that holds only a space, so a blank-looking cell can hide a gap. Clear cells with the Delete key instead of typing over them with a space.
- Counting the wrong columns. If the COUNTA range includes the requirement columns, like A2:G2, every row counts at least 1 and no gap ever shows. Count only the test case columns, C2:G2.
When a business analyst uses this
Before UAT sign-off, a business analyst filters the Traceability table to Gap and Failing and takes that short list to the sponsor: requirement 5 still needs a test case, and requirement 3 needs a retest after the card payment fix. It shows what is blocking sign-off without anyone reading every row.
Questions people ask
What is a requirements traceability matrix, with an example?
It's a grid that links each requirement to the tests that check it, with each result. For example, in an online shop, requirement 4 (the receipt email) is covered by test case 5, which passed.
What is the purpose of a requirements traceability matrix?
It shows whether every requirement has at least 1 test and whether those tests passed. Its main job is to find gaps, which are requirements that no test covers, before sign-off.
How do you create a requirements traceability matrix in Excel?
Put requirements in rows and test cases in columns, type Pass or Fail where they meet, then add a COUNTA count and an IF status formula. Conditional Formatting and Format as Table make the gaps and failures easy to find.
