18 Spreadsheet Best Practices
18 Spreadsheet Best Practices
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.
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.
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.
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.
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.
Managing spreadsheets, reducing risk and gaining confidence
Learn how to make spreadsheets work for your organization with this Incisive whitepaper.