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!

Cookies help us deliver our services. By using our services, you agree to our use of cookies.