Block That Spreadsheet Error

TECHNOLOGY WORKSHOP

You can make Excel safer and more friendly.

Block That Spreadsheet Error

BY THEO CALLAHAN

t one time or another it happens to nearly every spreadsheet user: In an instant a perfectly good spreadsheet disintegrates right before your eyes, leaving a wasteland of #VALUE! error cells. What triggered the problem? Someone mistyped a value into one cell, causing the #VALUE! error message to be propagated throughout every dependent cell—often with catastrophic results. Worse, if the worksheet were linked to a database or part of a web of interconnected spreadsheets, the error would instantly corrupt all those files, too.

The bad news is that errors can’t be avoided—that’s why pencils have erasers. The good news is that such an error can be prevented from devastating a spreadsheet by alerting the responsible person and giving him or her not only an opportunity to correct it but even a hint about what went wrong.

Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces.

Boldface type identifies the names of icons, agendas, URLs and application commands.

Sans serif type indicates instructions and commands that users should type and file names.

To see how let’s begin with a typical spreadsheet designed to track late shipments of a product (see exhibit 1, below).

EXHIBIT 1

Column B contains the products’ order dates and column C the shipping dates. Imbedded throughout column D is a formula that calculates the difference between the two dates and subtracts three holiday dates as shown in the upper right-hand corner of the spreadsheet: =NETWORKDAYS(B7,C7,Holidays). The summary at the top of the spreadsheet contains a formula that computes the average number of business days between order date and ship date: =AVERAGE(D7:D100).

As you’ll see it takes just one error—even a subtle one—to waste the whole spreadsheet. For example, say the person who is filling in the data on Days on Order types an error in cell C13—just a misplaced comma after January 12. Exhibit 2, below, illustrates how that little typo generates #VALUE! error messages and renders the report useless.

EXHIBIT 2

In this case, since the red errors messages appear near where the mistake was entered, the user can see the error’s location. But if the error message is on a different page of the spreadsheet or affects a linked file, he or she would have no clue that an error was made and is wreaking havoc.

BLUNDER BLOCKER
Excel has a function—called Validation—that solves this problem. To demonstrate we’ll create a typical expense report that will include columns for the date of the expense, its category, dollar amount and a column for entering any additional information. See exhibit 3, below.

EXHIBIT 3

Go to A12 and type Date and format the column for Dates; in B12 type Category; in C12 Amount and format the column for Currency; and in D12 type Comments. We’ve purposely left room at the top for the company logo and employee information.

EXHIBIT 4

Starting with the A column we’ll specify the kind of data that can be legitimately entered, so if someone puts in any other kind of data, Excel will trigger an immediate alert at the error location. Begin by highlighting column A by clicking on the column label—A. Then go to the toolbar and click on Data, Validation.

That brings up the Data Validation screen (see exhibit 4, at left). Choose Date as the type of data from the Allow: box. To restrict the valid dates to this year, enter 1/1/02 under Start Date: and 12/31/02 under End Date:.

For now ignore the second two tabs (Input Message and Error Alert). Click on OK to get back to the spreadsheet.

Now, if you type a bogus date—say June 31, 2002—in A13, you will instantly get a generic warning message (see exhibit 5, at right).

EXHIBIT 5

EXHIBIT 6

Let’s customize the error message so it will tell users the reason for the alert. To do that, return to the Data Validation screen and click on the Error Alert tab (see exhibit 6, at left).

Caveat: Resist the temptation to prepare humorous or sarcastic text in custom error messages—a common occurrence. Such text has a way of becoming not funny and quite irritating after a while. Also, keep in mind that you are trying to be helpful.

The next step is to set up validation rules for the other columns. For the rules to be effective, we need to be sure everyone uses the same expense categories with the same codes. For our example, we’ll use only the categories with the following codes:

Code Description
ENTR = Client entertainment
HOTL = Lodging
MEAL = Employee meals
TRVL = Airfare, taxi

Select all of Column B, go to Data, Validation and click on the first tab, Settings. Click on Allow: to generate a dropdown list and select List. The box will change to look like exhibit 7, at right. In the Source: box, type the four codes from the above list. Leave a check in the Ignore Blank box (so a blank cell will not trigger an error message) and in the In-cell dropdown box.

EXHIBIT 7

We placed a check in the In-cell dropdown box so that when users click on a cell in the Categories column they automatically generate a menu of all the listed codes (see exhibit 8, below); this saves users from having to type them in—another way to prevent typing mistakes.

EXHIBIT 8

Note that it is still possible to type an invalid entry into a data cell, so you should go to the Error Alert tab and create a suitable error message. If the list of categories we’ve used doesn’t seem very robust, don’t worry, we’ll probably be adding to it. For one thing, every expense report needs a catchall category. Instead of adding it here, let’s insert a miscellaneous (MISC) category to a range on the spreadsheet. In cells E1 through E4, type in each of our categories from the above table. Then, in E5 type MISC. Now go back and choose Column B and go to Data Validation. In the Source: box, clear out our earlier typing. Use the mouse to choose E1 through E5. Click on OK.

Caveat: You must note two things. First, if you change the list of valid entries, existing entries will not be updated. For instance, if you decide that HOTL should really be LODG, changing the value in the list will prevent anyone from using HOTL in the future, but cells already containing HOTL will remain. You may add entries to the valid list at any time, but you should make changes carefully, keeping an eye out for existing data.

Also, the list of valid entries must be placed on the same sheet as the data being entered unless you use a named range. To use a named range, highlight the expense categories in E1:E5 and go to the top left corner of your Excel window. Just below the Font Box is the Name Box (see screenshot below).

Type a phrase with no spaces like ExpenseCat into the Name Box. Now go back to Data Validation and replace E1:E5 with =ExpenseCat. You use the same process to place your named range on Sheet2. The advantage is that the person filling out the expense report doesn’t see (and won’t modify) the validation list.

RAISING THE FLAG
So far we’ve disallowed entries that didn’t meet the criteria. But sometimes we just want to warn a user that a value is unusual or to flag him or her with useful information. For example, when we want to warn users that any expense over $500 must be signed by a vice-president, highlight Column C and bring up the Data Validation dialog box. In the Allow: box, choose Decimal. In the Data: box, choose less than or equal to. In the Maximum: box, type 500. Go to the Error Alert tab. In the box marked Style: change Stop to Information. In the Title: box, type Large expense, and in the Error message: box type Expenses over $500 must be approved by a vice-president and click on OK (see exhibit 9, below). Now all the cells in Column C expect values less than $500. As long as we enter smaller values, nothing happens. If we enter a value greater than $500, the informational message is triggered.

EXHIBIT 9

Test that it works. Enter a figure less than $500; nothing should happen. But if you put in an amount over $500, the warning should pop up.

In the Style: box in Data Validation, you may have noticed another option called Warning. When this is added, the user is asked to verify the data. In other words it’s just a warning to the user to double-check the entry.

Now let’s add a validation for column D—Comments. Since Excel can handle a maximum of only 255 characters in any one cell, entering information that exceeds that limit either gets lost or, in a worst-case scenario, corrupts the cell and can ruin the spreadsheet. To impose a 225-character limit to cells in the Comments column, highlight the Comments column and go to the Settings tab and the Allow: box, choosing Text length. In the Data: box, select less than and set the Maximum: box at 255.

VALIDATION WITH FORMULAS
So far we’ve used only constant conditions. For instance, expenses must be less than $500 and expense codes must be in a standard list. But what if you’d like to check a changing condition? What if an activity must occur within a certain time range or a value must be at least a certain percentage of another?

In the Data Validation dialog box under Allow: you will see Custom. This validation feature lets you use any valid Excel formula that evaluates to TRUE or FALSE. For instance, if you type =(5>3) in a cell, it will show TRUE. If you type =(2=7) in a cell, it will show FALSE. Our formula must compare the value in the data validation cell with such a formula.

To illustrate, imagine a catalog sales department with different shipping charges. Certain products have one rate and bulk products have another. Imagine, too, that only one constant holds: Shipping is never less than 15% of a product’s price. Let’s set up a data validation cell to test that.

Our goal is to write a formula that will evaluate to TRUE when shipping charges are more than 15% and FALSE when they are less. In the spreadsheet shown in exhibit 10, at right, D1 contains the price and E1 the shipping charges; so our formula will be =(E1 > (D1*0.15)). Remember to go to the Error Alert tab and change the Style to Stop and write an appropriate error message.

Caveat: When you choose column E to set up the data validation, notice that while the entire column is shaded, only one cell is highlighted. That cell (probably E1) was the active cell—that is, the cell to use in our validation formula. The black arrow in exhibit 10 shows the highlighted cell. Be careful when entering validation formulas to use the active cell’s address; otherwise, you may be checking the amount just above or below a specific cell. To be sure you have selected the correct cell, look in the Name Box—the red arrow in exhibit 10 points to it.

EXHIBIT 10

CLEAN UP
If you want to add your company logo and other identification information at the top of the spreadsheets in rows 1 through 11, we’ll have to clean out the rules just for those rows. Start by highlighting rows 1 through 11 or just select the range from A1 to D11. Go to Data Validation and you probably will get one of two error messages depending on how you selected the range. One message states that some rows do not contain data validation rules and asks do you want to extend them? Since we’re about to delete the rules, it doesn’t matter what you answer. So just click on Yes to get rid of the box. The other likely error message tells you that your range contains multiple types of data validation and asks if you would like to clear them. Click on Yes.

That should bring you back to the regular Data Validation screen. At the bottom left is a Clear All box. Before you click on that box, make sure the check box just above it that says Apply these changes to all other cells with the same settings is clear. If that box is checked and you then click on Clear All, you’ll clear all of your rules—and you don’t want to do that. Now click on Clear All and on OK. You’ll find that you can now type in anything in the first few rows of your spreadsheet, but the rules still apply in the expenses section. To test that, I inserted a logo at the top left for my company and entered my information about the nature of my expenses near the top (see exhibit 11, below).

EXHIBIT 11

You now have the basic tools to make your spreadsheets more robust and user friendly. However, use data validation judiciously. If you put in too many rules, users may feel boxed in—limited in what they can do. Remember, data validation is not a security tool and it doesn’t make Excel an enterprise database application. If you want to take validation to the next logical step, you’ll need to use Visual Basic for Applications code. And for true data validation, data security and data integrity, you must migrate to a database such as Access, SQL Server or Oracle.

THEO CALLAHAN is president of I Get It! Development, a consulting firm based in Los Gatos, California, that develops custom software and offers custom business-process training programs. His e-mail address is theo@igetit.net.

An Invitation
If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is zarowin@mindspring.com.