Here are some general guidelines to making usable spreadsheets. Especially for students in Lonny Grafman's courses. Please note that this is a general guideline to making calculation spreadsheets. It is not a guideline for other type of spreadsheet, such as a monthly schedule. In addition, there are many ways to do calculation spreadsheets and your specific calculation may have different needs, especially aesthetically.
Assumptions and conversions
Assumptions and conversions should:
- live in their own area on the sheet (usually the upper-right or its own sheet) or on a sheet of their own
- have the value in one cell and the units in the adjacent right cell (e.g. 1000 in one cell and W/kW in the next cell)
- consider naming the value cell so that you will not need to use $ signs when dragging formulae that reference this cell
- be stated clearly and referenced/cited from a credible source
- use comments, or the cell directly right of assumption (this is slightly less aesthetic, but more accessible), for reference info
Information
- data input should be thorough, complete, accurate and discussed (how did you get the data)
- assumptions should be clearly stated (see #Assumptions and conversions)
- calculated results should be checked against reality
Calculations
- should be your own formula or referenced
- should be general, consistent and dragable (i.e. you should not need to type the formula over for each new row)
- should only contain functions and cell names (Do not put any numbers in any formula).
- should be as logically arranged as possible (i.e. a formula should reference values and results above, not below, its position)
Aesthetics
- rows should all be same height, unless conveying different value/information than other rows
- borders should be used sparingly and in order to clarify information (consider half-bright borders)
- colors and font effects should be used meaningfully, if using excel consider sticking with the excel styles for:
- assumptions and givens in Style: Explanatory
- user input cells in Style: Input (it is often best to place the main user input at the top of the page)
- calculations in Style: Calculation
- main Outputs in Style: Output
- a printed version should make sense as either 1 page wide by some number of pages long, or 1 page long by some number of pages wide
Other Organization
- for large calculations, consider multiple, named, sheets:
- one sheet for instructions
- one sheet for user inputs and outputs
- sheets for each specific sub-calculations
- one sheet for assumptions and conversions
- possible separate sheets for data sets
Other
- spelling still matters
- if someone else is going to use this, include clear instructions... even if that someone else is just you in the far future :)
- niftyness is sometimes useful, e.g. drag down lists that are used with Vlookup to pull in values from a separate data sheet
You can see an example large spreadsheet at File:Arcata Plastic Bags.xls. Would an example small calculation spreadsheet be useful? Let User:Lonny know!