Microsoft Excel: Rules for designing Excel workbooks

Q. Is there a list of standard Excel design rules we should be following as we create new Excel worksheets?

A. By following a common set of spreadsheet design rules, companies can produce more consistent workbooks that may be easier to review, edit, and use by others in their organizations. In this spirit, in 1987, I served on an AICPA committee that created a booklet titled Policies and Procedures for Standardizing Spreadsheet Design. Presented below are some of the suggestions from the committee’s work, updated for today’s technologies and including a few new suggestions of my own. (These suggestions apply more to workbooks that are frequently or heavily used; simple “scratch calculations” and “one-time only” workbooks would likely not benefit much from implementing the suggestions/measures set forth below.)

1. Documentation. Every workbook you create should contain a documentation page that details the title, creators, reviewers, the date created, the client(s) involved (if any), and a description of what the template is designed to accomplish (if it’s not obvious).

2. Table of contents. Larger workbooks may benefit from a table of contents page with links to the numerous worksheets, as suggested in the screenshot below.

3. Print macro buttons. Workbooks with numerous reports may benefit from including an assortment of print macro buttons for faster printing of various reports, or combinations of the reports, contained in the workbook, as suggested in the screenshot on the previous page.

4. Avoid embedded assumptions. Users should avoid embedding their assumptions in formulas for several reasons. Embedded assumptions don’t print to paper, so the reader can’t see the assumptions used. In addition, changing embedded assumptions is more difficult (and hence less efficient) when you must track down the location of each assumption and then edit the formulas to make the desired changes. It’s also more difficult to attach error-checking formulas to assumptions that are embedded in other formulas.

5. Well-organized worksheet assumptions. Many Excel worksheets involve assumptions, such as interest rates, inflation factors, revenue and expense amounts, etc. Novice Excel users tend to scatter their assumptions throughout their workbooks, which can make the worksheet more difficult to review and later modify. The better approach may be to list worksheet assumptions only once, in a well-labeled and well-organized list of assumptions. Thereafter, formulas used throughout the workbooks should reference those assumptions. An example of well-labeled, well-organized assumptions is pictured below.

6. Assumptions in yellow cells. I like to organize my assumptions further by highlighting each cell containing them in yellow with a black border, which makes it easier for me, as well as those I work with who are used to my design approach, to better follow my workbooks. An example of this is pictured above.

7. Name assumption cells. In some cases, it can be advantageous to label your assumptions using Excel’s Name Box tool. By naming the assumption cell, you can also reference the cell name when creating formulas instead of typing the cell reference. This can make the formula easier to write and review. Additionally, the Go To command (F5 key in Excel) can be used to quickly navigate to named ranges.

For example, if one assumption is the consumer price index (as pictured in cell B9 above), you can name the cell that contains that assumption CPI by selecting cell B9, and then in the Name Box typing CPI, and pressing Enter. An example of a resulting formula, which references the named cell CPI, is pictured below.

8. Error-checking formulas. To help prevent input errors, I like to include error-checking formulas next to my formulas when warranted. For example, if the workbook template provides cell B4 for entering an interest rate, users trying to enter, say, “5%” may not know whether to enter 5 or .05. To make sure users (including myself) get it right, I might include the error-checking formula =IF(B4>1,”Error – please use a decimal number, such as .02 instead of 2″,””) next to the assumption, as pictured below. This formula might alert users when a mistake has been made.

9. Organize your template by worksheets. When creating an Excel workbook, the sections of your work should be organized on different worksheets. For example, your assumptions may be organized on worksheet 1, your revenue calculations on worksheet 2, your expense calculations on worksheet 3, your income statement on worksheet 4, your balance sheet on worksheet 5, etc. This approach makes it easy to tab between each section and also makes it easier to print each section. Years ago, when spreadsheet applications provided only a single worksheet, extra effort was required to select print ranges from the single worksheet and force the pages to break at the desired locations. However, when your workbook is organized by worksheets, printing each section is simply a matter of selecting the desired worksheets and pressing the Print command. An example of a workbook with organized worksheet tabs is pictured directly below.

10. Simplify complex calculations. When faced with the need to create a complicated formula involving numerous functions and/or cell references, consider breaking that formula into a series of simplified calculations across multiple cells. The result will be formulas that are easier to troubleshoot and a worksheet that is easier to review.

11. Explanations. When the situation calls for an explanation, it can be advantageous to insert a text box or balloon callout in your worksheet to explain the underlying methodology or simply to provide instructions for using it. This can be done from the Insert tab by selecting Shapes, and then in the Basic Shapes section, select Text Box, or in the Callouts section, select Speech Bubble. Not only can these explanations be useful to others, but also I’ve found them useful years later as they remind me of how the worksheet is intended to be used. An example is pictured below.

12. Consistent look and feel. I prefer to turn off the gridlines in my Excel workbooks because I find they clutter the screen. If I want gridlines in specific areas, such as in my tables, I add them using the Borders formatting tool. I also prefer to use a standard 12-point Calibri font for all my workbooks because that font has been designed to be more readable for both text and numbers. To learn more about the Calibri font, see the April 2017 topic “What Fonts Work Best in Excel?“.

13. Add File Properties. In some cases, it may be worthwhile to edit the Excel workbook’s File Properties, so users can locate and identify the file more easily in searches. To do this, select the File tab, Properties, Advanced Properties, and on the Summary tab, add Keywords, Comments, and other information that might make the file easier to locate (see below).

14. Cross-footing and error-checking formulas. Whenever possible, well-labeled cross-footing totals and/or error-checking formulas should be included in the worksheet. For example, a simple IF function might be used to check if the balance sheet balances; if not, then the error-checking formula should display a message warning the user that the worksheet has a problem.

15. Worksheet protection. In some cases, it may be advantageous to turn on worksheet protection to secure the integrity of a completed workbook. To do so, first select each assumption cell in the worksheet (one at a time or all at once by holding down the Ctrl key to select multiple assumption cells), then right-click one of the selected cells, select Format Cells from the pop-up menu, and then, on the Protection tab, uncheck the box labeled Locked, as pictured below. (The previous measure of highlighting assumption cells in yellow will make it easier to identify them for unlocking purposes.)

Next, from the Review tab, select the Protect Sheet option, enter a password (or you can leave the password field blank to protect the worksheet with no password), and then click OK. Thereafter, changes can only be made to those cells that have been unlocked. To fully modify the worksheet later, you will need to turn worksheet protection off from the Review tab by selecting Unprotect Sheet, and entering the password, if necessary. Perhaps the best reason to protect your worksheet is to prevent accidentally deleting data. Consider the scenario in which your doughnut falls out of your hand and bounces off the spacebar and onto the floor. You don’t notice that data have been deleted from your worksheet because you are on the ground hurrying to obey the five-second rule. In this case, worksheet protection will protect your data but not your doughnut.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.