Test Case Management for Excel

Implementing a Robust Strategy Beyond Spreadsheets

Start QAComplete Free Trial

The world of software testing has changed a lot in recent years, with an increasing number of organizations moving towards an agile approach to software development. In an agile world, teams are being asked to move faster — reducing the length of time to delivery while still continuing to improve the quality of each release. At the same time, they are faced with increased pressure to reduce testing costs. This agile approach also means that different testers, with vastly different skillsets, are getting involved in the testing process. In fact, if you look at the trends over the past few years, testing is moving further to the left, — with more and more developers being incorporated into the testing cycle earlier on.

As a result, many times, we see manual testers, automation engineers, and developers all contributing to the testing process. The needs of these three audiences are different, especially when selecting testing automation tools. Some of the ways in which these three personas select an automated tool include:

  • Manual testers: Record and replay is crucial for manual testers, especially those who are new to automation. Being able to use the same recorded script with variety of input data can come in handy when trying to identify and fix problems across multiple environments.
  • Automation engineers: For automation engineers, robust support for scripting languages, integrations with CI systems, and the ability to scale tests easily is important.
  • Developers: Implement testing into the development process requires the ability to conduct tests within IDEs such as Eclipse and Visual Studio.

The question then arises, in an agile landscape, where different testers with different skill sets are getting involved, how does test management fit into the process? In many cases, we still hear that test management is a blocker to the agile process — impeding the speed at which software is delivered.

While the approach to testing has changed in recent years, the primary tool for managing these tests has not. Today, a majority of teams are still using Excel spreadsheets to organize and manage test cases. And while this old school method of test management still makes sense for some, a growing number of teams continue to be held back by their reliance on Excel. The stickiness to Excel just proves the great value associated when using excel for test management activities.

However, before we go into that direction, there are other few questions that we would like to touch upon, such as:

  • What are the benefits of test management in an agile world?
  • Why is Excel still the most widely used tool for test management?
  • What do teams miss out on when they rely on Excel for test management?
  • When is the right time to make the switch to a test management tool?
  • How to choose the right test management solution?

While test management can sometimes be viewed as a blocker on agile teams, there are a number of benefits that can’t be ignored. This is especially true for teams that want to improve their efficiency and avoid costly setbacks. Some of these advantages include:

Prioritizing Your Tests

In an agile world, testing different permutations and combinations of tests is not a possibility. This is why it’s critical for teams to prioritize tests for risky areas before releasing a product to the marketplace. Identifying risky areas can be a simple process. For example, you may have two different fields in your test management tool, such as impact and probability, which you grade on a scale of 1-10. In this example, one of your product areas the impact may be 2 (low) and a probability may be 5 (average). Multiplying these two values will give you a risk value. In this case for instance, the risk value would be: 2 x 5 = 10.

The process can be taken a step further and the including impact and probability weights to different product functions. The formula in that case changes to:

Impact (Weight for impact factor 1 x value for factor 1 + Weight for impact factor 2 x value for this factor 2…. ) x Probability (Weight for probability factor 1 x value for this factor 1 + Weight for probability for factor 2 x value for this factor 2)

In this case, the impact consists of two factors: Usage frequency and visibility (for prospects and customers). I rate them on a scale of 1-10. Similarly, the probability consists of three factors (ranked on a scale of 1-10):

  • Complexity
  • Number of People Involved
  • Time Pressure

Using the weighted sum values in the below example, I would test Function B before A and C as it has the highest weighted sum and thereby most risky. This can help save time in an agile environment where sprints last just a couple of weeks.

Weighted-Average.PNG

Regulatory Compliance

In the case of regulated industries, such as medical devices, it is often required to document and retain a number of details related to the tests in the event it needs to be submitted to a regulatory body. A few examples of test data often companies are required to document include:

  • When were tests run?
  • Who ran the test?
  • What requirements did they cover?

A test management tool can come in handy in such a situation since it can automatically keep a log of these details for different tests run at different times.

