Resources

18 Spreadsheet Best Practices

18 Spreadsheet Best Practices

Download PDF

Navigation & Consistency

1. Make Workbooks Easy to Use

If a workbook includes more than one sheet, be sure it also includes: 1) a table of contents sheet listing all the sections and sheets in the workbook; 2) hyperlinks from the table of contents to every sheet in the workbook; and 3) a hyperlink to the table of contents always in view on every sheet in the workbook.

2. Make Sheets of the Same Type Consistent

In each workbook, use a consistent structure and format for all sheets of the same type. Be consistent in sheet titles, styles, and positioning; heading styles and space; column and row dimensions; hyperlink positioning; viewing properties; formats and colors, etc.

3. Align Data Consistently

On each sheet, align all data of the type consistently either down rows or across columns.

4. Make Formulas Consistent

When more than one adjacent cell contains a similar type of output the structure and components of the formulas within the cells should always be consistent, so that the cell can be copied across/down the relevant range without needing to make changes.

5. Use Frozen Cells

Use frozen cells on every sheet to ensure that sheet titles, hyperlinks, check indicator flags, and other critical elements are always in view.

Guys Holding Question Mark Cards

Assumptions

6. Isolate Assumptions

Enter all assumptions on separate sheets that are easily identified as containing only assumptions.

7. Avoid Redundant Assumptions

Do not enter any assumption more than once. Each assumption should have a single entry.

Hello_My_Name_Is

Naming

8. Give Workbooks Meaningful Names

Use a naming scheme that allows each workbook to be 1) distinguished from all other workbooks in your organization, and 2) identified by its version.

9. Name Every Sheet

Give each sheet a name that clearly communicates the type of information it contains.

10. Give Ranges Descriptive Names

Give every range a name that identifies its content or its use.

Formula

Formulas

11. Make Formulas as Simple as Possible

Avoid overly complex formulas that might introduce errors or lead to misunderstandings.

12. Avoid Constants in Formulas

Constants in formulas are difficult to manage and keep current. Keep contansts on a separate sheet to clearly communicate and simplify management.

13. Avoid Merging Cells

Avoid using “Merge Cells.” If you must, use it with caution. “Merge Cells” makes it easy to accidentally copy formulas incorrectly or to misalign elements in a spreadsheet.

Pile of papers

Management

14. Include an Error Checks Summary

Set up workbooks so that the outcome of errors checks is visible in a separate, dedicated summary.

15. Be Systematic about Versioning, Backup, and Storage

Create a backup, versioning, and storage system for spreadsheets that is consistent across your organization.

16. Maintain an inventory of all spreadsheets

Maintain a list of all spreadsheets or use an automated solution to automatically discover all the spreadsheets in your organization.

17. Automate Quality Control

Run automated error checking software inside Excel to locate formula problems, discover hidden information, make spreadsheet structure visible, and ensure that external references are complete and accessible.

18. Monitor Risk

Routinely assess spreadsheets for risk. Rank sheets for risk to identify potential concerns via a manual audit or automated process.


Additional Resources


Managing spreadsheets, reducing risk and gaining confidence

Learn how to make spreadsheets work for your organization with this Incisive whitepaper.

Download white paper


Infographic: Six tips for building the best spreadsheet game plan

A primer on how to manage a critical spreadsheet game plan for your organization.

View infographic