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