Dos and Don'ts Of Spreadsheet Design
It occurred to me that don't have a section on the Dos and Don'ts Of Spreadsheet Design... I think the best way to do this is to start you all off, and you can add your own pet loves / hates
Do Separate inputs from outputs
Do Use Data validation on input cells
- make it as restrictive as possible
- this reduces the chance of bad data
Do Autofit your columns
- screen real estate is valuable
Do Add Range names to important ranges
- this means your VBA will be a lot clearer
Do Save your data to a csv, and create reports driven by pivot tables
Do Put all configuration variables in their own sheet
Don't ever ever ever link formulae from one workbook to another
Don't Use Excel as a database if you can avoid it
- get your data out of the spreadsheet and into CSVs or a database
Don't Use Autosave, it will annoy you !
Don't Forget to save your workbook as soon as you make a change you're happy with
- Nick's blog
- Login or register to post comments
- 19847 reads
Dos and Don'ts Of Spreadsheet Design
Do: organize your data in regular rows and columns grid.
Do: give your table column headers
Do: in complicated formulas, use named ranges as much as possible, for sake of legibility.
Do: use Custom Views to easily switch between different display setting of your sheet (filters, hidden rows and alike).
Don't: create your input table in form of your desired output because this will make creation of your output a nightmare. E.g., don't enter data in form of pivot table.
Don't: leave completely blank rows or blank columns in the middle of your table. Your formula and pivot table may not include all necessary data.
Don't: enter currency symbol/abbreviation in the same cell with amount (e.g. GBP 350.00), because cell will be treated as text and no calculation can be made of it. Rather, enter value and format cell as Currency.
Don't: enter manually values when you want to show numbers in different units/scale. E.g., to show 3 millions in thousands, don't enter 3,000. Leave your figures as they are, and format cell to show contents in thousands. Thus, you will avoid rounding problem in totals.
How about adding these: Do
How about adding these:
Do use colour to clearly distinguish inputs and calculated values (I also use colour to differentiate between inputs and control values)
Do use in-cell comments - it will save you months of time when you revisit something in a years time
Do add a notes sheet to your model and document what you did to it
Don't (unless there really is no way around it) use INDIRECT
- It makes auditing a nightmare
Don't merge and center cells EVER
Don't use Excel's built in formats for graphs unless you want your output to look like it was designed by a chimp
good ones... ; - >
good ones... ; - >
A Couple More to think about
DO
Design your spreadsheet like a car i.e. seperate the engine from the driving compartment... Typically raw data, calculating platform/workspace and a nice clean display to control it from.
DO
Keep it as simple as possible for the user
DO
Add more remarks to your programming than you think you really need at the time.
DO
Avoid working directly (programmatically) on the worksheets if you can avoid it. Userforms and arrays are much faster and most users can't tinker with them.