These guidelines are considered “best practices” and deviations from them should be considered in context – remember, all spreadsheets should be simple, easy-to-read, and flexible.

Use one formula per row or column
Formula should be written so that a single formula can be copied across the entire row of calculations, or down the entire column.   You should not have two different formulae in each row of the spreadsheet.  Formulae typically change at the point where the time periods change.

Flow of Calculations
Worksheets and tables will be arranged so that the flow of calculations is obvious with the labels and the use of styles (i.e., color coding).

Changing Time Periods
Ensure that formulae remain the same with respect to the time periods used in the calculations thus decreasing the amounts of errors.   

Simple Formulae
Keep formulae as simple as possible.  Make the formulae clear and concise as though you’re re-writing a long sentence that requires revision. Formulae should be highlighted on the sheets with styles.  If you need to express complex relationships, create several formulae like building blocks instead of constructing a long formula.

Define Formulae
Formulae will be defined on each worksheet in a separate column in the table.

External Spreadsheet Links
Don’t use them.

Range Names
Use range names to write more meaningful formula.  See Section below “Range Names” for more detail.

Numbers in Formulae
Manually entered numbers (actual data) should not be used in formulae. Instead, use cell references or range names that point to the cells containing the data.

Relative and Absolute References
Formulae may contain both absolute and relative cell referencing.   Formulae containing cell references must use defined names and range names rather than cell referencing.  See information on “Defined Names / Range Names” below.

• Absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula.   An absolute cell reference takes the form $A$1.)
• Relative cell reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row)

No Circular references
The best way to avoid a circular reference in your specification is to construct your calculation rules so that they always read from top to bottom and left to right: so that formulae always refer to cells above or to the left.

Lookups and DSums
If you find your spreadsheet to require lookups and DSums, chances are good that you should be using a proper database for your model.  Depreciate your use of these functions in Excel.