As you get further up the career hierarchy as a tester, you spend more and more of your time inside Excel both as a consumer and producer of data. Excel is unfortunately though one of the most abused pieces of support software a tester is exposed to. If you are dealing with Excel, please follow these practices.

  • Exploit the fill handle to populate series’ of information. There is also a menu option which allows you to create custom series
  • Use Named Ranges to organize clumps of data on a single worksheet
  • Separate logical blocks of information onto separate worksheets
  • Hide any intermediary steps used in calculations on a separate worksheet which is not visible
  • Use patterns, not colours to fill in charts / graphs. Not everyone has access to a colour printer or wants to use their ink
  • If the date matters, type it out rather than use one of the handlers as those are dynamic
  • Format your page breaks for your target audience. A North American consumer will likely have ‘Letter’ as the default page setting but an Indian one will have A4
  • Keep the current version always checked into version control
  • Check them into version control and send an announcement that it has been updated vs emailing the actual workbook.
  • Put a single type of data in a single column rather than overload columns. I received a workbook this week that had 4 pieces of unique information in one column. Breaking that column into 4 individual columns would have make sorting / interpreting a lot clearer.
  • Name your worksheets; Sheet 1, Sheet 2, Sheet 3 are not intuitive
  • Delete empty worksheets
  • Merging cells is an excellent way to show aggregations of information
  • You can changing the border location and style
  • Excel is useful for displaying numbers, and calculations with / between them. It is not a database; keep that in mind

Thats the list off the top of my head. If people who send me workbooks followed even a 3rd of these, I think I would be a (slightly) happier camper.

(And no, it is not a formatting mistake that one of them is highlighted)