18

It is an unfortunate reality of our industry that many testers are stuck using a spreadsheet to manage their test cases.

I wanted to ask (in a community wiki), what are some good techniques and examples that people can share on how someone can manage test case planning and execution in Excel?

(I know that this does not exactly fit the Q&A style, but I think that the question and answers will be valuable to testers who use spreadsheets as a test case management tool).

4
  • 1
    Kind of counter productive but how about not using excel since it is utterly unsuitable for the task? A better question would be "How to migrate from Excel as a test management tool?". Dec 18, 2012 at 11:09
  • 3
    Ideally yes, but a lot of people DO use excel (and share point), and I'd like to understand HOW they do it. Dec 18, 2012 at 11:23
  • For sure, it is a good question to be honest -- +1 having thought about it. I am just loathed to see people using what I believe are the wrong tools when there are good ones available. Dec 18, 2012 at 11:30
  • 1
    @Sardathrion Welcome to SQA. "The wrong tool" is a matter of perspective. As Bruce said, sometimes testers are stuck using a spreadsheet because the choice is outside of their control.
    – user246
    Dec 18, 2012 at 13:51

12 Answers 12

9

I do not have any examples but I can describe an experience, albeit more about written test cases than about using Excel.

When I started my previous job, test cases were not written down. Moreover, the IT department was openly hostile to the QA team; asking for a server as a test case repository was a non-starter. As an intermediate step, I picked an subset of the product and put test cases in a shared Excel spreadsheet. I think there were four columns: a description of the test case, the tester assigned to it, the pass/fail status, and free-form comments (e.g. for bug IDs).

The test cases were simple, declarative statements rather than step-by-step instructions, e.g. "Every field honors its minimum and maximum field lengths" or "Field X is mandatory."

Each release, as the product changed, some of the test cases would need revising, too. I wanted to keep the old test results for historical purposes, so we made a new copy of the spreadsheet for each release.

I think we followed that practice for a few releases. We had limited success. On one hand, the testers liked having something written down, and they liked being explicit about who was testing what. On the other hand, many on the team were not accustomed to writing test cases -- although they overlap, writing is a different skill from actual testing.

