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!

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