Data Validation In Excel

What Is Data Validation In Excel?

Data validation in Excel allows users to have additional control over the data entered into a cell or range of cells. In other words, it prevents users from entering data by limiting them only to enter pre-defined values in the worksheet.

Here is a list of actions that the user can perform in a cell(s) using the data validation tool:

Enter a certain number of characters in each data entry
Input only numbers that fall within a certain range, for example, only between 1 to 10
Dates and times outside of a specified range are restricted
In a cell, only text or numeric values are permitted
Invalid entries should be found in validation cells
Restrict user entries with pre-defined values in the form of a drop-down list
Validate an entry using information from another cell
When erroneous data is entered, it displays a warning message
When the user picks a cell, it shows an input message

The most widely used data validation tool in Excel is a drop-down list. For example, if we want the user to type YES or NO, we can create a drop-down list like this:

In cell A2, the user can enter either YES or NO. However, if they try entering anything which is not there in the drop-down list, Excel will show a warning message as shown below:

What Is Data Validation In Excel?

Data validation controls the input entered by users, thus making the textual or numerical input error-proof.
Users can give additional criteria to whole numbers, decimal numbers, dates, and time, such as greater or less than, between or not between, equal to or not equal to, to create a drop-down list for validation.
One can utilize logical formulae, like IF, ISTEXT, and ISNUM, to allow users to enter only text or numbers.
It shows the input message in the validation cell to let users know about the validation.

Data Validation Options

Whenever we need to restrict data entry, we will have to choose the kind of validation required for a specific cell or range of cells.

Follow the below steps to navigate the data validation feature in Excel:

Data >>> Drop Down of Data Validation >>> Data Validation

Below are the options under data validation:

Under the ‘Settings’ tab, you can set the validation criteria in the “Allow” drop-down list.

Any Value: It will allow the user to enter any value.
Whole Number: It will allow the user to enter only the whole number. We can also restrict the user to enter the number in conditions, such as greater or less than, between or not between, equal to or not equal to.
Decimal: It will allow the user to enter only decimal numbers. Additional conditions will be greater or less than, between or not between, equal to or not equal to.
List: We can create a drop-down list by entering values, giving cell references, or using named ranges.
Date: It will allow the user to enter only dates or their number value. Additional conditions will be greater or less than, between or not between, equal to or not equal to.
Time: It will allow the user to enter only time or its number value. Additional conditions will be greater or less than, between or not between, equal to or not equal to.
Text Length: The user can enter text values with certain characters’ length limits.
Custom: In this option, we can create custom data validation in Excel. For example, entering the formula =EXACT(A1,UPPER(A1)) will allow users to enter only uppercase characters in cell A1.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Steps To Validate Data In Excel

Let us look at the steps involved in creating a basic data validation in Excel.

Suppose we want the user to enter the month names in Excel through a drop-down list. Then, we will follow the below steps.

Select the cell or range of cells where we need the data validation. In this case, we have selected cells from A2:A6.
Go to the Data tab and choose the Data Validation option.
This will open the Data Validation window. Now, choose List from the validation options, as we are creating a drop-down list of months here.
In the Source box, enter the month names. Each month should be separated by a comma (,).
Click on the OK button, and we have our drop-down list ready in cells A2:A6.

Now, users can enter the values in the drop-down list or choose only from the list.

In this way, we can restrict the user entries in excel.

Examples

Let us look at some data validation examples to understand how it works:

Example #1: Create Drop-Down List From Cell Reference

In the above example, we have created a drop-down list by entering values directly in the Source box. However, we can make this dynamic by giving cell references.

Step 1: Enter the month names in cells A2 to A7.

Step 2: Select the cell where you need to create a drop-down list. We have selected cell C1.

Step 3: Create a drop-down list using the below path in Excel.

Data >>> Drop Down of Data Validation >>> Data Validation

Step 4: Choose List from validation options.

Step 5: In the Source box, we will select the month list entered in cells, i.e., A2:A7.

Step 6: Click on the OK button, and we have a drop-down list ready in cell C1.

However, this is not dynamic because the drop-down list will not update dynamically when adding a new month to the list.

As we can see above, a new month was added in cell C8, but the drop-down is not showing the newly added month.

