User:Lonny/Spreadsheet guidelines

From Appropedia
Jump to navigation Jump to search

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

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

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

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

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

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

  • 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.