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:

Data Validation in Excel Intro

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:

Data Validation in Excel Intro 1

Thus, we can make users enter error-proof values.

Key Takeaways
  • Data validation controls what users are entering, thus making the textual or numerical input error-proof.
  • Users can give additional criteria to Whole Number, Decimal Number, Date, 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 want to restrict the data entry, we will have to choose the kind of validation 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

Excel Tab

Below are the options under data validation:

Data Validation Options

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.

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.

  1. Select the cell or range of cells where we need the data validation. In this case, we have selected cells from A2:A6.


    Steps 1

  2. Go to the Data tab and choose the Data Validation option.


    Excel Tab

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


     Steps 2

  4. In the Source box, enter the month names. Each month should be separated by a comma (,).


    Steps 3

  5. Click on the OK button, and we have our drop-down list ready in cells A2:A6.


    Steps 4

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.

Excel Example 1.1

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

Excel Exampl 1.2

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

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

Excel Tab

Step 4: Choose List from validation options.

Excel Example 1.3

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

Excel Example 1.4

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

Excel Example 1.5

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

Excel Example 1.6

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.

In Data Validation Source box, enter the OFFSET formula.

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

 Excel Example 2.1

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.

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

Excel Example 1.5

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

Data Validation in Excel Example 2.2

Like this, we can create a dynamic drop-down list using the OFFSET and COUTNA 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.

Data Validation in Excel Example 3.1

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

Excel Example 3.2

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.

Excel Example 3.3

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

 Excel Example 3.4

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.

Excel Example 3.5

Step 6: Click on the Input Message tab.

Data Validation in Excel Example 3.6

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

Excel Example 3.7

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

Excel Example 3.8

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.

Edit Data Validation in Excel 1

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.

Excel 1.1

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.

Edit Data Validation in Excel 1.2

Click the OK button, and validation will be changed.

Edit Data Validation in Excel 1.3

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.

Remove Data Validation

It will remove the validation from the selected cells.

Important Notes

  • 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:
Data Validation FAQ 1.1

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

Data Validation FAQ 1.3

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
Excel Tab

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:
Data Validation FAQ 3.1

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.
Data Validation FAQ 4.1

It will show only these values in the drop-down list.
Data Validation FAQ 4.2

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.

This has been a guide to Data Validation in Excel. Here we learn how to create, edit, customize, & remove it, with examples & a downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.