# User:Lonny/Spreadsheet guidelines

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*

- assumptions and givens in
- 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.