Smart Workbook Design

Although this book focuses on the VBA aspect of developing Excel applications, this chapter wouldn't be complete without mentioning some practical advice regarding the general layout of an Excel application. In Chapter 22, I describe various application-distribution strategies and techniques such as templates, add-ins, and standard Excel workbooks. For the purposes of this section, I'm assuming that you're developing a standard Excel workbook. By standard Excel workbook, I mean an application whose "life-form" is a standard Excel workbook or file.

There are many schools of thought on the "proper" way to lay out or design a spreadsheet. Over the course of my career, I've experimented with many different strategies to design a spreadsheet. So if you want to know the best approach to designing a robust, efficient, and easy-to-maintain spreadsheet, I can say with 100 percent confidence that it depends. Among other things, it depends on the ultimate purpose of the spreadsheet, the potential value of the spreadsheet versus the extra time and effort (costs) required to implement the various characteristics, and the amount of data being analyzed. For example, sometimes you'll value clarity above everything else, other times ease-of-use will be your most important design objective. The best approach for designing and building the spreadsheet depends on your prioritization of the various design objectives.

That said, I believe most of the following design tips should apply to nearly every spreadsheet.

Spreadsheets should flow top to bottom, left to right. This seems natural, right? Almost too obvious to include here? However, an important point is implicit in this statement that I need to point out. Formulas in cells should refer only to cells that are upstream from them. So a formula in cell B10 should refer only to a cell in the range A:B9 on the current worksheet or any cell in one of the previous worksheets (a worksheet to the left of the current worksheet, assuming that the worksheets are in order of the flow of the worksheet).

Separate input, calculations, and output. This is good advice for a few reasons. First, it helps facilitate discussion when you're constructing the spreadsheet. It's natural to think in terms of these concepts. What do I have/need? That is your input. Where do I need to go? That is your desired output or reports. How do I get there? These are your calculations. Second, it is easy to understand, document, and explain the final product. And finally, it facilitates many of the techniques mentioned within this section.

Keep time periods consistent between worksheets. If the columns in your workbook represent a time series and this time series is repeated between worksheets, make sure that like time periods occupy the same column on every worksheet. If January 2004 is column D on Sheetl, it should be column D on Sheet2. The benefits of this practice are that it makes it much easier to construct formulas during the build process, it facilitates formula auditing, and makes it easier to refer to individual time periods programmatically.

Avoid blank columns. One common practice is to insert blank columns in between sections of data to create visual separation between the sections. This is especially true if each column represents a period of time in a time series. For example, if you have two years of data by month, do not insert an empty column between each year of data. You can create the desired separation using column widths and cell indenting. I prefer to keep all of the months as one unit and then show summaries (such as by quarter or by year) in the columns to the right of the data range. The benefit is that it is easier to create and audit formulas and easier to work with the data programmatically because you don't have to worry about blank columns or columns that represent summarized data. This strategy is easiest to implement when you break a workbook into input, calculation, and output sections. Assuming the monthly time series scenario, usually the only time that you need to display summaries (by quarter or by year) is on the output or reports.

Don't scale numbers using formulas. In Chapter 10, I demonstrated that you can easily display scaled values using a number format rather than physically adjusting the true value of the number (such as multiplying by 0.001 or dividing by 1000).

Monster formulas are not cool. Earlier in my career, I thought that the ability to create huge, complex formulas was a way to prove how "advanced" my Excel skills were. Big mistake. It is much better for everyone, including yourself, to use a series of separate, intermediate calculations. If you follow the input, calculation, and output theme just discussed, using a few extra columns or rows to perform intermediate calculations shouldn't be an issue. Calculation worksheets are just that— places in a workbook for calculation purposes only. In order to get the most bang for the buck, you should lay out calculation sections in a manner that facilitates understanding and auditing. Unless you have more than 65,536 rows or 256 columns, why consolidate intermediate calculations into one mega formula? It is true that you'll get a marginal benefit in calculation time; however, this benefit comes at a huge cost, in my opinion.

As I alluded to earlier, one size doesn't fit all. If I had to try and make one size fit all, I'd use the one highlevel design that seems to work for me more times than not. This design includes a menu worksheet, an input worksheet(s), a calculation worksheet(s), a report worksheet(s), and the Settings worksheet used by the Setting and Settings classes presented in Chapter 12.

By default, the menu worksheet is what is displayed when the workbook is opened. Giving a nod to the value of the first impression, the menu worksheet should be visually pleasing and have a little "WOW!" aspect to it. The menu worksheet can serve numerous purposes. If the application doesn't use its own Excel menu or toolbar, the menu worksheet serves as a switchboard to help users navigate to various sections of the workbook. Even if the workbook does have a more sophisticated user interface, the menu can provide an additional way to navigate to other locations in the workbook. It could also be used to navigate to external documents.

Another possible function of the menu worksheet is to collect high-level user information. For example, in a budgeting application, the menu worksheet might be the location where the user enters his name and the name of the entity or department that he is creating a budget for.

In addition, I feel that the menu should, if possible, convey the overall message that the workbook aims to convey. For example, in a budgeting workbook, you may include a small graph on the menu worksheet that shows next year's budget against this year's, along with a few key performance indicators (KPIs).

Finally, you can use the menu worksheet to display general application information such as the current version, version date, or other useful nuggets of information. In one application I built, the menu worksheet included dynamic comments retrieved from a database that notified users of important information related to the model. Figure 13.5 shows an example of a menu worksheet.

After the menu worksheet, I'll have one or more input worksheets. Input can come from multiple places including databases, text files, other Excel workbooks, user input, or an XML file. I like to keep each set of data on a separate worksheet. This makes things easier to work with programmatically.

Input worksheets should be the raw input data and nothing else. Perform any supporting calculations on separate worksheets. The calculation worksheets should be laid out in a manner that facilitates understanding. Don't worry about eye candy when it comes to calculation worksheets. Calculation worksheets can be kept hidden and displayed only when necessary.

The next set of worksheets represents the output or reports. These worksheets are organized in a manner that conveys the information that the spreadsheet was designed to convey. Output worksheets should be well formatted and print ready.

The last worksheet in the workbook is the Settings worksheet. This worksheet stores any configurable settings used in the workbook. I usually use the Settings worksheet in conjunction with the Setting and Settings classes presented in Chapter 12.

Figure 13.5

I like to think of the menu worksheet as a portal to the rest of the workbook.

Figure 13.5

I like to think of the menu worksheet as a portal to the rest of the workbook.

Workbook Design
+1 0

Responses

  • miia
    What are some different ways you can use a worksheet within a workbook?
    7 years ago

Post a comment