Tip #4128 - Data Validation
Printer Friendly Version | E-Mail This Tip
Data
Validation
Did you know you have
the ability to set limits on the data that's entered into an MS Excel
worksheet?
Maybe you've got a
worksheet where the data to be entered must be between two values (between
1 and 100, for example) and you'd like a way to make sure that what's
entered falls outside the range.
It's called Validation,
which the online help defines as a feature that "defines what data
is valid for individual cells or cell ranges; restricts the data entry
to a particular type, such as whole numbers, decimal numbers or text;
and sets limits on the valid entries."
If you've ever wanted
to have some control over the data entered into your worksheet, validation
may just be the thing for you!
For today, we'll start
with taking a look at setting data value ranges, so be sure to read on!
The first step is
to highlight the cells in which the data will be entered.
With the cells highlighted,
go to the Data menu, Validation choice.
When the Data Validation
window opens, you're looking for the Settings tab.
Here's where you tell
Excel what type of data to accept.
From the "Allow:"
drop down list, select the type of data. Since we're looking
for set data ranges in this tip, we'll select either the Whole number
or Decimal option, whichever is your preference.
When you make this
selection, you'll notice that the bottom half of the tab changes.
The fields where you
can enter the minimum and maximum data values then appear.
Go ahead and enter your acceptable range.
Now, click over to
the Input Message tab.
Here's where you can
enter a message for the worksheet user to let them know about the cell
(such as the acceptable data range) when it's selected.
It will pop up a little
information box so that they can input the required data.
I felt that under
certain circumstances, it might be useful, but if there's a lot of data
to be entered, this would get annoying rather quickly. But, it's up to
you!
If you want an Input
Message, enter a title (the bolded Warning! in the picture)
and in the bottom section, your message.
Finally, tell Excel
what to display when the user inputs data that doesn't fit within your
range.
You can accomplish
this on the Error Alert tab.
Same as with the Input
Message, enter a title for the alert and a message. If
you don't enter any information, Excel will display a generic message,
but it seems to me that if you went to all the trouble to set this up,
a specific error message may be the way to go.
At any rate, when
you've entered all your information just the way you want it, click the
OK button.
Believe it or not,
that's it!
If someone tried to
enter data that isn't within your range, they're going to get a Stop window,
like this:
They will then be
given a chance to try again.
It's time to take
control of your data, so go to it!
~ April
Want To Comment On This Tip? Click Here! We'd love to hear from you :-)
Like These Tips? Get 'Em Free In Your E-mail
