In each project, the Tester’s work from the Q&A management file, test plan, testcase, test report to specification documents, take notes, as a user manual, etc. uses Excel. Perhaps, so that Excel is listed as one of the powerful tools for Tester.
I have been in a situation where after completing the testcase, I need to change, have to add / remove a few test cases, then I have to type testcase ID and statistics of the total number of test cases. Or simply, every tester must statistically test the number of test cases, how many cases pass, fail, pending, not test, and how many cases need to test … to make a report. If done manually, it is really hard, while this work will be repeated many times. Many times I also struggled not knowing how to present, using Excel function to make the job easier, less time consuming with ~ 2000 testcase sheet …. But Excel can support my tester. that.
I would like to share some Excel functions, recent Excel usage tips I use very often and also learn from colleagues a lot. The project I use is mainly google sheet, but excel file offline sometimes has a bit different menu position but basically the same.
1. The function ‘ROW () – number’ numbering automatically increases
The automatic numbering is usually used to type the testcase ID, using the ‘ROW () – number’ function , even if the test cases are added in the middle, deleted will be correctly typed.
For example: The first test case starts from the 9th row, in the testcase ID column, it says “= ROW () – 8”
2. Use ‘Data Validation’ to create the Select Box
Assuming the test results column in the testcase file has values: Pass, Fail, Pending, Not Test. I want to select a value, so I don’t have to fill it in many times, so I can use ‘Data Validation’ to create a Select Box.
Step 1: Select a cell of the column to create the Select Box
Step 2: Select Data -> Data Validation (Or select the cell to create Select Box, then right-click and select Data Validation.)
Step 3: Create a list value for the Select Box. After step 2, the table will appear:
The simplest way I usually use is:
- In Criteria: Select “List of items” and Input list value into the right textbox, separated by commas.
- At On invalid data: select “Show warning”, you can enter another value in the combobox box, but will display a warning. Select “Reject input” then only select values from the select box.
- Remove validation: If you want to remove validation in that cell.
The result when clicking on the box will show dropdown list:
3. Use ‘Conditional Formatting’
The purpose of this function is to automatically format according to the predefined conditions. Assuming that when adding new test cases, using this method will save a lot of time.
- Step 1: Select the cell or column you want to create Format.
- Step 2: Right-click -> Conditional Formatting -> Conditional format rules table appears.
- Step 3: Select the condition under “Format cells if” and select the format you want in “Formatting style”. And press “Done” is okay.
Example 1: In column D, if “Done” is selected, the cell is marked with blue color, if selected “In-progress”, the mark is orange. Set up as follows:
To add a rule for the “In-Progress” box, select “Add another rule” and the same set.
Example 2: In column E, if the text starts with “2020/06” the text is highlighted and the cell is marked in blue.
4. Create groups for rows and columns
This function helps to expand / collapse the rows and columns for easy management. I often use this function to group testcase types (such as testcase UI, testcase function …), testcase of each part will be grouped … especially the sheets with thousands of test cases, easily see which part I was looking for it, and it didn’t take long to scroll.
How to: Select the row / column to group -> Right-click -> Select “Group rows / columns …”
Ting! Ting! The result displays like this:
- Group columns
- Group lines:
Right click the grouped icon to add more settings …
5. Copy the format with ‘Format Painter’
When it comes to formatting, Format Painter is one of the most effective tools available. It will copy the format of one cell and apply the format to another.
- With just a few clicks, you can copy most formats including:
- Number format (General, Percentage, Currency)
- Font, size and font color
- Font characteristics such as bold, italic, and underline
- Background color
- Align text and letter direction
- Cell border
- Step 1: Select the cell whose format you want to copy. (You can select a range of cells or multiple rows and columns).
- Step 2: Click the Format Painter button in ToolBar
- Step 3: Click on the cell you want to copy the format (or the top, left cell of the area, the first row, the first column, … depending on the area selected to copy).
6. F4 to keep cell / reference region fixed
- Step 1: Place the cursor in the position of the reference area to be fixed.
- Step 2: Press F4, then the symbol ($) will appear in the formula —> When you copy the formula to another cell, the reference area will be fixed, do not change anymore.For example: Consider cell A1:
A1 is fixed in the formula as follows:
7. Count the total with ‘COUNTIFS’ and ‘COUNTIF’
7.1 ‘COUNTIF’ is used to count when there is only one condition.
- Formula: = COUNTIF (criteria_range, criteria)
- criteria_range: defines the area where criteria criteria is applied.
- criteria: condition for a numeric, reference cell, text string, an array, or another excel function, required. Examples could be: 10, “<= 32”, “<> 2020/06/30”, $ A $ 6, “candy”.
- Example: The following data table is available, count the number of tasks completed, and the status “Done”
7.2 ‘COUNTIFS’ is used to count with multiple conditions.
- Formula: = COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2] …)
- For example: Count the number of test cases passed in June, performed by tester “MaiHTP”
8. Conditional sum with ‘SUMIFS’
- This function is used to sum under multiple conditions. Sometimes I often have the synthesis task must thoroughly apply these functions.
- The formula: = SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], …)
- For example: Calculating the total number of hours in June for completed tasks.
Above, I have noted some excel functions that I often use to write test cases, summarize progress, make test plan files, … I was also very confused before, so I only knew. That’s why I am writing this article, just very basic functions only, so I will review it later when needed, I hope it will be useful for you.