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!