With data validation, you can control what is entered in a cell, and prevent invalid data. I usually use drop downs, to create a list of options for people to choose from.
Here is a typical drop down list in Excel, showing a list of month names.
Sometimes a list isn’t the best option, and I have to use one of the other data validation settings.
Enter a Number
There are several other things that you can allow in a cell, instead of a list, such as whole numbers or dates.
In this example, the cell will only allow whole numbers.
After you select from the Allow drop down, one or more boxes will appear. For most of the data types, you’ll have to choose an operator, such as greater than, less than or equal to, or between.
Set Range of Numbers
This cell should only allow numbers between 1 and 10, so the Between operator is selected, then 1 is entered as the minimum number, and 10 as the maximum number.
Test the Validation
After you set up the data validation rule, you can test it, by typing an invalid entry. When I type 11 in the cell, an error message appears. It’s not too helpful – it tells me that the entry is not valid, but doesn’t explain why.
Add More Information
There are a couple of things that you can do, to help people put valid data into the cell:
- show an input message when the cell is selected. Give a brief description of what can be entered in the cell.
- create an error message, which appears if invalid data is entered. The error message can stop invalid data, or show a warning but allow the invalid data.
You can read detailed instructions on my Contextures website, for setting up these messages. Or watch the video below, to see the steps.
____________________