Deciding when to Ship

Test management allows you to objectively measure quality and track progress. Historic trends can help you gain an understanding of how your efforts are progressing and, in turn, show the measurable business value of your testing efforts. Reports such as: test results over the past seven days and tests per requirement, as per priority, can be helpful when making such a business decision.

Reducing Data Duplication

A test management tool can reduce duplication of data which traditionally occurs when requirements, tests, or defects are managed in a silo. It ensures everyone — from business analysts to developers, testers, and QA managers — has one view of the progress being made. It gives team members an end-to-end view of: requirements, tests, and defects — ensuring that proper coverage exists.

As you write more automated tests, as well as manual tests over time, maintaining traceability between the requirements and defects raised in correspondence can become tough. Test management systems can help ensure proper API and functional GUI test coverage exists for requirements.

Improving Coverage of Manual Tests

Running a manual test through different data sets is often challenging and unproductive as it involves manually trying different sets of data for specific test steps. A test management process can make the process of iterating an existing manual test through a variety of data set really easy, thereby increasing the coverage of existing manual tests. Take the following example:

A manual test that tests the login functionality in three steps. The first step consists of entering JSmith@Acme.com as username, while the second and third steps consists of entering “Pass1234” as password and clicking the submit button respectively. Having a test management tool such as QAComplete can help increase the coverage of manual tests as now we can run login and password fields through different data sets at the click of a button. The sample data could look something like this:

Manual-Testing-Example.PNG

Increasing Collaboration

The success of an agile team is primarily based upon collaboration between different stakeholders. Test management ensures that dev and QA teams are on the same page, as different testing assets can be viewed as and when needed. It helps reduce the silos of information that exist between different teams and gives people the same information whenever they need it.

One question still remains: if test management comes with so many benefits why are so many teams putting their trust in Excel to do it? In the next chapter, we will look at the benefits and limitations of using Excel for test management.

One of the biggest challenges of implementing most test management tools into an agile environment is that they are rarely customizable. Instead of meeting the needs of the users or ensuring that the tool can change to fit in with existing processes, most tools force teams to change their processes and adapt their behavior to make the tools work for them. Unfortunately, changing people’s behavior is traditionally a difficult process and as a result, more and more people end up sticking with Excel spreadsheets. Other key reasons why Excel is popular for test management purposes include:

No training curve and limited auditing

Any tester can get started with using Excel for test management purposes. The learning curve is really low and as a result, it requires less time commitment from a resource perspective. On the flipside, it provides a good mechanism to hide mistakes. Not tracking changes means that people can easily hide missteps without concern of being audited. For some teams, the lack of accountability of Excel could be a major benefit.

Integrating with MySQL

Excel can work well with back-end database systems such as MySQL. As a result it can come in handy for pulling in your test results and in turn, reporting on them. Add to that the fact Excel is operating system independent; hence these functionalities can be leveraged across multiple operating systems.

Use of Macros

Team members that know how to code can take the steps to the next level by writing a VBA code and further limit any manual intervention that occurs while collecting different test results such as number of tests run, priority of test, who ran the test, and when those tests were run.

Basic Reporting

It is quick and easy to create basic reporting templates in Excel. Bug count by severity and requirement, along with tests executed over the past few days are examples of reports that can be easily built in Excel using a PivotChart. Add to that, different excel reporting add-on is available to create reports and dashboards.

Lack of Integrations with Automation Frameworks

Working in an agile environment typically means that you are automating a majority of your testing processes. A test automation pyramid is often followed to create a solid test automation strategy in an agile environment. The pyramid focuses on creating more unit tests, followed by API tests, and then fewer UI tests. The pyramid in turn looks something like this:

Test-Pyramid.PNG

The successful implementation of a test automation pyramid requires integrating your test management tool with automated tests created through free open source API and GUI testing tools such as SOAPUI and Selenium, or commercial tools such as TestComplete. While this can be done through Excel by writing VBA code, it traditionally comes with added maintenance costs. Add to that, the fact that most of these integrations are available at no extra cost with test management tools such as QAComplete.

