Rafiul Haq
Test cases refer to determining whether all features of a specific website or piece of software are operating as intended. In this article, you will find three quick-and-easy steps on how to write test cases in an Excel sheet. We will be using Microsoft 365 to do this; however, you can use any version of Microsoft Excel and follow this tutorial.
Download Practice Workbook
You can download the Excel file from the link below.
Step-by-Step Procedures to Write Test Cases in Excel Sheet
There are three easy steps to prepare the test cases in the Excel sheet. We will consider the login page test cases for our site. Furthermore, creating the test cases described in this article is a manual process. Firstly, we will set up a format for that. Secondly, we will enter the relevant data. Thirdly, we will use a formula to return the test case status
Step 1: Setting Up Format
We will set up a format for the test cases for a login page in the Excel sheet. There will be ten columns in the template of this article. We will show you the template in two parts for better visualization.
- Firstly, create the following fields –
- Project: The project name for the test cases.
- Module: The module name for the test cases.
- Reference File: Here a file can be attached for reference purpose.
- Date Created: The date on which the test is carried out.
- Date Reviewed: The date when the test cases are reviewed.
- Comments: The tester can provide comments in this field.
- Secondly, create the following columns –
- ID: We will assign a unique identification number for each test case.
- Test Scenario: The test scenario for the test cases.
- Test Case: The subject for a test procedure.
- Pre-condition: If there is any pre-requisite for a test case, we will input it in here.
- Thirdly, insert more columns –
- Test Steps: Required test steps to carry out a test case.
- Test Data: If any data needs to be entered, then we will use this column.
- Expected Output: The most likely output, if everything works perfectly.
- Post-condition: If there is any condition required after the test is carried out.
- Actual Output: The real output we get from a test case.
- Status: Pass, Skip or Fail.
Read More: Prepare Test Cases for Gmail in Excel Sheet (with Easy Steps)
Step 2: Entering Relevant Data
In this section, we will enter the relevant data into the empty cells. After that, we will use the Wrap Text feature to format the cell values. Additionally, AutoFit the rows may be required.
- To begin with, type the values into the empty cells. Now, we cannot see the values properly. We will fix this in the next step.
- Then, select the required cell range.
- After that, from the Home tab, select Wrap Text, which is under the Alignment section.
- By doing so, we can see all the cell values clearly. Moreover, for better visualization we have shown only the first four columns.
Read More: Test Cases for E-Commerce Website in Excel (with Easy Steps)
Step 3: Finding Test Status
In this step, we will create a formula by combining the IFS function to return test cases’ statuses in Excel. When the expected outcome and the actual outcome are the same, we will get the “Pass” status. Moreover, when the actual outcome is “As expected”, we will also get the “Pass” status. For all other scenarios, it will be “Fail”.
- Firstly, type the following formula in cell K13.
=IFS(J13=H13,"Pass",J13="As expected","Pass",TRUE,"Fail")
- Secondly, press Enter.
Formula Breakdown
- There are three conditions in the formula.
- Firstly, J13=H13,”Pass”
- When the value of J13 is equal to the value of H13, then, return Pass.
- Secondly, J13=”As expected”,”Pass”
- When the value of J13 is equal to “As expected”, then, return Pass.
- Thirdly, TRUE,”Fail”
- For everything else, return Fail.
- In this case, the formula reduces to -> IFS(TRUE,”Pass”,FALSE,”Pass”,TRUE,”Fail”)
- Output: Pass.
- The first condition is true, so it returns the value from that condition.
- Thirdly, use the Fill Handle, and fill the formulas into the rest of the cells.
- Finally, this will complete the process of writing the test cases in the Excel sheet.
Read More: How to Create Test Cases for Registration Form in Excel Sheet
Conclusion
We have shown you three quick steps on how to write the test cases in the Excel sheet. There is a template for your practice in the Excel file. If you face any problems regarding these methods, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy, for more Excel-related articles. Thanks for reading, keep excelling!
