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.

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.

 

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

 

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.

 

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.

 

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

 

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.

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.

This create in the cells a drop down menu. More about drop down menus in the Excel  https://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.