I’ve used the template described in this post when I don’t have a test tool available on the project. Many tools (such as HP/Mercury’s QualityCenter or Seapine’s TCM) will provide this functionality. If you don’t have access to a tool, though – this template may help. The template is designed to help you define your test case coverage, plan for test iterations, and track test case status per iteration.

Template Links

Go ahead and grab the template and I’ll describe the worksheets below. You can get the template in an OpenOffice or Excel version.

The Template Explained

You’ll want to blank out the sample data before you use the template, but it may be helpful to leave it there while I walk through each worksheet.

Test Case List

This worksheet is used to plan the creation of your test cases. It starts with test scenarios, which are just ways to group related test cases together (if you don’t group your test cases, just ignore these 2 columns). Then list out each test case with a description. Columns F, G, and H are for tracking purposes and shows the status of each test case as it is being produced, the person authoring the test case, and the approver(s). Columns J and K are for traceability and allow you to ensure that every use case and functional requirement has at least one corresponding test case (you could easily extend the spreadsheet if you need to trace other items). Columns M and N are placeholders for other classifications you may want to apply.

Test Case Coverage Plan

This worksheet is used to plan test case coverage across planned test iterations. This worksheet (and the next five) are setup for 5 test iterations. This is just a number I picked to demonstrate the tool – you may need more, or may need less than 5 iterations (you can extend or shrink the template as necessary). Determine which test cases will get executed in each test iteration and make the appropriate selections in columns C through G – you can also assign a lead tester.

Test Iteration

In each of the test iteration worksheets, you mark information about each test case as it is executed. The template tracks tester, status, identified defects, and blocking defects (a blocking defect is an issue that prevents the test case from being executed). Column C will be brought forward based on entries you make on the Test Case Coverage Plan worksheet.

Stats

This worksheet summarizes the Test Iteration worksheets in one table that can be reported against. Don’t make entries on this worksheet – all data is brought forward from other worksheets. Please note that if you add rows to the Test Case List, you’ll need to add rows to this worksheet as well (along with each of the Test Iteration worksheets). A nice touch to add to the template would be some macros that add rows simultaneously to each worksheet – perhaps I’ll include that on the next version.

Pivot Tables

This worksheet includes a few pivot tables that would be helpful in creating regular statuses. The Excel version of the template has a sample pivot chart as well.