In 12 Rules for Making Better Spreadsheets, Chandoo posts some reader tips. I'd like to add some more.
Use less colors
I subscribe to the "top-down" theory when selecting a color scheme. That is, when selecting a color from the fill color dialog (see below), I try to stick to the colors that are in the same "column". I avoid "loud" and contrasting colors. If necessary I change the font color to make the text more readable with a fill color such as brown or green.
Separate data from presentation
I keep the raw data separate from its presentation, in order to make it simple to update the data without disrupting the dashboard. Formulas and functions reference the raw data such that, if it were updated with new data, the formulas auto-update themselves. Dynamic range names ensure that new appended data is always included in the formula operation.
Keep dashboards simple
I don't know about you, but I'm tired of seeing dashboards with ten charts on them, plus data ranges and other analyses. It looks more like showing off than actual work. They mean something to somebody, but not me. Give me something with one or two charts tops, plus a bit of detail.
Name and reference frequently used or 'important' cells
Your company name, or the name of a client, should be placed in ONE cell and given a meaningful name in the Name box. This one cell should then be referenced throughout your worksheets using cell references. That way, if the name changes, the update is simple and cascades through the workbook. It also makes formulas easier to read, ex.
=B1 (have to look at B1)
vs.
=ClientName (I know what that is!)
This goes for other cells too, for example, cells that contain
- Milestone dates and times
- Proper Names
- Tax rates and other numeric constants
- Volatile functions such as current date and time which can slow down calculation if used repeatedly
Make calculations transparent to end user
Some of you might disagree with this, but I think that the calculations your spreadsheet model depends on should be readily available to the end user. For example, in many of my workbooks I include a "Calculations" sheet that contains numbers used by VBA and worksheet formulas to determine output. The reason for this is simple.
I want to go on vacation (or be promoted) without having to worry about someone needing to adjust something in my workbook. They can simply open the Calculations sheet, make the adjusment, and the dashboard will update accordingly. Also, I don't want anyone to open my VBA project (and possibly damage the code) to try and update it.
For example, I would put the current tax rate into a cell on the Calculations sheet. So while I'm on a beach in Florida (or working on another job) and the tax rate changes, someone can open the spreadsheet and fix it themselves easily without having to bother me. Sweet!
I'm aware that you can define constants directly in the Name box, but that makes them a bit harder for the end user to find. Better to put them on the worksheet (with all the other calculations) where it's much easier to change.
I also use dynamic range names with Data Validation so that the list of possible values can be updated by anyone, not just me, and the Data Validation will auto-update.
We could go on and on. For example, I didn't mention merged cells and why they should be avoided. Do you have any more?
About JP
Comments:
Regarding your second tip, rather than separate data and presentations only, I group sheets in 4 categories: presentation raw data, calculations, tables and inputs (or asumptions), and presentation (or dashboard). If posible, a setings sheet.
Many times presentation or calculations sheets can include inputs and asumptions, and many times models aren't that complex. But I think that the most important thing is that the user should become accustomed to always use the same structure in its models in order to easily navigate trough'em in the future.