Data Validation in Excel

Data validation is used when it is necessary to keep an eye on what values are entered into the cell. This tool allows you to specify the values that may be written into the cell. This helps for example to avoid entering wrong date or entering text, where is supposed to be a number, etc.

Example

There is a list of employees. Each column in the following Excel table have some rule for writing values. We have to modify Data Validation to be able to enter only correct values into the cells.

List of employees

Rules:

Employee number is exactly 5 characters long.

Salary can be set in the range 600-2000.

Bonus cannot be greater than 10 % of the salary.

Date of entry can be set only as today.

Department must be one of the values from the list of Departments.

Solution

Emloyee No. (exactly 5 characters long)

Select range B2:B7 and choose Data Validation.

Data Validation on the ribbon

 

Set Allow to Text length, Data to equal to and Length to 5.

Data Validation Text length

 

Now you can try write something to column Emloyee No.

If you have selected at the beginning only cell B2, you can copy Data Validation to the other cells in the column.

Salary (range 600-2000)

Select range C2:C7 and choose Data Validation. Set Allow to Whole number, Data to between, Minimum to 600 and Maximum to 2000. You can also set Allow to Decimal. It depends on whether you want to allow decimal numbers.

Data Validation Whole number

 

Salary Bonus (cannot be greater than 10 % of the salary)

Select cell D2 and choose Data Validation. Set Allow to Decimal, Data to between, Minimum to 0 and Maximum to =C2*0.1. Now copy from D2 to the other cells in the column. You can use Ctrl+c and Ctrl+v or some other way that you know.

Data Validation with formula

 

Date of entry (only today)

In this example, we will use the Excel function TODAY(). Select range E2:E7 and choose Data Validation. Set Allow to Date, Data to equal to and Date to =TODAY().

Data Validation Date

 

Department (from the list of Departments)

Select range F2:F7 and choose Data Validation. Set Allow to List and Source to =$I$2:$I$5.

Data Validation List from the range

Or you can type items from the list directly to the Source. Between the values must be , or ; That depends on your version of Excel.

Data Validation List from typed list

This create in the cells a drop down menu. More about drop down menus in the Excel  http://excel-example.com/other-tutorial/how-to-create-drop-down-menu-in-excel-worksheet.

Note: Change in the source does not change already entered values in the cells.

 

Add a comment »One comment to this article

  1. Hello,

    how can I check date interval in cell value?

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress