Excel in Testing

As excel is the de facto tool of any tester, usage of excel starts from test planning, test case creation, test execution and most importantly test reporting, even if we use other tools like MS -Word, web forms, rich text email, or other customized sophisticated technologies, testers may directly or indirectly use MS-excel in test reporting and thus, knowing excel or excelling in excel is one of the key responsibilities of tester. Usually even commercial tools have the option of exporting the test execution status to spreadsheets.

Test reporting without any functions or conditions: This is just copy paste or data entry operations of entering pass or fail or blocked against tests and filling corresponding bug numbers against failed test cases. Though this method is simple, it is time consuming and definitely is not the smarter way of working.

Test reporting with functions and conditions: In this way, we can write macros for copying the cell data across spreadsheets in a workbook, change cell colour based on test status,

refresh the whole workbook on periodical basis, summarize test results using aggregate functions and if conditions--all using single macro click.

Macro programming starts with simple formula entering in the cells and it goes up to complex function calling using VBA.

Personally I have used formula like,

=COUNTIF (Test_Area_Login! C3:C100,"P")

Here Test_Area_Login is the name of the worksheet (which obviously contains Login related test cases), this formula tells that count the number of 'P' entry between the cells C3 and

C100, so between C3 and C100 (which is 97 test cases, too high for login :-)) if tester passes 40 test cases by entering 'P' and fails 50 testcases by entering 'F' and 7 testcases are

blocked (entered as 'B'), now the above formula gives 40.

In the same way, failed test cases can be found by

=COUNTIF (Test_Area_Login! C3:C100,"F")

Suppose let’s assume some test cases are marked as N/A which stands for Not applicable. So in calculating total number of test cases, we should subtract these test cases, which can be done by

=COUNTA (Test_Area_Login!C$3:C$100)-COUNTIF(Test_Area_Login!C$3:C$100,"NA")

Moving forward, we can access and control the cell values.

Worksheet ("Test_Area_Login").Range ("C1").Value=10 -->Assigns 10 the cell C1

We can change the active cell from one cell to another cell by,

ActiveCell.Offset (1,0)=1 (Places 1 on the next row)

ActiveCell.Offset (0, 1) =2 (Places 2 on the next column)

We can also change the background colour of the cell based on our own condition (if passed, then green, if failed, then magenta etc.)

We can do so by,

If (Worksheet (Test_Area_Login).Range ("C1").Value="P") then

Worksheet (Test_Area_Login).Range ("C1").Interior.ColorIndix=3

The interior property references the colour and style of the shading; it uses an index, rather than real colour. What is the colour of magenta? I don’t know, we can find it , by the following

VBA procedure,

Sub DisplayPallet()

Dim N As Long

For N = 1 To 56

Cells(N, 1).Interior.ColorIndex = N

Next N

End Sub

The options through the macro and VBA are countless, what I have touched here is just a tip of ice berg. Usage of macro and VBA programming will definetely rise the bar in test reporting and make it more sophasticated and smart.


Welcome to our blog, yet another blog in software testing. We know that there are numerous blogs in software testing discussing abounding no. of. Topics.

In this blog, our intention is to share our testing experiences, challenges we met, questions we have (and you have) while continuously getting the input from you.

Well, there are many grey areas in software testing,though explored by many of the industry’s most powerful testers, but still remaining as puzzles.

-Are testers Non-technical persons middle of 'techi developers'?
-Self respect of tester in a development team
-Measuring the capacities of testers in a testing team
-Precise test estimation
-Power testing
-Effective automation
-Using Open source tools and scripting language in software testing
-Evolution of testing and tester in agile period

are some of few...

There are many areas in outer world, which are not (yet) completely explored by software testers,at least you and me, many mathematical subjects (like mathematical modeling, permutations and combinations), Statistics (Sampling and probability) , Logic (Complex truth table) are to name a few..

Still we do not know the precise answer to questions like 'How you missed this bug?','Why automation framework is not robust across builds?','Can we release the product?' etc.

This blog will continuously explore these grey areas, will get questions (as well as answers) from friends to solve our testing problems,ways to improve our testing, ways to make our self powerful testers,attaining international standards (see, v shud be a bit enthu.. :-))


-Vasu & Gokul