Microsoft Excel — Rules for Designing Excel Workbooks? — Part 1
Every Situation is Different, But Here Are Some Useful Guidelines!
In this post and future posts, I will be exploring selected items from the list below and HOW I implement them in my Excel development and spreadsheet work.
Why Rules for Spreadsheet design?
Every organization I have worked for, or consulted with, uses Microsoft Excel in their workflow.
Yet, there is typically no standard approach to designing and laying out a spreadsheet.
This is important for several obvious reasons, among them are:
- Organization — documentation and audit “trail”-the more complex the spreadsheet becomes, the more critical documentation is to the process.
- Ease of use —for you and others (can others pick up the spreadsheet and use it?)
- Data and formula integrity — “hard coding” data in formulas, assumptions scattered throughout the workbook, etc. are not only inefficient, but can make the spreadsheet unreliable and error prone.
- Ease of review — can the spreadsheet be easily reviewed by others for understanding and review purposes?
Where Do I Start?
In the June 2017 Journal of Accountancy, the following question was posed:
“ Q. Is there a list of standard Excel design rules we should be following as we create new Excel worksheets?”
J. Carlton Collins, CPA, a JofA contributing editor, addressed this topic in his monthly “Technology Q&A”. His response, in my opinion, is a helpful “framework” to use whenever you develop spreadsheets.
Here are his 15 “design rules” to consider — which range from very important to “preference” items:
Organization
1 — Documentation
2 — Table of Contents (see below)
5 — Well-organized worksheet assumptions
9 — Organize your template by worksheets
11 — Explanations
13 — Add file properties
Ease of Use
3 — Print macro buttons
4 — Avoid embedded assumptions
6 — Assumptions in yellow cells
7 — Name assumption cells
12 — Consistent look and feel
Spreadsheet Data and Formula Integrity
8 — Error-checking formulas
10 — Simplify complex calculations
14 — Cross-footing and error-checking formulas
15 — Worksheet protection
Consider the Table of Contents
I will be exploring selected items from the list and HOW I implement them in my Excel development and spreadsheet work.
I have previously written two posts about creating a worksheet “navigation” index. My approach to the recommended Table of Contents suggestion is a little more comprehensive, but very user friendly and an absolute time saver as your Excel workbooks get more complex!
In my next post, I will explore “2 — Print macro buttons”. Don’t be intimidated — once you get started with the basics, you develop quickly!
Good luck!
