Courses/Computer Science/CPSC 203/CPSC 203 2008Winter L03/CPSC 203 2008Winter L03 Labs/MiniTutorial: Spreadsheet Design

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
 * 4) location (cell range),
 * 5) name,
 * 6) the Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
 * 7) Data Type (e.g. Integer, Text, Currency, Date, etc.) and
 * 8) Description (a description of the data or what it's 'purpose' is).
 * 9) Raw Data. Present your raw data in tabular form -- with columns representing variables and rows representing cases.
 * 10) Calculated Data.
 * 11) Summary Statistics --Usually Summary statistics result from calculations across rows for a single column.
 * 12) Derived variables are often based on calculations across columns for a row.
 * 13) Presentation' (Reporting)
 * 14) Emphasize the final information you wish to show without excessive background details.
 * 15) Use charts wherever appropriate to summarize large volumes of data