Excel: Data validation

There may be situations when you want to prevent users from entering data that doesn't meet the conditions you specified. 

Suppose that you have the cell in your worksheet where you want a user to enter his age. Therefore, as a condition you require the integer between 18 and 99

To set a condition, first, select the cell or the range of cells, then click DATA >> Data Tools >> Data Validation. After you do that, a new window will appear.

 

Settings

Here, you can specify which kind of data and range you want to set for the selected cells. 

You can use one of the following data types: 

Whole number

Numbers that are not fractions (-12, 0, 4, etc.).

Decimal

These numbers can also be fractions (1.2, -1, 5, 4.6, etc.). 

List

With this type of validation, you can choose from the drop-down menu one of the several items.

 

If you choose List, the new textbox, called Source will appear. In this textbox, you can select cells from the worksheet or enter values from the keyboard- for example "one,two,three" (or "one;two;three" depending on your system configuration). 

Date

Here, you can enter date and time. (“3/14/2014”, “14-Mar”, “14”).

CAUTION

If you enter a date without a year- for example, "14 March", Excel will treat this record as "13 March 2014" (current year). If you enter only a day, for example, "14", it might seem that it is a day of the current month and current year. However, in this case, it will be regarded as "14 January 1900". That's because the text "March 14" is treated as a date and "14" as a number. You will learn more about dates in the future lessons. 

Time

Here, you can enter an hour. For example: "12:00", "1:14:50 p.m."

TIP

If you want to see which date and time formats you can use, right-click the selected cell and choose Format Cells…. After you click the position from the category on the left side, a list of different formatting options will appear.

Text length

Here, you can specify the number of characters that must be entered. You can enter plain text, as well as numbers. 

CAUTION

There may be some confusion when you deal with dates. If you set the length of the text range for 3 to 5 characters, and then you enter the date “March 14, 2014” (14 characters), format it to “14-Mar-14” (9 characters) Excel won’t return an error. That’s because a date in Excel is stored as a number and is only formatted as text. If you want to see that number, right-click this cell and select Format Cells ... from the contextual menu. Select Number and click OK. As you can see this number is “41712”. It consists of 5 characters, which are inside the specified range, so this number won't return the error. 

Custom

The custom validation is more complicated than other types of validation. Here, you can insert a formula with the specific parameters. 

At the bottom of the window, you will find the checkbox called Apply this changes to all other cells with the same settings. It means that if you click the cell and select this option, Excel will check in a worksheet (not a workbook) whether there are other cells with the same validation parameters. If so, all these cells will be selected and the changes applied. 

Input Message

Here, you can set a pop-up window with title and input message. Now, when you click the cell, a window with entered message will appear. 

Error Alert

Stop

Excel displays an error message with the Retry and Cancel buttons. If you select the Retry button you'll get a chance to re-enter the value. If you choose the Cancel button, the value will be restored to the one that was previously entered. 

Warning

In this case, Excel displays a warning message. There are three option available: Yes, No and Cancel. If you choose Yes, Excel will enter the value that doesn’t meet the conditions. If you click No, Excel will let you change the data. If you click Cancel then the data will be restored to the previous value. 

Information

When information window appears, you can click OK to accept the value or Cancel to return to the previous state. 

CAUTION

Excel checks the value only after you create the validation rule. If you apply validation to the cells that already contain values, Excel will not complain, even if those values are incompatible with the rule. 

Removing validation

If you want to remove the validation from the cells, first select these cells, then click the Clear All button in the lower left corner of the Settings tab. Click OK to confirm.