Do …

Follow your plan
Begin each spreadsheet with an end in mind.  Sketch out spreadsheet designs on paper before opening Excel so that you have sufficient understanding of the spreadsheet’s purpose. 

Use effective tabular structure

• Inputs, Processing and Outputs should be on separate tabs of information
• Limit the number of tabs used in a spreadsheet – make additional spreadsheets if necessary.
• Pre-set print areas to facilitate ease of printing

Use effective table structure
A well-designed spreadsheet typically has one data table per tab.   For example, a tab would have a table that calculates Ceded Core Premium, and another tab with a table that calculates the Journal Entry for Ceded Premiums.    Though there may be exceptions to this guideline, they should be considered only if they promote the design of a simple, easy-to-use, flexible spreadsheet.

Color-code cells by purpose
Use descriptive formatting based on the purpose of each cell so that others can recognize the cells purpose. Use formatting to create visual contrast between cells designed to serve different purposes—input cells versus formula cells, for example—as well as visual separation between different sections of your user interface.

• Use Styles by purpose
• Color unused cells “black”
• Color formula and reference cells “grey”
• Color cells where the user types in data (inputs) “white”
• Color outputs in “blue”

Apply all formats consistently – for example, don’t format input cells with a white background in one area and a green background in another. 

Create logical data flow
Create a logical, well-structured flow through your user interface. Your user interface should flow from left to right then top to bottom within a worksheet and from left to right among multiple worksheets.

Use a naming convention
Spreadsheet names, tab titles, column headers, variable names are based upon a generally-accepted naming convention.  This convention uses short-descriptive names without spaces.  See additional details on “Naming Conventions” at the end of this document.

Include a cover page
All spreadsheets have a cover page that describes the spreadsheet’s purpose, intended audience, and distribution, and scope.

Make the spreadsheet easy to read
Make it obvious to users what they are supposed to do each time they are required to perform some action. Techniques for doing this include the use of cell comments, validation lists, default values, good descriptive field names and so on.  Styles and formats are used to identify row and column headers, input areas, formula results and areas that are outside the user interface.

Protect worksheets
Protect your worksheets, leaving only cells that require data entry unlocked. This prevents critical formulae from being accidentally overwritten.  A worksheet is designed to be protected, and once protected, the only cells that can be modified by the user are cells having the Input style (the style with the white background).

Label columns and rows
Use each column for the same purpose throughout the model However many worksheets you use, it is good practice to always use the same layout for columns on all worksheets.

Beware …

• Don’t use fixed ranges:  Use named ranges that are descriptive of the range contents instead.
• Beware of fixed offset lookup functions:  They are a hassle to maintain and are prone to errors.
• Beware of lengthy formulae:  They are difficult to understand, hard to read, and are prone to errors that can go undiagnosed.
• Don’t reference uncontrolled spreadsheets:  This exposed the newly-controlled spreadsheet to additional risks.