Inputs, Processing, Data Transformation, and Outputs
Separate the calculations from the inputs and outputs, and apply the formatting described above.  Also, perform the following:

• Limit the width of data tables to one screen length
• Omit unnecessary columns or text
• Omit hidden columns and the use of macros to show/hide columns
• Inputs, calculations and outputs should be separated physically on the sheet and with clear labeling and color (i.e., highlighted)

Named Ranges
All tables will include a specific named range.  Named ranges should be defined in all spreadsheets for each data structure that makes up a table and use the standard naming convention.  See further information on named ranges and naming conventions at the end of this document.

Importing Data / Data Transformation
Data imported from applications other than Excel (e.g. Crystal Reports, COGNOS, F9) should be included on a separate input tab with distinct style.

Reading the Table
The table should be read left to right and top to bottom.  The spreadsheet should always be built so that the long way goes down in the spreadsheet.  Since most spreadsheets have more rows than columns, and since we prefer to read by paging down, the spreadsheet will be more readable if it’s aligned vertically. 

Input Parameters in Named Ranges
All spreadsheets should set their input parameters in named ranges so that they can be referenced in multiple locations in the spreadsheets.  Input parameters are typically used with importing data / data transformations and are centralized on the spreadsheet.  The following attributes are assigned to them:

• A common background color (i.e. “white”).
• The title, in full text, that describes what the parameter represents (i.e. “Month used as a basis for the analysis”)
• A definition of what constitutes a valid input value (i.e. “requires a date from 1/1/2007 to 12/31/2007”)
• A definition of the source for the input parameter (i.e. “obtained from the person requiring the report”)
• Has a defined name (see information on “named ranges”).

Worksheet protection
Protect your worksheets by locking all of cells except those that require data entry.  Since data entry cells all have the same appearance – a white background – it will be easy to see which cells are meant to be changed and which ones are calculated values.  This prevents critical formulae from being accidentally changed.  Restrict the area of the worksheet within which the user can navigate.  The spreadsheet owner should be the holder of the password to unlock and can publish the password on the cover page if the information is not confidential.  Excel allows the writer to password protect the spreadsheet in such a way that changes can be tracked by others in a shared spreadsheet.  An organization using many spreadsheets might consider implementing software to manage worksheet protection.

Connecting worksheets
There are two common methods for consolidating data or connecting worksheets:

• using multiple sheets within a single spreadsheet;
• using separate spreadsheets with external links between them.  If external links are used, then the following must be performed:
  • Input external links (coming from somewhere else) will be clearly identified and included on an input tab.  Styles should be used to clearly identify the information.
  • Output external links (where data is going) will be clearly identified and included on an output tab.  Styles should be used to clearly identify the information.

Information regarding connections in to / out of worksheet will be included in the cover page of the spreadsheet.

NOTE:  Don’t link to uncontrolled spreadsheets.

Using Macros
Use of macros should be avoided unless they are the only alternative.  In complex situations, macros may be the only alternative.  If used, macros must be defined in the table of the worksheet or within the cover page.

Changing Time

Design the spreadsheet model around the shortest time period required. Challenge whether changing time periods is actually necessary in the worksheet. Quarterly time periods may be used on one worksheet as a short term model and the annual amounts will be included as a separate long term planning.

For example, if you are required to produce output in annual terms, you can perform the calculations quarterly, and then consolidate on a separate sheet for annual results. This will ensure that the underlying model has consistent logical assumptions across all of the time periods.

Print Area
Pre-set print areas within each worksheet to facilitate ease of printing.  Similar to any reporting function, only necessary input, calculation and output information should be included in the print area.  Print areas should be defined with a style, such as a border.