Excel Data Validation | Tutorial
Well thought out data validation helps users to enter consistent and accurate data, and drop-down lists of valid values can also make data entry more speedy.
This tutorial covers all of Excel's data validation facilities, and on completion you will not only know how to implement data validation, but will also have a good idea of where data validation can be of most benefit.
Download Sample Workbook
Data Validation
.xlsx
Download XLSX • 15KB
The following workbook contains a meeting room booking form, and a table of bookable rooms. In this tutorial you will add data validation to this form.
Download the above Data Validation workbook and open in Excel.
Go to the 'Book Room' tab and note there are five data entry fields.
Go to the 'Rooms' tab and note the table of bookable rooms and maximum capacities.
Opening the Data Validation dialogue
Excel data validation is almost entirely managed by selecting one or more cells and opening the Data Validation dialogue - either from the Data Menu, or with a keyboard shortcut, as shown.
By default each cell will allow any value. 90% of the restrictions you'll likely need can be easily configured with the whole number, decimal, list, date, time and text length options, and most of the remaining 10% can be covered with a little more effort via the Custom option. We also look at how input messages can help the user to enter accurate date, and how you can configure the way Excel reports and handles errors.
The data validation button in the ribbon also has a drop down that provide the options to circle any invalid data present; and or to clear (or hide) those circles - and we will look at these, too.
Validation Criteria
Select the Book Room sheet
Select the Participants field
In the ribbon select the Data menu
In Data Tools click Data Validation.
Go to the Settings tab
The settings tab contains the rules governing what values are allowed. By default all cells allow any value.
Set Allow to 'Whole Number'
Set Data to 'between'
Set Minimum to '2'
Set Maximum to '=MAX(Indirect("tblRoom[Size]"))'
Click OK.
Test this out by attempting the entry of both valid and invalid data. Try also selecting the participants cell and pressing the delete button on your keyboard.
Now go through a similar process to achieve the following:
Set meeting date to allow dates greater than or equal to, '=today()'.
Set meeting title to allow text of a length less than or equal to 30 characters.
The validation for Room is a little different in that we'll define a list of valid values from which the user may select one.
Select Rooms and then use the ...
Keyboard shortcut [Alt] + [A] [V] [V]
Set Allow to 'List'
Set Source as shown
Click OK
The rooms field should now be a dropdown from which only valid rooms may be selected.
This is the second time we have used the INDIRECT function, which required some explanation.
To the right of the fields in which the indirect function has been used, there is an underlined up arrow which allows you to select a range of cells. But if you use this to select the Name or the Size column it will return something like, 'Rooms!$B$5:$B$10', which will not extend to accommodate new rooms added to the bottom of the table.
Structured table references such as 'tblRoom[Name]' or 'tblRoom[Size]' are not recognised by the data validation dialogue as a range of cells, and will be rejected.
So we use the INDIRECT function, which is fully supported, and does exactly what we want, converting the string, "tblRoom[Name]", into a range of cells at every turn.
Blank cells, paste special values, and validation circles
By default excel does not validate blank cells. However, there may be some cells for which a blank value is unacceptable.
Select the meeting date field
Open the data validation dialogue
Go to the Settings tab
Untick the Ignore blank checkbox
Click OK
The effect of this is hard to spot, as you will still be able to use the delete key to empty the cell.
Enter a date from the future and note that this passed validation
Enter an invalid date from the past, and note that this causes an error.
Respond Retry to the error message and delete the past date.
This seems hardly worth the effort, given how easy it is to empty the cell of contents, but it does mean Excel can report the validation failure in other ways, which we'll come to shortly.
Paste Special Values
Using the delete key to empty a cell is not the only way to bypass data validation. It is also possible to paste invalid data into a cell, without challenge.
Go to the Book Room tab
Select the, "Too many participants cell" containing the value, '22'
Copy to clipboard using the [Ctrl] + [C] keyboard shortcut.
Paste the value '22' using the keyboard shortcut [Alt] + [E] [S] [V] and then [Enter]
Note. Pasting the whole cell will overwrite the data validation settings. So it's important to just paste the value using the Paste Special Value shortcut.
Validation Circles
Both the Delete and Paste special functions can be used to change the value of multiple cells in one fell swoop, and it therefore seems reasonable not to present individual error message for every data validation error that arises from such actions. However, this does mean that there needs to be another way to show cells that fail their validation - and there is.
The data validation button in the ribbon is a combo-button that provides either access to the data validation dialogue; or can invoke one of two other 'visual' functions that show or hide red circles around cells that have failed their validation check.
Select the Meeting date field
On your keyboard press the delete key
On the data validation button in the ribbon, click the Circle Invalid Date option.
Now you can see the error highlighted. The circle will vanish when you enter a valid date, but if you then delete that date again, the circle will not reappear.
Note. Cells in a table that have validation errors are also highlighted with a small green triangle, which we'll come to later.
Input Messages
Helpful messages as the user enters data will also have a positive impact upon the accuracy and consistency of data entered into your workbook.
Select the Requests field
Open the data validation dialogue
Select the 'Input message' tab
Enter the title as shown
Enter the input message as shown
Click OK
Note. In this example we've defined an input message without any validation criteria, but it's entirely possible, and sometimes desirable, to have both.
Once the input message is set up it will appear whenever the cell is selected, as shown here.
Note. It can be distracting if every input box flashes up an input messages; and it is wise to consider the value each such message adds. Generally input message should only be presented when the prompt and/or context is insufficient to properly convey the field's purpose.
Errors, Warnings and Information
You will by now be familiar with the rather generic message that appears when user input fails the validation rules set up for a cell, and the Retry and Cancel options presented. These things can be changed in the third tab in the Data Validation dialogue, which is called, "Error Alerts".
Select the Participants field
Open the data validation dialogue
Select the Error Alert tab
Set the style to 'Warning'
Set the title field to, "Book Meeting Room".
Set the error message field to, "The number of participants must be between two and the number that can be accommodated in the largest meeting room"
Click OK
Enter an invalid value into the participants field.
Instead of the retry option, there is now the option to either accept the warning and bypass validation; or to have another go at entering a valid value.
Try using 'Information' instead of 'Warning' and note the options in the information message.
Applying data validation to a range of cells
By selecting a range of cells and opening the data validation dialogue, it is possible to apply data validation to multiple cells. In this example we want to ensure each room name is unique.
Go to the Rooms sheet
Select Name column (not header)
Open data validation dialogue
Select Custom
Enter formula as shown
Click OK
This formula counts the number of times the string appears in the name column, and will only return TRUE if that count is 1.
Because data validation does not recognise 'tblRoom[Name]' as a range of cells, we use the INDIRECT function.
The formula is written as though we're validating the first cell in the range (B3). When creates the validation for the other cells, it adjusts the reference accordingly (unless anchored by dollar symbols).
Editing data validation across a range
You need to take a little care when data validation settings have been applied to a range of cells. You must either:
Select all the cells before opening the data validation dialogue; or
Select one of the cells, open the data validation dialogue, and then tick the 'Apply these changes to all other cells with the same settings' checkbox.
Copy Validation Settings
It is all too easy to update the settings for one cell, when you intended to update them for every cell.
Go to the Rooms tab
Selects the top cell in the Name column (not the header)
Open the data validation dialogue
Go to the Error Alert tab
Enter title/error message as shown
Click OK
Note that the new error message will only appear when attempting to change the first cell to a duplicate name, and the old message will appear for all the other cells.
Select all of the names in the table
Open the data validation dialogue
Note the message and click Cancel
Excel has noticed the problem but offers only the very blunt instrument of starting over again, which may not be what you want if the validation took time to set up in the first place.
What you need to do is find the cell in the range that contains the validation that you want to replicate across the whole column.
Select first room name in the table
Copy to clipboard ([Ctrl] + [C])
Select all room names in the table
Paste the validation using shortcut [Alt] + [E] [S] [N] and then [Enter]
Now all the cells share the same data validation settings, input message and error alert. And can now be edited en-masse, either by selecting all the cells before launching the data validation dialogue, or by ticking the 'Apply these changes to all other cells with the same settings.
Validation errors in tables | Trace error alerts
If a cell in a table has a validation error, then Excel will create a, "Trace error alert", which is a little green triangle in the top left of the cell. To create some errors in the Rooms table we will use cut and paste to create two duplicate entries.
Select cell containing 'Clifford Suite'
Copy to clipboard ([Ctrl]+[C])
Select cell 'Meeting Room 1'
Paste from clipboard ([Ctrl]+[V])
Press escape to clear clipboard
Select one of the cells containing 'Clifford Suite'
Review each of the options presented.
Finding cells having data validation
Most what constitutes is highly visible, such as values and formatting; or easily visible such as formulae in the formula bar; but data validation is only in evidence when the cell fails the validation.
For this reason it is possible to use the Find & Select button in the Home menu to select all cells on the current spreadsheet that do have validation. Try it now on each of the two sheets in your workbook.
Additional Notes
Multiple validation conditions
You may have cells upon which you want to apply multiple conditions. For example, as well as checking the room names are unique (which we have done) you may also want to check that the names are no more than, say, 20 character. All of this can be done using the Custom validation - just so long as you can construct a formula to deliver a single True/False results. However, you should note the following:
You can only have one error message.
You cannot use structured table references in the formula
The space in which you can type the formula is extremely limited.
Entering a formula into a validated cell
Although it's difficult to foresee an example where this is useful, it is entirely permissible to enter a formula (instead of a value) into a cell that has data validation. In this case the formula will be calculated at the time it's entered and the result validated. But if something else in the worksheet changed causing that cell to recalculate, then the validation will not take place and you will not be advised that the cell no longer passes validation.