Spreadsheet good practice guidelines

Overview

About these guidelines

This is a list of good practice guidelines for building and using spreadsheets. The guidelines are structured in the table below as links to short articles, grouped by category and section.

The first few articles describe the purpose of these guidelines, along with stating why good practice matters and broadly defining what we need to do about poor spreadsheet practice. Each good practice guideline is defined as a bullet point, supported by explanations and examples.

More detailed introduction

For a more detailed introduction to these guidelines, see the blog article Introducing our spreadsheet good practice guidelines.

List of guidelines

Category	Section	Guidelines
Purpose of these guidelines	Target audience	Target audience: Everyone who uses spreadsheets Understand why spreadsheet good practice matters Learn how to make better spreadsheets
Why good practice matters	Basis for these guidelines	These guidelines are based on academic research and practical experience
	Spreadsheets are not as simple as we think	Treating spreadsheets as a simple tool is a mistake Recognize that even small spreadsheets have risks
	Spreadsheet risk is real, with substantial impacts	Use good spreadsheet practices to avoid horror stories
	Why spreadsheets have errors	We treat spreadsheet results as "the truth", generally without justification The problem is partly the way spreadsheets work, but mostly it is us Overconfidence is the biggest issue
	95% of spreadsheets have errors	Research shows that 95% of spreadsheets have errors Humans error rate is 1% to 5% per task, but spreadsheet errors cascade
What we need to do	Excel formulae are a programming language	Spreadsheet formulae are a form of programming language Software development issues and lessons apply to spreadsheets
	Learn from software developers	We need to apply software engineering principles to spreadsheets Rules are too rigid, so we define guidelines
	Spreadsheet users must recognize the risk	Feedback from testing your spreadsheets can be sobering Training is an effective way to reduce overconfidence
	Managers must recognize the risk	A manager's role is to mitigate spreadsheet risk and overconfidence Apply a positive feedback mechanism to reinforce and improve spreadsheet practice
Spreadsheet Development Life Cycle	Understand the life cycle of spreadsheets	Understand the spreadsheet development life cycle
	Manage the life cycle	Manage the spreadsheet development life cycle
Organizing workbooks	Folder and filename conventions	Store related workbooks in a folder hierarchy Use a consistent filename convention Filenames with dates use yyyy-mm[-dd] format
	Divide content into modules	Divide a workbook into logical modules Modules are clearly separated and labelled The modules have a flow that fits the context
	Be consistent	Use a consistent structure and orientation for similar content Have consistent styles throughout the workbook Terminology is used consistently Highlight inconsistent formulae in a block
	Worksheet headings	Every worksheet has a heading All worksheet headings have the same style
	Remove empty sheets	The workbook has no empty worksheets or ChartSheets Before removing, ensure that a sheet is unused as deletion cannot be undone
	Worksheet flow	Content flows logically from top-left to bottom-right Keep related content close together Prefer having a short arc of precedence Highlight deviations from the general flow
Data	Hard-coded data in formulae	Values are not hard-coded in formulae
	Give each special data value a name	Constants, symbols, and other special data values are in named ranges Scale factors are not embedded in formulae
	Data clones	Each input, assumption, and data value is entered in only one location If values are copied, then clearly mark the copies
Formulae	Keep formulae simple	Formulae are short, simple, and easy to understand Formulae are not atomised to the extent that they become difficult to understand in aggregate
	Spaces in formulae	Avoid using spaces and new lines in formulae Any spaces and new lines in formulae do not cause unintended side-effects
Formatting	Merged cells	There are no merged cells Use alternatives to merged cells, such as Center Across Selection or a Text Box

Summary of categories

Purpose of these guidelines Articles: 1

Why good practice matters Articles: 5

What we need to do Articles: 4

Spreadsheet Development Life Cycle Articles: 2

Organizing workbooks Articles: 6

Data Articles: 3

Formulae Articles: 2

Formatting Articles: 1

Essential reading

Latest articles

Spreadsheets as a source of analytical and accounting problems

Self-checks in spreadsheets: A survey of current practice

The development of a structured methodology for the construction and integrity control of spreadsheet models

Refactoring meets spreadsheet formulas

Simple spreadsheet test case application to test spreadsheet formula in end-user software engineering

Proper VBA error handling

VBA Like operator – using wildcards in conditional statements

Count cells with text and formula – Excel stats

VBA: The VBA array

Excel Tables

An innovative spreadsheet authoring environment

Consulting