Data validation may sound complicated, but it’s just a way to apply rules to a cell to make sure good (valid) data is being entered. How can you use this? You can set data validation for a cell to only accept a certain type of data such as date, time, numbers, or whole numbers. You can control the length of text that can be entered. You can even create a list of entries that users must choose from. Let’s look at a few examples.
If you need to control the length of an entry, such as If you have a column in your spreadsheet for part numbers, and all of your part numbers are 10 characters long:
- Select the cells (or the whole column) that should only accept input of exactly 10 characters.
- Go to the Data tab on the ribbon and choose Data Validation.
- At Allow, choose Text Length.
- At Data choose Equal To
- Enter 10 in the Length field.
- If needed, click on the Input Message tab to enter a message that will be displayed when someone selects this cell.
- Go to the Error Alert tab to enter a message that will appear if someone enters data that doesn’t meet the requirements (in this case, anything that’s not 10 characters long).
- Click OK
To make sure a cell only accepts dates:
- Select the cell(s)
- Go to the Data tab on the ribbon and choose Data Validation.
- At Allow, choose Date.
- At Data choose whatever is appropriate. If you want to limit the cell to a range of acceptable dates, choose Between and then set your start date and end date. If you don’t care about a date range and just want to make sure a date is entered, there’s not an “Any” option so you’ll have to get a little creative. You could set Data to Not Equal To and enter a date like 12/31/9999.
Most of the “Allow” options work the same way. If you choose Whole number, Decimal, Date, Time, or Text Length, then at the “Data” field you can choose from:
- Between
- Not Between
- Equal To
- Not Equal To
- Greater Than
- Less Than
- Greater Than Or Equal To
- Less Than Or Equal To
Depending on your choice you’ll get the appropriate fields to enter the value(s) you want to compare to such as start date, length, etc. In those fields you can enter the exact number/date/text you want, or you can point to another cell on your spreadsheet, or you can even enter a formula.
Another more advanced choice in the “Allow” option is Custom. If you choose custom, the Data field is greyed out and you are given a Formula field. The cell being validated will be checked against the formula or cell reference you enter here.
At the beginning, I mentioned that data validation will let you create a list of choices. This is actually quite easy to do. First, you need to create your list. This is done on your spreadsheet, not in the Data Validation window. In most cases, I recommend typing the list on a separate tab in your workbook. That way it’s out of the way and you can hide the tab if you don’t want people to be able to easily change the list of choices. Once you’ve created the list:
- Select the cell(s) you want the list to be used in
- Go to the Data tab on the ribbon and choose Data Validation.
- At Allow, choose List.
- Click into the Source field, then click back onto the spreadsheet and highlight the list you just created
- Continue through the Input Message and Error Alert tabs as needed
- Click OK
Now when you move to the cell you applied the list validation to, a down arrow button will appear that opens the list of choices. Only entries from this list will be allowed.
