Become an Excel Power User: 99 Ways Workshop #90

The Software Testing Club recently put out an eBook called "99 Things You Can Do to Become a Better Tester". Some of them are really general and vague. Some of them are remarkably specific.

My goal for the next few weeks is to take the "99 Things" book and see if I can put my own personal spin on each of them, and make a personal workshop out of each of the suggestions. 

Suggestion #90: Become an Excel power user. Functions, logic and conditional formatting can all be used as powerful analysis and test tools.

This has actually proven to be the hardest "workshop" to create, mainly because (true confession time) I have not used Excel as a "testing tool" for over three years. I used to use Excel religiously for storing all of my test cases and ticking off pass/fail details for years.


Since I started working in "Agile" shops, the traditional "test case in Excel" model has ceased to be part of my daily activities. The closest I get these days to using Excel in any testing related capacity is when I use Rapid Reporter and save the output of the tests.

Having said that, I realize that there are many people who still work in environments where Excel is used frequently to construct and track test case execution. Barring anything else, it's a system that is portable, and the skills developed can be used anywhere, provide Excel (or another spreadsheet application) is the tool being used. While it's a bit "old school" now, one of my favorite uses of Excel is to use it to create a low-tech "dashboard" for a given project. That, however, also requires a bit of creativity and manipulation, which is not easy to do in a 1000 word blog post.


Additionally, just to make life more interesting, my PC bricked in the process of working on this (oh, you lovely "BOOTMGR is missing" failure, you pick the best times to happen, don't you :p?).

Workshop: Use Microsoft Excel to create a low-tech dashboard to help you see progress, and moreover, print out a visual summary for your group/team meetings. Take advantage of the formatting options available to help keep the message focused on the data and the analysis, rather than the features or eye candy aspects.

Dashboards were all the rage a few years ago, and being able to make them in Excel seemed to be a special kind of "Holy Grail". While they don't seem to be as hip and happening today (much has moved onto the web or into various plug-ins for tools like Pivotal or Jira), there's still a benefit to learning what it takes to make even a simple low-tech dashboard, because the skills needed to create a simple dashboard will also help considerably when it comes to learning analysis aspects of Excel (read: the ones that software testers may find helpful/interesting).

Dashboards tend to be three layers deep. the three layers are Data, Analysis, and Presentation.

 The data layer is where you store whatever it is you are interested in analyzing. This is your raw info. This can be output from a number of test runs, a log file, or some other output that is stored in an easy to list manner (the .csv (comma separated values) format is ubiquitous and easy to set up). Since every organization will have some unique aspects to what they want to keep track of, it won't make much sense to define something ironclad, but for the sake of a very simplistic example, let's go with a few fields:

TestNumber, Date/Time, BranchID, Platform, Browser, TestDescription, DevComplete, TestComplete, Comment

The biggest challenge is that most test data that we would store, unless we were looking for things that are specifically numeric, don't actually map to raw numbers. Still, there's a lot of things that we can look at and get a handle on that can be quantified, such as date/time, DevComplete and TestComplete. 

Where I currently work, we typically don't look at PASS/FAIL counts, we look at acceptance criteria and when something is "done" or "accepted". Regardless of what you want to call it, a dashboard works much better when it has something to count based on a particular date and time. Also, formatting the data in a way that is easy to read and reference is vital. Flat data files or a tabular dataset are great for this purpose, and can easily be imported (and more to the point programmatically created ;) ).

The second layer is analysis, and this is where formulas, macros and bits of VBA code tend get called in to help shape the data you have entered or collected. By creating a second sheet for analysis, you can create areas that do multiple calculations such as:

- How many test cases were completed on a given date? 

- How long has a particular story been in flight? 

- Are there tell-tale signs of a section or a particular acceptance test that has been open for an extended period? 

A single report won't tell you this, but having a space where multiple calculations can be formatted and processed helps make this much easier to handle. Note: this page can have a lot of calculations happening. It just depends on what you are looking to analyze. Here's where pivot tables can be extremely valuable, and also give lots of "what if" scenarios to channel and experiment with.

There are a variety of quick formulas that you can use to do calculations; SUM, SUMIF, COUNT, COUNTIF, AVERAGE, VLOOKUP. Using them to span a section of your data sheet, you can create smaller areas of analysis, and you can make as many of them as you want or need to. What's the real benefit of these "analysis tables"?  Other than being able to gather up a lot of information in a number of different ways, you can also condense down the information that is really important to you. By turning them into report or pivot tables, you can also make them expandable as you add more data, and the biggest benefit… the charts and pretty stuff that you display on the front (presentation) page will be created and pulled from the data that is created in these analysis tables.

The third layer is presentation, and this is where the charts, graphs, gauges, colors and any variety of other things you would want to display can be seen. The recommendation here is to be spartan; display only what you need to tell your story.


While there's a lot of ways to add eye candy and make it look all spiffy and cool, focus on what will make your case and share the information you most want to emphasize. One of the best suggestions I've received is to say that, if you can orient the presentation layer so that it can be printed on a single piece of standard 8 1/2" x 11" paper, then you've probably got it right. 

Bottom Line:

This is a huge topic. There's a lot of things that can be done, and way more space would need to be dedicated to doing this than a blog post could possibly cover at one shot. There are several resources out there that can help with learning how to make dashboards effectively, and one book that I have and like is "Excel 2007 Dashboards and Report for Dummies". Don't knock the title, this is actually a very good book and has a lot of great recommendations to play with data and set up a working data model. 

Also, many of the options listed here don't necessarily require that you use Excel. You can do most of the same things in Google's spreadsheet program as well. How basic or fancy you want to get with this is entirely up to you. The point to doing this is to have some opportunity to use Excel (or another spreadsheet app) as what it should be used for, which is an analysis application. Use it to examine your data, and tell your story in the way that you choose. Of course, if you decided that you want to take the next step and put this information up on the web, the data model you use here will also work with a database and server side scripts as well (but that's another post entirely ;) ).