 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
. | | |
Get your clients ready for tax season
Keeping you informed and prepared amid the coronavirus crisis