Software Quality Assurance & Testing Stack Exchange is a question and answer site for software quality control experts, automation engineers, and software testers. It only takes a minute to sign up.
Sign up to join this community
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).
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.
I made this sample spreadsheet for a project named Gmail (download it here).
Some comments:
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.
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.
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 :).
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.
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)
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
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.
At my current company, we are using excel to keep track of our testing. We have a QA Checklist Tempate spreadsheet that consists of:
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.
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.
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.
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
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 :)