Chapter 5: What Is a Spreadsheet Application?

IN THIS CHAPTER

• Getting a working definition of a spreadsheet application

• Understanding the difference between a spreadsheet user and a spreadsheet developer

• Classifying spreadsheet users to help you conceptualize the audience for your applications

• Discussing why people use spreadsheets

• Exploring a taxonomy of the basic types of spreadsheets

Spreadsheet Applications

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform useful work without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't really designed for others to use.

A good spreadsheet application has the following characteristics:

• It enables the end user to perform a task that he or she probably would not be able to do otherwise.

• It provides the appropriate solution to the problem. (A spreadsheet environment isn't always the optimal approach.)

• It accomplishes what it is supposed to do. This prerequisite may be obvious, but it's not at all uncommon for applications to fail this test.

• It produces accurate results and is free of bugs.

• It uses appropriate and efficient methods and algorithms to accomplish its job.

• It traps errors before the user is forced to deal with them.

Note that errors and bugs are not the same. Attempting to divide by zero is an error, whereas failure to identify that error before it occurs is a bug.

• It does not allow the user to delete or modify important components accidentally (or intentionally).

• Its user interface is clear and consistent so that the user always knows how to proceed.

• Its formulas, macros, and user interface elements are well documented, allowing for subsequent changes, if necessary.

• It is designed so that it can be modified in simple ways without making major changes. A basic fact of life is that a user's needs change over time.

• It has an easily accessible help system that provides useful information on at least the major procedures.

• It is designed to be portable and to run on any system that has the proper software (in this case, a copy of the appropriate version of Excel).

It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from simple fill-in-the-blank templates to extremely complex applications that use a custom interface and that may not even look like spreadsheets.

The Developer and the End User

I've already used the terms developer and end user, and you will see them frequently throughout this book. Because you've gotten this far, I think I can safely assume that you're either a spreadsheet application developer or a potential developer.

My definitions regarding developers and end users are simple. The person who creates the spreadsheet application is the developer. For joint projects, there are multiple developers: a development team. The person who uses the results of the developer's spreadsheet programming efforts is the end user (which I often shorten to simply user). In many cases, there will be multiple end users, and often the developer is one of the users.

Who are developers? What do they do?

I've spent about 20 years trading methodologies and hanging out (usually in a virtual manner online) with the motley crew of folks who call themselves spreadsheet developers. I divide them into two primary groups:

• Insiders are developers who are intimately involved with the users and thoroughly understand their needs. In many cases, these developers are also users of the application. Often, they develop an application in response to a particular problem.

• Outsiders are developers who are hired to produce a solution to a problem. In most cases, developers in this category are familiar with the business in general but not with the specifics of the application they are developing. In other cases, these developers are already employed by the company that requests the application (but they normally work in a different department).

Some developers devote all their time to development efforts. These developers may be either insiders or outsiders. A fair number of consultants (outsiders) make a decent living developing spreadsheet applications on a freelance basis.

Other spreadsheet developers don't work full time at the task and may not even realize they are developing spreadsheet applications. These developers are often office computer gurus who seem to know everything about computers and software. These folks often create spreadsheet applications as a way to make their lives easier — the time spent developing a well-designed application for others can often save hours of training time and can greatly reduce the time spent answering others' questions.

Spreadsheet developers are typically involved in the following activities, often performing most or all of each task on their own:

• Determining the needs of the user

• Planning an application that meets these needs

• Determining the most appropriate user interface

• Creating the spreadsheet, formulas, macros, and user interface

• Testing the application under all reasonable sets of conditions

• Making the application relatively user-friendly (often based on results from the testing)

• Making the application aesthetically appealing and intuitive

• Documenting the development effort

• Distributing the application to users

• Updating the application if and when it's necessary

I discuss these developer activities in more detail in Chapter 6.

Developers must have a thorough understanding of their development environment (in this case, Excel). And there's certainly a lot to know when it comes to Excel. Developing nontrivial spreadsheet applications with Excel requires an in-depth knowledge of formulas, functions, macros, custom dialog boxes, user interface elements, and add-ins. Most Excel users, of course, don't meet these qualifications and have no intention of ever learning these details — which brings me to the next topic: classifying spreadsheet users.

Classifying spreadsheet users

Over the years, I've found that it's often useful to classify people who use spreadsheets (including both developers and end users) along two dimensions: their degree of experience with spreadsheets and their interest in learning about spreadsheets.

To keep things simple, each of these two dimensions has three levels. These levels can be combined in nine combinations, which are shown in Table 5-1. In reality, only seven segments are worth thinking about because both moderately experienced and very experienced spreadsheet users generally have at least some interest in spreadsheets. (After all, that's what motivated them to get their experience.) Users who have a lot of spreadsheet experience and a low level of interest would make very bad developers.

Table 5-1: Classification of Spreadsheet Users by Experience and Interest

	No Interest	Moderately Interested	Very Interested
Little Experience	User	User	User/Potential Developer
Moderately Experienced	N/A	User	Developer
Very Experienced	N/A	User	Developer

It should be clear that spreadsheet developers must have a great deal of experience with spreadsheets as well as a high interest in spreadsheets. Those with little spreadsheet experience but a great deal of interest are potential developers. All they need is more experience. If you're reading this book, you probably fall into one of the boxes in the last column of the table.

The audience for spreadsheet applications

The remaining segments in the preceding table comprise spreadsheet end users, whom you can think of as the consumers of spreadsheet applications. When you develop a spreadsheet application for others to use, you need to know which of these groups of people will actually be using your application.

Users with little experience and no interest in learning more about spreadsheets make up a large percentage of all spreadsheet users, probably the largest group of all. These are the people who need to use a spreadsheet for their jobs but who view the spreadsheet simply as a means to an end. Typically, they know very little about computers and software, and they usually have no interest in learning anything more than what's required to get their work done. They might even feel a bit intimidated by computers. Often, these users don't even know which version of Excel they use, and they are largely unfamiliar with what it can do. Obviously, applications developed for this group must be user-friendly. By that I mean straightforward, unintimidating, easy to use, and as foolproof as possible.

From the developer's point of view, a more interesting group is comprised of users who have little or moderate spreadsheet experience but who are interested in learning more. These users understand the concept of formulas, use worksheet functions, and generally have a good idea of what the product is capable of doing. These users generally appreciate the work that you put into an application and are often impressed by your efforts. Even better, they'll often make excellent suggestions for improving your applications. Applications developed for this group should also be user-friendly, but they can also be more complex and customizable than applications designed for the less experienced and less interested groups.

Solving Problems with Excel

In the previous sections, I cover the basic concept of a spreadsheet application, discuss the end users and developers of such applications, and even attempt to figure out why people use spreadsheets at all. Now, it's time to take a look at the types of tasks that are appropriate for spreadsheet applications.

You may already have a good idea of the types of tasks for which you can use a spreadsheet. Traditionally, spreadsheet software has been used for numerical applications that are largely interactive. Corporate budgets are an excellent example of this interactivity. After the model has been set up (that is, after formulas have been developed), working with a budget is simply a matter of plugging in amounts and observing the bottom-line totals. Often, budgeters simply need to allocate fixed resources among various activities and present the results in a reasonably attractive (or at least legible) format. Excel, of course, is ideal for this scenario.

Budget-type problems, however, probably account for only a small percentage of your spreadsheet-development time. If you're like me, you've learned that uses for Excel can often extend well beyond the types of tasks for which spreadsheets were originally designed.

Here are just a few examples of nontraditional ways that you can use Excel:

• As a presentation device: For example, with minimal effort, you can create an attractive, interactive, on-screen slide show with only Excel. PowerPoint is a better choice, but Excel will do in a pinch.

• As a data-entry tool: For repetitive data-entry tasks, a spreadsheet is often the most efficient route to take. You can then export the data to a variety of formats for use in other programs.

• As a database manager: If you're dealing with a fairly small amount of data, you may find it much easier to manage it using Excel rather than a program like Access.

• As a forms generator: For creating attractive printed forms, many find it easier to use Excel's formatting capabilities than to learn a desktop publishing package.

• As a text processor: Excel's text functions and macro capability enable you to manipulate text in ways that are impossible using a word processor.

• As a platform for simple games: Clearly, Excel was not designed with gaming in mind. However, I've downloaded (and written) some interesting strategy games by using the tools found in Excel and other spreadsheets.

You can probably think of many more examples for this list.

Ironically, the versatility of spreadsheets is a double-edged sword. On one hand, it's tempting to try to use a spreadsheet for every problem that crops up. On the other hand, you'll often be spinning your wheels by trying to use a spreadsheet for a problem that's better suited for a different solution.

Basic Spreadsheet Types

In this section, I classify spreadsheets into several basic types to provide a better perspective on how spreadsheet applications fit into the overall scheme of things. This is all quite arbitrary, of course, and is based solely on my own experience. Moreover, the categories have quite a bit of overlap, but they cover most of the spreadsheets that I've seen and developed.

My names for these categories are as follows:

• Quick-and-dirty

• For-your-eyes-only

• Single-user applications

• Spaghetti applications

• Utility applications

• Add-ins that contain worksheet functions

• Single-block budgets

• What-if models

• Data storage and access

• Database front ends

• Turnkey applications

I discuss each of these categories in the following sections.

Quick-and-dirty spreadsheets

This is probably the most common type of spreadsheet. Most of the spreadsheets in this category are fairly small and are developed to quickly solve a problem or answer a question. Here's an example: You're about to buy a new car, and you want to figure out your monthly payment for various loan amounts. Or perhaps you need to generate a chart that shows your company's sales by month, so you quickly enter 12 values and whip out a chart, which you paste into your word processor.

In both of the preceding cases, you can probably input the entire model in a few minutes, and you certainly won't take the time to document your work. You probably won't even think of developing any macros or custom dialog boxes. In fact, you might not even deem these simple spreadsheets worthy of saving to disk. Obviously, spreadsheets in this category are not applications.

For-your-eyes-only spreadsheets

As the name implies, no one except you — the creator — will ever see or use the spreadsheets that fall into this category. An example of this type might be a file in which you keep information relevant to your income taxes. You open the file whenever a check comes in the mail, you incur an expense that can be justified as business, you buy tax-deductible Girl Scout cookies, and so on. Another example is a spreadsheet that you use to keep track of your employees' time records (sick leave, vacation, and so on).

Spreadsheets in this category differ from quick-and-dirty spreadsheets in that you use them more than once, so you save these spreadsheets to files. But, again, they're not worth spending a great deal of time on. You might apply some simple formatting, but that's about it. This type of spreadsheet also lacks any type of error detection because you understand how the formulas are set up; you know enough to avoid inputting data that will produce erroneous results. If an error does crop up, you immediately know what caused it.

Spreadsheets in this category don't qualify as applications, although they sometimes increase in sophistication over time.

Single-user applications

This is a spreadsheet application that only the developer uses, but its complexity extends beyond the spreadsheets in the for-your-eyes-only category. For example, I developed a workbook to keep track of registered users for my software applications. It started out as a simple worksheet database (for my eyes only), but then I realized that I could also use it to generate mailing labels and invoices. One day I spent an hour or so writing macros and then realized that I had converted this workbook from a for-your-eyes-only spreadsheet to a single-user application.

Creating single-user applications for yourself is an excellent way to get practice with Excel's developer's tools. For example, you can learn to create custom dialog boxes, modify the user interface, write Visual Basic for Applications (VBA) macros, and so on.

Working on a meaningful project (even if it's meaningful only to you) is the best way to learn advanced features in Excel — or any other software, for that matter.

Spaghetti applications

An all-too-common type of spreadsheet is what I call a spaghetti application. The term stems from the fact that the parts of the application are difficult to follow, much like a plate of spaghetti. Most of these spreadsheets begin life as a reasonably focused, single-user application. But over time, they're passed along to others who make their own modifications. As requirements change and employees come and go, new parts are added, and others are ignored. Before too long, the original purpose of the workbook may have been forgotten. The result is a file that is used frequently, but no one really understands exactly how it all works.

Everyone who's involved with it knows that the spaghetti application should be completely reworked. But because nobody really understands it, the situation tends to worsen over time. Spreadsheet consultants make a lot of money untangling such applications. I've found that, in many cases, the most efficient solution is to redefine the users' needs and build a new application from scratch.

Utility applications

Good as it is, I still find quite a bit lacking in Excel. This brings me to the next category of spreadsheets: utility applications. Utilities are special tools designed to perform a single recurring task. For example, if you often import text into Excel, you may want some additional text-handling commands, such as the ability to convert selected text to uppercase (without using formulas). The solution? Develop a text-handling utility that does exactly what you want.

The Power Utility Pak is a collection of utility applications for Excel. I developed these utilities to extend Excel's functionality. These utilities work just like normal Excel commands. You can download a trial version of the Power Utility Pak from my Web site (www.spreadsheetpage.com), and you can get a discounted copy of the licensed version by using the coupon located at the back of the book. And if you're interested, the complete VBA source code is also available for a small fee.

The best utility applications are very general in nature. Most macros are designed to perform a specific operation on a specific type of data found in a specific type of workbook. A good utility essentially works like a command normally found in Excel. In other words, the utility needs to recognize the context in which a command is executed and take appropriate action. This usually requires quite a bit of error-handling code so that the utility can handle any situation that comes up.

Utility applications always use macros and may or may not use custom dialog boxes. Fortunately, Excel makes creating such utilities relatively easy, and they can be converted to add-ins and attached to Excel's user interface so that they appear to be part of Excel.

The topic of creating utilities is so important that I devote an entire chapter to it. Chapter 16 discusses how to create custom Excel utilities with VBA.

Add-ins that contain worksheet functions

As you know, Excel has many worksheet functions that you can use in formulas. Chances are that you've needed a particular function, only to find that it doesn't exist. The solution? Create your own by using VBA. Custom worksheet functions can often simplify your formulas and make your spreadsheet easier to maintain.

In Chapter 10, you'll find everything you need to know about creating custom worksheet functions, including lots of examples.

Single-block budgets

By a single-block budget, I mean a spreadsheet (not necessarily a budget model) that essentially consists of one block of cells. The top row might contain names that correspond to time (months, quarters, or years), and the left column usually contains categories of some type. Typically, the bottom row and right column contain formulas that add the numbers together. There may or may not be formulas that compute subtotals within the block.

This is a very common type of spreadsheet. In most cases, simple single-block budget models are not good candidates for applications because they are simple to begin with, but there are exceptions. For example, you might consider converting such a spreadsheet into an application if the model is an unwieldy 3-D spreadsheet, needs to include consolidations from other files, or will be used by departmental managers who may not understand spreadsheets.

What-if models

Many consider the what-if model category to be the epitome of spreadsheets at their best. The ability to instantly recalculate thousands of formulas makes spreadsheet software the ideal tool for financial modeling and other models that depend on the values of several variables. If you think about it, just about any spreadsheet that contains formulas is a what-if model (which are often distributed as templates). Changing the value of a cell used in a formula is akin to asking “what if . . .?” My view of this category, however, is a bit more sophisticated. It includes spreadsheets designed exclusively for systematically analyzing the effects of various inputs.

What-if models often benefit from additional work to make them more user-friendly, especially if the model will be used for a lengthy period of time. Creating a good user interface on an application can make it very easy for anyone to use, including computer-illiterates. As an example, you might create an interface that lets users provide names for various sets of assumptions and then lets them instantly view the results of a selected scenario and create a perfectly formatted summary chart with the click of a button.

Data storage and access spreadsheets

A large percentage of Excel workbooks consist of one or more database tables (sometimes known as lists). These tables are used to track just about anything you can think of. Most people find that it's much easier to view and manipulate data in a spreadsheet than it is using normal database software. If the tables are set up properly, they can be summarized with a pivot table.

Spreadsheets in this category are often candidates for applications, especially if end users need to perform things like data validation and pivot table summaries.

For more sophisticated database applications, such as those that use multiple tables with relationships between them, you'll be better off using a real database program such as Access.

Database front ends

Increasingly, spreadsheet products are used to access external databases. Spreadsheet users can access data stored in external files, even if they come in a variety of formats, by using tools that Excel provides. When you create an application that does this, it's sometimes referred to as an executive information system, or EIS. This sort of system combines data from several sources and summarizes it for users.

Accessing external databases from a spreadsheet often strikes fear in the hearts of beginning users. Creating an executive information system is therefore an ideal sort of Excel application because its chief goal is usually ease of use.

Turnkey applications

The final category of spreadsheet types is the most complex. By turnkey, I mean ready to go, with little or no preparation by the end user. For example, the user loads the file and is presented with a user interface that makes user choices perfectly clear. Turnkey applications may not even look as if they are being powered by a spreadsheet, and, often, the user interacts completely with dialog boxes rather than cells. I've heard these types of applications referred to as “dictator applications” because the user can perform only the operations that the developer has allowed.

Actually, you can convert many of the categories just described into turnkey applications. The critical common elements, as I discuss throughout the remainder of the book, include good planning, error handling, and user interface design.