How to make a software test report using Excel

Key points of this article

The table in the test report should be split into at least two test case results tables and defect details tables, with fixed fields to facilitate subsequent statistics.
The values for status, severity, and priority must be agreed upon in advance to avoid the same problem being written in several ways.
The defect table should be able to correspond to the use case number, and the summary figures should be calculated using formulas, rather than copied by hand.
After the form is completed, check whether the number, status and summary number are consistent with the original record before putting it into the report

When writing a software testing report, most people get stuck on the form. The text part can be written according to the template, but the test results have dozens of use cases and more than a dozen defects. Manual typesetting in Word will quickly become messy: the numbers do not match, the status of the same defect is inconsistent in two places, and the final pass rate has to be counted again. This article is written for test engineers, developers and project leaders who need to organize test reports by themselves. It explains how to use Excel to create test results tables and defect tables.

After reading, you can get three things: the field design of the two tables, the writing method of the value agreement, and a set of checklists before putting it into the report. The operations in this article are also applicable to the UniCell table in the OmniDoc desktop client. The steps are the same, except that the tool that is opened is changed.

How to create an Excel table for software testing reports? One sentence conclusion

A common approach is to split the data into two tables: one records the execution results of each test case, and one records the details of each defect. The two tables are related by number. The statistics quoted in the main text of the report are calculated from these two tables. Below are suggestions for common situations.

your situation	Recommended practices	reason
Fewer than 20 use cases and fewer defects	Just two worksheets in the same workbook	Simple structure and low maintenance cost
There are many use cases and need to be collected by module.	Add the "Module to which" field is added to the result table and summarize by module using formulas	Reports often write conclusions according to modules, and manual calculations are easy to miss.
The same defect appears repeatedly in multiple rounds of testing	The defect table retains the number separately, and the result table only references the number.	Avoid filling in the same question repeatedly in multiple places and causing inconsistent status
The report must be reviewed by multiple people	Add "Responsible Person" and "Updated Date" fields to the table	During the review, you can quickly see who is following up and whether the data is updated to the current time.
Table data also needs to be transferred to Word report	After the form is completed, copy it into the report as a whole and check the format.	The report is mainly based on Word, and the table is only used as the data source.

Before you start: Confirm these points first

Decide on the following things before you start, and you will have much less work to do later.

Confirm test scope and rounds. Which version and which round of testing the report is written about determines whether the results table should be differentiated between rounds. The pass rate loses its meaning when the rounds are mixed together.
Determine the use case numbering rule. The number should not be repeated in the entire project. For example, the serial number should be added according to the module, and the defect number should be the same. Once the number is changed at will, the connection between the defect and the use case will be broken.
Agreed value range. The status (such as not executed, passed, failed, blocked), severity level, and priority should be written into a fixed vocabulary first. The values are not uniform, and "failed" and "failed" will be regarded as two results during statistics.
Make it clear who will fill it out and when. The results form is usually filled out by the executive, the defect form is filled out by the submitter, and the summary is completed by the person in charge. If responsibilities are clearly defined, the form will not remain in a semi-finished state for a long time.
Have original records ready. Use case execution records and defect tracking export results should be prepared before building the table. Filling in data from memory is a common cause of reporting errors.

Steps to build test result table and defect table in UniCell

The following steps use the UniCell table in the OmniDoc desktop client as an example. If you are used to using Excel from other desktop office software, the operation idea is the same, just copy the corresponding fields and formulas.

Install the OmniDoc desktop client on your computer (download link: download.omnidoc.top), open it, create a new UniCell spreadsheet file, and name it "Test Report Data for a Certain Version".
Name the default worksheet as "Test Case Results", and write the fields in the first row in order: Case ID, Module Affiliated, Case Description, Execution Turn, Execution Result, Executor, Execution Date, Associated Defect Number, Remarks.
Create a second worksheet called "Defect Details". The first line enters the fields: defect number, title, module, severity level, priority, current status, discovery turn, submitter, fixer, and remarks.
Above the third worksheet or result table, write the value conventions. For example, execution results can only be filled with "Pass," "Fail," "Blocked," or "Not Executed." If the table tool supports dropdown lists, set it directly; If not supported, consistency is ensured through agreements and subsequent checks.
Enter data item by entry according to the original records. When entering, the associated defect numbers of the use cases must exactly match the numbers in the defect table, and not be written as "BUG-12" or "bug12".
In the summary area, use formulas to count the number of passes, failures, blockages, and unexecuted entries, and calculate the completion rate. The formula results change with the data; if you change a record, the summary is automatically updated, and the numbers in the report will never become outdated.
After completion, copy the table area you want to enter into the report body into the Word document used for the report body, adjust column width and font to ensure the header is clearly visible on every page.
Save the file and write the version number and date in the file name or the first line of the table to facilitate the use of the same structure in the next round of testing.

After completion, check each item one by one

Before putting the form into the report, check the following points item by item. This step can detect most basic errors.

Inspection items	How to see it	What should you do if there's a problem?
Whether use case numbers are duplicated	Sort the numbered columns to see if adjacent rows are duplicated	Find duplicates, return to the original use case library to confirm, and modify them
Can the defect numbers match?	Filter rows where the "Associated Defect Number" is not empty and look them up one by one in the defect table	If the number cannot be found, add the defect record or delete the incorrect reference
Check whether the values are consistent	Filter by execution result and severity level column to see if there are any unspecified usages	Uniformly replaced with the term from the agreed vocabulary
Check whether the sum is correct	Manually count the number of passes and failures in a given module and compare it with the formula results	Check whether the statistical range of the formula has missed any newly added rows
Check if the defect status is the current value	Compare with the current status of the defect tracking system and check each item in the "Current Status" column	Update the table based on the newly exported records, and indicate the update date
The tables in the report are consistent with the data sources	Compare the table in Word line by line with the table file	After data changes, duplicate it again; don't just change one number in the report

If a large amount of data is found during verification that needs to be transferred from other files, such as scanned paper records or PDF files, it is recommended to first test by selecting text to confirm whether the text can be directly copied. If it cannot be selected, OCR recognition is used first, then the recognition results are checked item by item. OCR recognition can have errors, so serial numbers and numbers must be checked carefully.

❓ Frequently Asked Questions (FAQ)

1. What forms are generally required for software test reports?

Common examples include the test scope and environment table, test case execution result table, defect detail table, and test conclusion summary table. Small projects can include environmental information in the report body, but both the result table and the defect table are basically necessary.

2. How should defect numbers be compiled?

It is recommended to add numbers by item, for example, "Project Abbreviation - Year - Three-digit Number" to ensure no duplication and easy to find. Closed defect numbers should not be reused, even if they are deemed invalid.

3. Should you write test reports in Excel or Word?

Data content is maintained using table tools, while the main text and conclusion are written in Word, with clear division of labor between the two. When table data changes, simply update the data source and copy the required parts into the report.

4. How do you define the status and severity level in the table so things don't get confusing?

At the start of the project, write a value description listing the meaning of each state and level. For example, "failure" means the actual result does not match expectations, and "blocked" means execution cannot continue. Everyone fills out the same instructions to ensure comparability when statistically comparing.

📝 Summary

The key to software test report spreadsheets lies in breaking down structures, unifying fields, and ensuring data correlations. First, build the test case result table and defect detail table, agree on values, then summarize using formulas, and finally check according to the list before entering the report. Following this process, the same data can remain consistent even after multiple modifications.

Open OmniDoc and process your table file