Search

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.

  1. Download the above Data Validation workbook and open in Excel.

  2. Go to the 'Book Room' tab and note there are five data entry fields.

  3. Go to the 'Rooms' tab and note the table of bookable rooms and maximum capacities.

 

Opening the Data Validation dialogue

Shortcut to Data Validation is [Alt] + [A] [V] [V]

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

  1. Select the Book Room sheet

  2. Select the Participants field

  3. In the ribbon select the Data menu

  4. In Data Tools click Data Validation.

  5. Go to the Settings tab

The settings tab contains the rules governing what values are allowed. By default all cells allow any value.

  1. Set Allow to 'Whole Number'

  2. Set Data to 'between'

  3. Set Minimum to '2'

  4. Set Maximum to '=MAX(Indirect("tblRoom[Size]"))'

  5. 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:

  1. Set meeting date to allow dates greater than or equal to, '=today()'.

  2. 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.

  1. Select Rooms and then use the ...

  2. Keyboard shortcut [Alt] + [A] [V] [V]

  3. Set Allow to 'List'

  4. Set Source as shown

  5. 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.

  1. 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.

  2. 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.

  3. 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.

  1. Select the meeting date field

  2. Open the data validation dialogue

  3. Go to the Settings tab

  4. Untick the Ignore blank checkbox

  5. 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.

  1. Enter a date from the future and note that this passed validation

  2. Enter an invalid date from the past, and note that this causes an error.

  3. 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.

  1. Go to the Book Room tab

  2. Select the, "Too many participants cell" containing the value, '22'

  3. Copy to clipboard using the [Ctrl] + [C] keyboard shortcut.

  4. 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.

  1. Select the Meeting date field

  2. On your keyboard press the delete key

  3. 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.

  1. Select the Requests field

  2. Open the data validation dialogue

  3. Select the 'Input message' tab

  4. Enter the title as shown

  5. Enter the input message as shown

  6. 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".

  1. Select the Participants field

  2. Open the data validation dialogue

  3. Select the Error Alert tab

  4. Set the style to 'Warning'

  5. Set the title field to, "Book Meeting Room".

  6. 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"

  7. Click OK

  8. 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.

  1. Go to the Rooms sheet

  2. Select Name column (not header)

  3. Open data validation dialogue

  4. Select Custom

  5. Enter formula as shown

  6. Click OK

  7. This formula counts the number of times the string appears in the name column, and will only return TRUE if that count is 1.

  8. Because data validation does not recognise 'tblRoom[Name]' as a range of cells, we use the INDIRECT function.

  9. 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:

  1. Select all the cells before opening the data validation dialogue; or

  2. 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.

  1. Go to the Rooms tab

  2. Selects the top cell in the Name column (not the header)

  3. Open the data validation dialogue

  4. Go to the Error Alert tab

  5. Enter title/error message as shown

  6. 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.

  1. Select all of the names in the table

  2. Open the data validation dialogue

  3. 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.

Shortcut for Paste Special Validation is [Alt] + [E] [S] [N]
  1. Select first room name in the table

  2. Copy to clipboard ([Ctrl] + [C])

  3. Select all room names in the table

  4. 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

Validation errors in tables cause green triangles

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.

  1. Select cell containing 'Clifford Suite'

  2. Copy to clipboard ([Ctrl]+[C])

  3. Select cell 'Meeting Room 1'

  4. Paste from clipboard ([Ctrl]+[V])

  5. Press escape to clear clipboard

  6. Select one of the cells containing 'Clifford Suite'

  7. 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.

 

14 views0 comments

Related Posts

See All