After a few releases, we managed to get the IT department to install Mediawiki on a server, so we switched to documenting our test cases there. We went back to personal communications or email for assigning testers to test cases and for reporting status. (We used Bugzilla for bug tracking, but we didn't have a formal way to declare which test cases were tested and which were still left to do). I left that job a few years ago. As I understand it, they still use my test automation framework, but they stopped using the written test cases altogether. I think the test cases grew obsolete and there was no one left on the team who was interested in writing.

1
  • 4
    Your post made me think of another reason for using excel: maintainability. Although overtly there is more maintenance required for an excel test case management system, it may be seen as the low maintenance option as many people feel they know excel well and can jump in and out to change a format as suited.
    – SheyMouse
    Dec 18, 2012 at 17:39
9

I made this sample spreadsheet for a project named Gmail (download it here).

Test Case Spreadsheet

Some comments:

  • Test Case ID: use this value to link each bug with one or more Test Case IDs.
  • Test Case Description: they may be independent from each other or something like
    1. Run Test Case ID 4
    2. Click on "submit"
  • The Status columns (the ones with the colours) are pretty self-explanatory. The "Failed" status should include a link to the issue detected. A failed test case should be linked to one and only one issue in your bugtracker. Otherwise, it's hard to do regression testing.
  • For every test run there is a column added to the left. The leftmost column should be the most recent test run. Optionally, you can include the revision under test.

Advantages

  • It's easy to show the results in an e-mail, you just copy and paste the spreadsheet and the most recent test run. Also, because you use colours, it's easy to see the overall status of the test run.
  • It's easy to add more test cases.

Disadvantages

  • It's hard to know whether a test case has been written before, i.e. if you're not writing duplicate test cases.
1
3

As a consultant, I am often sent to a client who does not have a test case management tool. It can be a short contract which would not last as long as the time it would take to introduce a test case management tool and teach the users how to use the software.

With Excel, I can format a basic test case template for use by myself or the client within minutes. This is then used to track what has been tested on a feature (test steps or scenarios), and monitor whether the testing passed or failed. In the case of a failed test, the bug is outlined in a "Notes" column. After a bug fix, the results of the re-run are recorded on a second sheet within the Excel workbook.

Finally, the spreadsheet can be placed on the network for reference, attached to an email, or printed.

From my experience this approach helps a client gain confidence in the value of testing, and gives them space to consider which tool they want to use.

On a final note, I take comfort from the knowledge that the team is now recording their tests, their results, and any bugs somewhere more formal than in an email (as is often the case). This makes testing a process which is repeatable, traceable, and adjustable, whilst also introducing a common point of reference.

1

A have a technique and example for you. Our dev team and I have 2 methods of communicating bugs and bug fixes: e-mail and Excel. If it is a bug or feature of large scope, or an installation build, I use excel.

In terms of bug or feature of large scope, my excel spreadsheet has a minimum of 3 tabs. The first has the versions and objects affected, as well as any testing notes the dev has told me. The second has my test scenarios with staging data, steps, expected results and real results. This is stored per version and per platform so it can get fairly large. The 3rd tab has what we call the "Issues list." (wink wink nudge nudge, aka "bug list") On the Issues list I number the issue, provide a description, a hyperlink to documentation and a 'status' column (open/closed/he said/she said). There is usually a column for 'notes' as well since the devs will occasionally break down for me specifically which object they fixed or the design logic behind why it's as designed.

When I started here, none of this was being tracked. I put a stop to that. After I clarified theirs and the customers expectations (not well received at first, but I'm a bulldog), I then provided my testing steps and results, usually in a Word .doc file (baby steps, folks). Slowly, my system grew on our team. I have said on SQA before and I say it again: do what works best for your team. What works for your team won't work for mine, and vice versa. Flexibility is key!

One day, a dev called me "Macguyver" - I didn't watch that TV show and had to ask someone else, did he just insult me or what? Thankfully I found out it's a compliment.

I am proud of my excellent rapport with devs and work to protect it.

Finally what I am most proud of with my .xls files is this: I campaigned and sold the idea to management and devs to upload a comprehensive .xls to SharePoint where we ALL update the .xls file with the latest and greatest updates to issues. It works; they are ALL on board with this. At first they were put off by having to look at my test scenarios but I quickly learned to make it open up to the issues list to save them time. Also, to save them time, when an issue is verified as fixed or otherwise closed, and if there are > say 10 issues, I move it to a tab called 'closed issues list' thereby eliminating closed items so the devs can focus on the outstanding issues.

One thing I love about files on SharePoint is the ability to receive an e-mail when a file is updated. So as soon as they update it, I get an e-mail. I then check to see if I found a valid bug, it's as designed or I misunderstood the feature (few to no requirements are provided).... and sometimes I am flat out wrong.

You mentioned using Excel for test planning. I did attempt a Excel test plan many years ago, which my manager welcomed, but it didn't go further. We strictly use Excel for test scenarios, object/version reference and a bug list.

And, finally, when I first came to work here, I instituted a practice of using Excel to store test cases (they were 99% nonexistent). I have hundreds if not thousands of examples of Excel test cases if you need one. But since our QA staff went from 3 to 1, I no longer have time to create and maintain test cases.

1

Recently my boss tasked me with finding a Test Case manager to hold our regression suites. After extensive research i found TestLodge to be the best in terms of affordability and UI interaction. We were originally using TeStuff but with the comparison in price it was simply to expensive. I was not really a fan of all the web based platforms that i tested but TestLodge was by far the best.

One thing i really liked and that i would recommend for you is the Google Docs integration. I exported all the high priority regression tests into an excel document and pulled them into shared Google Docs. The best part about this is now we have them on the cloud and can be accessed by any member of the team given permission which is a huge plus for us. Then import them to TestLodge and assign them to each member of the team as needed. Hope this helps :).

1

Using Excel to mange test cases does have many problems, but there are a few things you can do to make life a little easier.

  • Use a collaborative tool - Instead of using Excel, how about a tool such as google spreadsheets. This allows you to work online and also allows multiple people to use the tool and make edits at the same time.
  • Maintain the templates - Make sure you clearly label which spreadhseet is the main template and always copy this when starting a new test cycle and always make sure any edits get made to this master copy.
  • Share - Make sure that everyone has access to the place where you store the files to prevent people from making multiple copies.

Eventually you will start to find this a little painful and the best thing you could do is try and explain the benefits of using a dedicated tool to your team. (Maybe suggest trailing a tool for one project and seeing how they get on)

0

At my first job, we used Excel extensively (and I have used it at a few places). I don't think it is a horrible tool as I have seen people use it amazingly well and efficiently, it just depends on what's the main purpose. There are way too many non-engineering firms where a tons of people work as non-programming QA for whom integrating the test cases with scripts, etc. may not be the most important feature.

(Just what I have used as, not recommending to use excel over other tools)
Test Plan and Cases

  1. Sheet 1 - Included a basic test plan, revision history, comments, etc.
  2. Sheet 2 - Test cases: First column had a TC title in bold followed by Steps. Second column had expected results, third - actual results, fourth - Priority, fifth - Pass/Fail, sixth - Comments.

I worked with a guy who used excel macros and formulas really well to formulate the expected results from the input data. While he was manual testing, he would just enter the input values and the actual values in respective columns. The final sheet in his spreadsheet had Test cases which would be set to pass if the expected value matched actual.

0

At my current company, we are using excel to keep track of our testing. We have a QA Checklist Tempate spreadsheet that consists of:

  1. Functional Checklist - list of requirements. Mainly focusing on whether the application works as intended and is fit for purpose and usage (Positive testing)
  2. Test Scenario Checklist - list of test scenarios that the testers document while they testing the application. These test scenarios can be detailed or high-level, it all depends on the tester and the type of tests can vary from negative to exploratory to performance testing.

We are busy investigating different test management tools to replace the excel spreadsheet but they have been working with relatively good success for the past few years at our company.

0

In my project we use excel spreadsheets for test case execution and management. Once testing finishes we import the same on Quality Center using excel add-on (I understand that is a waste of effort).

The excel sheet has got a specific format i.e. Sheet1- Version Control, Sheet2- Test Scenario with steps, Sheet3- References etc. Let me know if you are looking for a sample template.

1
  • Why are you using excel if you have spent the $$ on QC? (I think I already know the answer but I'd like to know your view). Linking to a sample sheet would be great. Dec 20, 2012 at 22:35
0

Although Excel has been around for a long time now, its astonishing simplicity and ease of use still make it a good test management tool. And since test suites are invariably a combination of manual and automated tests, you cannot avoid having to manage your tests manually. Also, cloud-based spreadsheets have made shared access possible, so that the test suite record can be updated easily.It is easy to maintain the test record for multiple environments by using spreadsheets.

0

Several years late, but maybe these example XL templates (in the download folder out on github) will be helpful?

There's full documentation and links to training videos at this gitHub site: https://github.com/DataResearchLabs/my_test_case_manager

-1

As I am a developer, my answer will be sarcastic. Please do not take it personally :)

It is maybe the right time to update your QA tools if you are still using Excel?

If you have heard about the Agile manifesto and agile software development, you probably would know that thanks to these approach, quality insurance becomes a part of the development and it is not a different function. When you apply TDD, BDD, or whatever test-first approach, you start with the test before the coding, so clearly an Excel Spreadsheet is not the right tool. You typically need a tool for Agile Development

If you didn't realize it, Excel is a old tool from the nineties and it is a spreadsheet, not a management tool. If you want a management tool, I suggest you to look for a management tool. If project management / but reporting tools like JIRA are not enough for your QA needs, I warmly suggest you to have a look to some providers of test management solutions.

Testuff for example cost 27$ per month if you get the monthly subscription, having an unmanageable Excel spreadsheet to handle your QA will cost you much more :)

6
  • How does this answer the question? "A lot of people DO use excel (and share point), and I'd like to understand HOW they do it." + "As Bruce said, sometimes testers are stuck using a spreadsheet because the choice is outside of their control."
    – dzieciou
    Dec 20, 2012 at 16:30
  • "Excel is a old tool from the nineties" LOL! From the nineties? That's got to be bad, right? I don't judge the fitness of a tool based on when it was invented. And of course most of us are using somewhat newer versions of Excel (and Windows). Dec 20, 2012 at 16:35
  • 4
    The question is not "Is excel the right tool?". It is "I am using excel. How can I use it better?" Dec 20, 2012 at 22:33
  • 2
    The Scrum Alluance even has a section on its site for Excel templates - scrumalliance.org/resources/6 - and a google of Excel and Agile shows a lot more examples of people using this tool from the 90s... Dec 21, 2012 at 4:03
  • 1
    @Edmondo1984: I might sound sarcastic ;-) but your belief makes a perfect sense in the ideal situation when you're not constrained by your management. Hence, I would suggest you to post another question: "What are better test management tools than Excel?" and then answer it yourself.
    – dzieciou
    Dec 21, 2012 at 22:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.