In the below example, we will see a dynamic way to create a drop-down list.

Example #2: Dynamic Drop-Down List Using OFFSET Function

We added a new month in the above example, and the drop-down was not updated automatically. However, the cell reference given to the drop-down list was A2:A7, so only change within this range will affect the drop-down.

Step 1: In Data Validation Source box, enter the OFFSET formula.

=OFFSET($A$2,0,0,COUNTA($A$2:$A$20))

The OFFSET function moves down 0 rows and columns starting from cell $A$2.

Then, the COUNTA excel function will return a number of cells with the values. And OFFSET will offset the number of rows provided by the COUNTA function.

So, as you keep adding or deleting values in the range A2:A20, COUNTA will return a number of cell counts with values, and accordingly, OFFSET will retrieve those values to the drop-down list.

Step 2: Click on the OK, and the drop-down list is ready.

Now add new months to the list, and the drop-down list will return them dynamically.

Like this, we can create a dynamic drop-down list using the OFFSET and COUNTA functions.

Example #3: Allow Users To Enter Numbers Between Certain Ranges

For instance, if you want the user to enter the numbers between 1 and 100, you need to create this validation.

Step 1: Select the cell where you need to create a validation.

Step 2: Select Data Validation under the Data tab and choose the Whole Number option.

Step 3: As soon as we chose the Whole Number, the Data Validation window brought up three other options: Data, Minimum, and Maximum.

As we are trying to enter the values between 1 to 100, we will enter 1 in Minimum and 100 in Maximum boxes.

Step 4: Click on the OK tab, and we will have validation in cells A2:A7.

In this case, users will not know whether they need to enter the numbers between 1 to 100. So, we need to display the Input Message.

Step 5: Select cells A2:A7 and open the Data Validation window again.

Step 6: Click on the Input Message tab.

Step 7: Enter the validation message in the Input Message.

Step 8: Click on the OK button. We will see this input message whenever we select the validation cells A2:A7.

Like this, we can create various other data validation to make the data error-proof in excel.

How To Edit Excel Validation Rules?

If you are using an Excel file that already has some validation rules, you can still edit those rules. For instance, we have a drop-down list like the one below.

We can change the range of cells used for the drop-down validation.

Go to Data and click the Data Validation icon. It will open the Data Validation window.

You can see that it has the reference range of A2:A7. Click inside the Source box and choose the range of cells you want to include or exclude.

Click the OK button, and validation will be changed.

How To Remove Data Validation In Excel?

If you want to remove the validation from a specific cell or range of cells, follow these steps:

Step 1: Select the validation applied cell(s) where you want to remove the validation.

Step 2: It opens the Data Validation window. Click on Clear All.

It will remove the validation from the selected cells.

Important Things to Note

Data Validation will not allow users to enter any values outside the validation criteria provided.
If we copy the non-validation cell and paste it on the validation applied cell, we will lose validation from that cell. Unfortunately, excel will not give any warning, so users should be careful here.
Similarly, if we copy the validation applied cell and paste it on the non-validation cell, validation will apply for pasted cells.
It always displays the input message in the validation cell to make the user aware of the validation put in.

Frequently Asked Questions (FAQs)

What is Data Validation in Excel with example?

Data validation is the process of creating validation rules to restrict the users from entering only specific or pre-defined values from a drop-down list. For example, assume you need to allow the user to enter only text values in the cell, then you can create a custom validation:

ISTEXT function will evaluate the value entered in cell A1 and does not allow anything other than text.

Where is data validation in Excel?

Data validation is the feature available under the DATA table. Below is the image to navigate to data validation in excel.

Data >>> Drop Down of Data Validation >>> Data Validation

How to do data validation in Excel?

If you want to make the user enter the dates only on or before TODAY, you can create a validation rule like this:

It will make the user enter the dates only on or before TODAY.

How to use Data Validation in Excel for drop down list?

If you want to allow the user to enter the status of the project between “Completed,” “WIP,” and “Under Review,” you can create a drop-down list like the one below.

It will show only these values in the drop-down list.

Download Template

This article must be helpful to understand Data Validation in Excel, with its options and examples. You can download the template here to use it instantly.

Data-Validation-in-Excel Download

Data Validation In Excel