I am a beancounter and an IT geek. Ergo, I know a thing or two about spreadsheets. Upon reviewing a third-party's spreadsheet today, I got thinking about it - and these are my five top tips.
- Input, output and setup should be on separate sheets
Each of my spreadsheets with more than a few calcs on them have at least three sheets. The first is for setup data. This is data that is often shared by many other sheets (and formulae therein) or by other macros. The second is for data. For data to be good data it should not have blank rows in between data rows and it should not be overly formatted. It should look just like an MS Access table. The third sheet is the output sheet. Here is where the fancy formatting, spaces between lines and other presentation trickery should take place. Formulae in the output page link to the data page (or pages).
There is nothing wrong with having multiple data pages, or multiple output pages, or multiple setup pages. And of course there are some exceptions to the rules, but we'll skip over those for today.
- Put totals at the top of your tables, not the bottom
This way, data can go all the way down the page and - as long as you have used a large enough cell reference - your totals will be easily accessible (especially if you freeze panes, freezing your totals row in place) and always right.
- Don't make data look like output
This is a big one and is related to point 1). How many times have I been handed a spreadsheet where the author has formatted their data by inserting lines and multiple headers. It limits what you can do with Excel formulae and automation. Data should look like an MS Access table.
- Put the filepath, sheet name and datetime stamp in the footer
I am a little guilty of not doing this from time to time. If nothing else, printouts will be versioned. This will alone will save your skin more than once. It will also help you to find you files again (most offices do not allow offline search engines to be installed) which will save your skin once more.
- Know when not use a spreadsheet
Why do people create application in spreadsheets? Because it is easy to do so. Why do companies create 'proper' applications? Because they work better. Often, spreadsheets are made to do jobs that databases do better. Sure, they have a steeper learning curve and they take longer to develop. But in general they hold together a lot better, are more scalable and, once they work right, they don't fall prey to errors like spreadsheets to. And if the once-innovative spreadsheet has turned into a monster, but is a monster that your business relies upon, perhaps it is time to replace it with a custom developed application.