User:Lonny/Spreadsheet guidelines   

Here are some general guidelines to making usable spreadsheets. Especially for students in Lonny Grafman's courses. Please note that this is a very general guideline to making spreadsheets for specific calculations. It is not for using a spreadsheet for other documents, such as a monthly schedule. In addition, there are many ways to do calculation spreadsheets and your specific calculation may call for a lot of specifically aesthetic divergence from these guidelines.

Contents 1 Assumptions and conversions 2 Information 3 Calculations 4 Aesthetics 5 Other Organization 6 Other

[edit] Assumptions and conversions

• should live in their own area on the sheet (usually the upper-right or its own sheet)
• 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
• state clearly and referenced from a credible source
  • use comment for reference info, or the cell directly right of assumption for reference info

[edit] Information

• your data should be thorough, complete and accurate and discussed (how did you get the data)
• clearly stated assumptions (see Assumptions)
• your answer should be checked against reality

[edit] 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 chronological as possible (i.e. a formula should reference values and results above, not below, its position)

[edit] Aesthetics

• rows should all be same height, unless conveying very different information than other rows
• borders should be used 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

[edit] 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

[edit] Other

• spelling still matters
• if someone else is going to use this, include clear instructions
• 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? yes