Method 1 – Setting Up Format
- Create the following fields:
- Test Case ID: Input the unique test case ID.
- Test Priority: Priority for the test case scenario.
- Test Case Description: Description of the test case is recorded.
- Date: Date when the test is carried out.
- Create the following columns:
- #: The serial number for all the test cases.
- Activity: The subject of a test procedure.
- User Input: Input for that particular test case.
- Expected Output: The most likely output.
- Insert more fields and columns:
- Tester: The person responsible for the test.
- Precondition: Any pre-requisite.
- Postcondition: Any condition after the test is carried out.
- Actual Output: The real output we get from a test case.
- Test Medium: The platform the test is carried out.
- Result: Pass, Skip or Fail.
- Comments: If a tester needs to comment on something.
Method 2 – Entering Relevant Data
- Type the values into the empty cells. We cannot see the values properly.
- Select the cells.
- From the Home tab, select Wrap Text, which is under the Alignment section.
- You will notice the rows are breaking; you must select the cells and increase the row height.
- This is a snapshot after doing all the steps.
Method 3 – Finding Test Status
- Type the following formula in cell H10.
=IF(OR(F10=E10,F10="As expected"),"Pass","Fail")
- Press Enter.
Formula Breakdown
- OR(F10=E10,F10=”As expected”)
- Output: True.
- This function checks whether the value of cell F10 is equal to cell E10 or The value of F10 is equal to “As expected”.
- Then, the formula reduces to -> IF(TRUE,”Pass”,”Fail”)
- Output: Pass.
- As the condition is true, the formula returns the first portion of the formula.
- Use the Fill Handle, and fill the formulas into the rest of the cells.
Method 4 – Creating Another Test Scenario
- Create the following fields.
- We have two new fields:
- ID: We will assign a unique identification number for each test case.
- Task: This is the same as “Activity” from the first example.
- We have columns to enter statuses for three dates; we can compare the test cases.
- Type all the relevant values. We have manually typed the statuses without using any formula.
Download Practice Workbook
You can download the Excel file from the link below.
Related Articles
- Create Test Cases for Login Page in Excel Sheet
- How to Export Test Cases from ALM to Excel
- How to Create Test Cases for ATM Machine in Excel Sheet
<< Go Back to Excel Test Cases Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
thank you for the information
Hi Zidane
Hope you are doing well. Thanks for your appreciation.
Regards
ExcelDemy
What measures can be taken to maintain the integrity and accuracy of test cases in an Excel sheet throughout the testing process for a banking application?
Hello Telkom University,
Thank you for sharing your concern here. The step-1 of this article ensures setting up the format and the integrity and accuracy of test cases of the testing process. However, I will list some additional approaches you can take to maintain the integrity and accuracy of test cases:
1. Worksheet Protection:
Approach: Protect worksheets with a password. Limit access permissions to authorized team members. Regularly review and update access rights based on the testing.
2. Data Validation:
Approach: Set up data validation rules in Excel. Specify allowed data types and values for each cell. This prevents errors caused by incorrect data entries during testing.
3. Cell Comments and Documentation:
Approach: Add comments to cells for additional information. Maintain a separate documentation sheet detailing test case specifics, ensuring clarity for team members.
4. Consistent Formatting:
Approach: Establish a consistent format for your Excel sheets. Use the same font styles, colors, and cell formatting throughout. Consistency enhances readability and reduces confusion.
5. Regular Reviews:
Approach: Schedule regular team reviews of the Excel sheets. Discuss any updates or changes to ensure everyone is aligned. Use this as an opportunity to address questions and concerns.
6. Backup:
Approach: Regularly back up your Excel sheets to a secure location. Cloud storage is a good option. This ensures data recovery in case of accidental changes.
7. Excel Features:
Approach: Use Excel features like Named ranges, filters, and Conditional Formatting. These tools help organize and highlight critical information, making test cases more accessible.
8. Training and Documentation:
Approach: Conduct training sessions on Excel usage and the established processes. Document guidelines for creating, updating, and maintaining test cases.
Take these steps to properly maintain the integrity and accuracy of the test cases throughout the testing process for a banking application.
Regards,
Yousuf Khan Shovon