Courses/Computer Science/CPSC 203/CPSC 203 2007Summer L60/CPSC 203 2007Summer L60 Labs/Lab 2

Mini-Tutorial: Spreadsheet Design
Below are some general rules for building a well designed spreadsheet.

Some Basic Rules about Design

 * 1) Design the spreadsheet on paper first. Graph paper often works well.
 * 2) Test and edit your calculations. Where appropriate use intermediate calculations and check-sums to ensure calculations are correct.
 * 3) Keep the components of a calculation visible. No "magic numbers". Place fixed numbers used in a calculation in their own cell with a descriptive title.
 * 4) Be aware of the "space" or "geography" of the spreadsheet. Arrange your information so that it is well spaced and easy to take in at a glance.

Parts of a Well Designed Spreadsheet
These parts can be considered components of a disciplined approach to building spreadsheet so they are self-documenting. The parts could each be in their own sheet, or they could be in a single sheet.


 * 1) Introduction -- What is this spreadsheet or workbook about. Note the title, purpose, author, creation and revision dates etc.
 * 2) Model and Assumptions. Justify any models, summary statistics, or calculated variables you are using.
 * 3) Data Dictionary. For every variable in the spreadsheet note: its location (cell range), name, the type of data it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.), Field Format (e.g. Integer, Text, Currency, Date, etc.).
 * 4) Raw Data. Present your raw data in tabular form -- with columns representing variables and rows representing cases.
 * 5) Calculated Data.
 * 6) Summary Statistics --Usually Summary statistics result from calculations across rows for a single column.
 * 7) Derived variables are often based on calculations across columns for a row.
 * 8) Presentation' (Reporting)
 * 9) Emphasize the final information you wish to show without excessive background details.
 * 10) Use charts wherever appropriate to summarize large volumes of data

Resources
The Elements of Spreadsheet Style. 1986. By J.M. Nevison