My Golden Rule:
Can you explain the workbook to a ten-year-old?
1. Know your audience.
Detailed: Adjust the layout, documentation, and navigation to the skills and familiarity of downstream users.
Question: “Will my teammates be able to understand the purpose of this workbook, be able to follow the logic, and understand what the summary represents?”
2. Focus on the spreadsheet’s purpose.
Detailed: Consider whether Excel is the right tool for the job. Build an understanding of whether you should choose Excel/Access/Word/OneNote for each specific project. When in doubt… Google it!
Question: “Will this workbook be the most efficient and accurate tool to handle the task at hand?”
3. Have a plan for your workbook.
Detailed: Consider the uses for the workbook and add the level of detail that supports the purpose.
Question: “Do I need to spend a large amount of time on formatting if this is a single-use workbook?” How often will this workbook be used, for what purposed, and by whom?”
4. Using fewer workbooks to do more things.
Detailed: Think of Goldilocks and the Three Bears; don’t add too little information, don’t add too much, focus on “just right.” The result may be appending each month’s data to create a more meaningful analysis rather than 12 standalone reports.
Question: “Does my workbook provide value as a standalone resource?” “Does this workbook answer all questions about X?”
5. Ask for feedback.
Detailed: Ask other people to review your work. The second pair of eyes can catch errors, find improvements, and test the effectiveness of a workbook.
Question: “Can others use and understand my workbook? Can they follow the flow and reasoning? Are there better ways of doing X?”
6. Create an About Me or instructional sheet in the workbook.
Detailed: Use the first sheet of a workbook to document the purpose of the workbook. Include instructional notes and resources that will help a 3rd person replicate the process.
Question: “If my teammate sat in my chair, can they replicate the same results using my instructions? Would they understand the purpose of the workbook?”
7. Separate data, calculations, and summaries into separate worksheets within a workbook.
Detailed: Creating three distinct sections in a workbook enhances clarity, improves the performance of calculations, & reduces errors.
Question: “Is the source data clear and unmodified? Is the summary clear and impactful? Are the calculations transparent and accurate?”
8. Use formatting to highlight relevant information.
Detailed: Formatting is a great feature in Excel that helps to segregate information and draw attention to specific areas. Use shading, borders, and font size to keep your audience focused.
Question: “Is it clear where the audience needs to focus their attention?” “Is my formatting communicating a consistent message?”
9. Aim for simplicity.
Detailed: The goal should be to limit the amount of complexity within the layout, the formulas, and the number of worksheets. Complexity increases the opportunity to create errors and reduce auditability.
Question: “Is there a simpler way for me to communicate the same message or reach the same results?”
10. Test the workbook.
Detailed: Build and test a workbook that will be able to handle various circumstances. Try using several different weeks or months of data to ensure that changes in the information will provide accurate results.
Question: “Will the design and contents of this workbook be able to produce accurate results each time?”
11. Overview: Build in checks, controls, and alerts throughout each workbook.
Detailed: Build in controls that compare the raw data totals to the summary results. Use data validation to alert you to #N/A reference errors. Tie outs should be in place to make sure that information isn’t lost or omitted.
Question: “Are processes in place to ensure data integrity and accuracy each time?”
12. Overview: Never hardcode in place of a formula.
Detailed: Data should be entered once, and the other parts of the spreadsheet should reference that data. Changing a few assumptions should make the spreadsheet dynamically adapt.
Question: “Is the data entry spreadsheet the only place where raw data is being entered and are all calculations handled by Excel formulas?”
13. Overview: Design for the long-term.
Detailed: Workbooks should be designed as engines whereas data is the fuel. When new data is added, the machine should be able to adapt and summarize the results easily. Key assumptions such as exchange rates should be dynamic.
Question: “Will this workbook meet today’s needs and require little/if any modification to work tomorrow?
14. Overview: Keep formulas short and simple.
Detailed: Each cell should only hold one formula. Use references to previous calculations as a way to ensure calculation accuracy.
Question: “Is my formula and workflow process easily understood?”
15. Overview: Never link workbooks.
Detailed: Moving a linked workbook from its original location, changing workbook or file pathnames, or changing the location of data of the original spreadsheet will invalidate all formulas connected to this data.
Question: “Do I enjoy being tortured and/or causing deep mental anguish to others?”
Updated: 6/7/2023
