Dec
12 Good Spreadsheets vs. Bad Spreadsheets
Spreadsheet "Design"
Spreadsheets are usually "grown", not designed. If you are responsible for a spreadsheet that is vital to a business process, relied upon by others - you may not be able to describe the parts of the spreadsheet. It just is. There are the places you go to update the data. There are the changes you make to the dates. There are the parts that always break and the parts that never change.
So what makes a good spreadsheet?
Let me know in the comments or on social media what you think. I have an opinion, but there are experts out there!
Spreadsheets are a container for data and programming code. So my experience as a database administrator and designer, and as a programmer across multiple languages (and eras) suggests that there may be some patterns that would be useful.
1) Separation of Concerns.
Many programmers use the shorthand SoC for this, and it is one of the pillars of good design in most coding schools of thought. Make every component of your [thing] do one thing, and that one thing well. For spreadsheets, that probably means looking at the structure of your worksheets. If you have everything on one worksheet, it might make things easier to move some of the content onto another sheet and use relative links to bring the data back for calculation purposes.
A rule of thumb - separate the spreadsheets into sections based on how fast things change. If you have parts that never change, put them in a worksheet and don't touch that worksheet. If it changes, you should know why. If you have stuff that changes all the time, isolate that from the rest of the document so that the volatility doesn't lead to accidentally overwriting some of the longer-lived stuff. If you have stuff that changes on a schedule, work on a naming scheme and put all of the date-sensitive stuff on one worksheet. Then you can turn that into a template and change only that stuff on a schedule.
2) Don't Repeat Yourself
This comes from "The Pragmatic Programmer" by Hunt and Thomas (right up there on my bookshelf). If you enter data twice, one of the times will be wrong - not every time, but often enough that there will be a problem at some time in the future. So try to avoid the cut and paste, and especially the re-typing. If you can automate the export and import of data from your other tools, do it! The fewer the steps involved, the better.
There are plenty more - let me know your best ideas, or the source of your best Spreadsheet tips!
...Follow me on Twitter @NorthCreekSoft - Like North Creek Software on Facebook - email me at dave@northcreek.ca...
Categories: Methodology , Software