Creating Test Sets

Creating test sets containing both manual and automated tests involves a lot of copy and paste in Excel. This can be a time consuming and tedious process. A test management tool, such as QAComplete, can make this process frictionless by helping you build different test libraries by a single drag and drop. As a result, testers can create different test sets, while utilizing the same test cases.

Tracking Changes to Test Cases

An inability to track changes to tests in Excel can limit accountability. As a result, for many organizations, not being able to keep track past tests can be extremely limiting as they scale their testing process. One of the biggest benefits of using a test management tool such as QAComplete comes from the fact that it gives managers the ability to track the changes made to the test. In fact, the manager can see who made the changes, when those changes were made, and, if necessary, revert those changes back. When a test goes haywire, having the ability to revert back to a previous version will be incredibly valuable.

Visualizing Test Data

One of the crucial ways that a test management tool, like QAComplete, is different from Excel is the fact that it can tie into existing systems. Integrations with defect and requirement systems such as JIRA are available out of the box. What this means is your test management tools acts as a single source of information which may be in disparate systems. This ensures that you have optimal coverage, which is essential when conducting ongoing tests. What this means is your test management tools acts as a single source of information which may be in disparate systems. This ensures that you have optimal coverage, which is essential when conducting ongoing tests.

As is the case with any tool, choosing a test management solution requires you to evaluate the tools and features that are available and how they align with your needs. If you’ve been using Excel and are thinking of evaluating an alternative solution, there are a few questions you’ll need to answer. Consider these five questions:

  • Are a large number of your requirements, tests, and defects going undocumented?
  • Is versioning of test cases important to you — specifically versioning of manual tests?
  • Do your tests require integrations with other systems and do you want to reduce the time associated with maintaining those integrations?
  • Do you have a need to combine manual and automated tests in one test suite and reuse that suite across an entire project?
  • Is your team having trouble measuring the progress of tests and does that limit your ability to show the value of these tests to management?

If you’re still using Excel spreadsheets to manage your tests, and answered yes to three or more of the above mentioned questions, it might be time to start looking at different test management solutions. The good news is that many of these limitations can be addressed by choosing the right test management solution. As you start the evaluation process, there are a few important areas you’ll want to focus on.

Does this tool fit in with my existing processes?

First and foremost, how easily does the test management tool fit into your testing process? While agile is quickly becoming the norm, many testing organizations still use an approach that incorporates both waterfall and agile. As a result, it will be crucial to ask the vendor what efforts would go into setting up the tool to fit the needs of different teams within your organization.

Is this tool easy to learn?

Migrating from Excel is already tough since your users have been using them for a while. To make this process less painful, ensue that the test management tool you select is easy to learn and provides the necessary resources, training, and education to help you get started.

Does this tool offer an easy-to-follow audit trail?

Test cases are often changed with the introduction of new features or when changes are made to original requirements. Such potential changes need to be tracked in a sequential manner as it enables managers to figure out what has changed and revert changes back if needed.

How does it improve productivity through reuse?

In an agile process, different kinds of manual and automated tests are often written as a part of a test suite. For instance, a login test suite would consist of few manual/automated GUI tests to test the front-end and then few API tests to test the back-end. While looking for a test management tool, it is critical to look at how easy the tool makes combining manual and automated tests in one suite,and whether or not you can repurpose those test sets across release or sprints.

Does this tool offer pricing that fits my needs and budget?

As with other tools, the cost of a solution should be looked into while selecting a test management tool. This involves taking into account not only the upfront amount it costs to buy a tool but also training, implementation, and support costs for your organization.

 

Looking to move away from spreadsheets? Try QAComplete - the powerful and easy-to-use alternative.

Try QAComplete For Free

 

Start Free Trial

 
 

By submitting this form, you agree to our Terms of Use