Data Validation in Excel: A Complete Guideline

Data validation in Excel refers to the process of restricting or controlling the types of data that can be entered into a cell or range of cells. It helps ensure that the data entered meets certain criteria, making it more accurate.

In this tutorial, you will learn everything about Data Validation from its purpose to how to apply it in your Excel worksheet.

As you can see in the image above, we have applied the data validation in the column named “Stock Quantity” thus it will store only numbers as input in the cells. But if you enter any text value it will show the message box saying, “This value doesn’t match the data validation restrictions defined for this cell”. That’s how data validation works.

⏷Data Validation in Excel
⏷Purposes for Using Data Validation
⏷Types of Data Validation Input
⏷1. How to Apply Data Validation in Excel?
⏵1.1. Apply Data Validation in Excel Cells
⏵1.2. Apply Multiple Data Validation Rules in One Cell
⏵1.3. Apply Data Validation Based on Another Cell
⏵1.4. Apply Data Validation with Checkbox
⏷2. Create a Drop-Down List with Data Validation
⏷3. Use Data Validation in Excel with Color
⏷4. Application of Custom Data Validation in Excel
⏵4.1. Allow Entries of Alphanumeric Characters Only with Data Validation
⏵4.2 Allow Numbers Only with Custom Data Validation
⏵Custom Excel Data Validation Rule Not Working
⏷5. Edit Data Validation
⏷6. Copy Data Validation to Other Excel Cells.
⏷Find Cells Containing Data Validation Rules
⏷Remove Data Validation in Excel?
⏷Useful Tips to Apply Data Validation
⏷Limitations of Data Validation
⏷Issues with Data Validation & How to Solve Them

Throughout this Excel blog post, you will learn how to

Apply Data Validation (In cells, Multiple Rules, Rules based on another cell, With a Checkbox)
Create a Drop-down list with Data Validation
Use Data Validation
Apply Custom Data Validation (Alphanumeric character only, Number Only)
Edit Data Validation
Find Data Validation
Remove Data Validation

📒Note: We have used Microsoft 365 to prepare the dataset for this article. You can apply the mentioned methods in versions from Excel 2007 onwards.

What is Data Validation in Excel?

Data validation in Excel is a feature that allows you to control the type of data entered into a cell. It helps maintain accuracy and consistency by setting rules for what can be input. For example, you can restrict entries to whole numbers, decimals, dates, or a predefined list.

What are the Purposes for Using Data Validation in Excel?

We can use Data Validation in our Excel worksheet because it-

stops us from entering incorrect data in cells.
Keeps our data uniformly formatted.
Simplifies and speeds up our data input.
Enhances our overall data reliability.
Creates a positive user experience.
Allows custom rules using formulas for our unique requirements.

What Are All Types of Data Validation Input in Excel?

In Microsoft Excel’s Data Validation feature, there are different tabs under which you can set various options. Here’s an overview of the options available under the Settings, Input Message, and Error Alert tabs:

1. Settings Tab
There are two input boxes that we must fill Allow and Data. We will use these two boxes to set up the rules for our data validation.

a) Allow Box:
You can choose the type of data validation:

Whole Number: Allows only whole numbers.
Decimal: Permits decimal numbers with a set precision.
List: Creates a drop-down list that allows you to select from the list.
Date: Ensures entries are within a specified date range.
Time: Allows time only.
Text Length: Controls the number of characters allowed.
Custom: This lets you set unique rules using a formula.

For a better understanding, you can have a look at the image below about the Allow box:

b) Data Box:
In the Data tab, you must define a criterion based on the validation you have selected in the Allow tab.

Whole Number: Specify minimum and maximum values.
Decimal: Set the number of allowed decimal places.
List: Create a list of acceptable entries.
Date/Time: Establish minimum and maximum date or time values.
Text Length: Determine the minimum and maximum character count.
Custom: Input a custom formula for validation.

Here is the image that shows the Data box:

2. Input Message Tab:
You can use this tab to customize your message to give any instructions. There are two boxes in this tab-

“Show input message when cell is selected” Box: Display a message when users select the validated cell.

“Title and Input Message” Box: Customize the title and message to offer guidance.

3. Error Alert Tab:
This “Error Alert” tab shows any sort of error message. You can customize your error message from here. You have to customize the three boxes mentioned below-

a) “Show error alert after invalid data is entered”: It Triggers an alert if users enter data that violates validation rules.

b) Style:
Choose the error alert type from this box:

Stop (prevents entry)
Warning (informs without blocking)
Information (provides information without blocking).

c)Title and Error Message: Customize the title and message for the error alert.

1. How to Apply Data Validation in Excel?

To apply data validation in Excel, start by selecting the cell or range where you want the validation. Then, go to the “Data” tab, click on “Data Validation,” and choose the validation type you need, such as Whole Number, List, or Date. Configure the criteria based on your requirements and click “OK” to apply the validation.

In this section, we will learn to apply Data Validation to cells, multiple criteria to cells, based on another cell, and apply Data Validation with the check box.

1.1. How to Apply Data Validation in Excel Cells?

In this portion, we will learn to apply data validation to cells (i.e., Range, or a column). We have a dataset as below, in this data set we will apply Data Validation in the column named “Date of Birth” thus this column stores dates only.

To apply this Data Validation in the “Date of Birth” column-

Select the range F6:F19.> Click on the Data tab> Data Validation.

Select Date in the Allow > Select “greater than” in the Data box.> Set 1/1/1900 (mm/dd/yyyy) as the Start date.> Click on OK.

This will enable the Data Validation for this range. It will accept those dates as inputs that you will type in the mm/dd/yyyy format. Otherwise, it will show an error message saying “This value doesn’t match the data validation restrictions defined for this cell.

If you enter the data as follows, it will allow each data.

1.2. How to Apply Multiple Data Validation Rules in One Cell?

We will use the OR function to apply multiple data validation rules in one cell. We have a dataset below where we will take input and validate it in cell E6 based on Criteria 1 (Price greater than 50) and Criteria 2 (Product only from the list).

To apply multiple data validation rules in cell E6:

Select cell E6.> Click on the Data tab> Data Validation.

Select Custom in the Allow > Use the formula in the Formula box.

=OR(E6>$B$6, COUNTIF($B$9:$B$12,E6)=1)

Click on OK.

This will enable the Data Validation in cell E6. For example, when you enter 40 in it, it will instantly show an error message. Because it does not satisfy any of the criteria.

When you enter 70 (Which is greater than 50), it will accept this value as valid data as it satisfies Criteria 1.

Similarly, when you enter Coconut in cell E6, it will also accept it. Because it satisfies the Criteria 2. That’s how we apply multiple criteria to a cell.

1.3. How to Apply Data Validation Based on Another Cell in Excel?

We will use the INDIRECT function to apply Data Validation based on another cell in Excel. Suppose we have data in cells F6:F11. We will use these data to validate the values in the column named “Designation”.

To apply Data Validation based on the values in cells F6:F11:

Select the range F5:F11.> Press Ctrl + T. > Check the “My table has headers” option.> Click on OK.

Select the created table.> Click on the Table Design > Set the Table name as “Info”.

Select the range D6:D16.> Click on the Data > Data Validation.

Select List in the Allow > Type the formula below in the Source box

=INDIRECT(“Info [Designation]”)

Click on OK.

This will create a list based on the table created. Now in the column “Designation”, use the list to input the data.

The column will look like in the dataset below.

1.4. How to Apply Data Validation with Checkbox in Excel?

You can also use Data Validation with the check box. If you use the check box, you have to click on the check box to activate the data validation of your requirements.

Imagine we have a dataset as below where we will apply Data Validation in the two columns named “Name”, and “Leave (Taken)” using the check box.

To activate Data Validation with the check box:

Click on the Developer > Insert.> Check Box.

Select the check box.> Right-click on the mouse.> Click on Edit Text in the menu.

Rename it as “Validate Name” as follows.

Similarly, follow the same process to add another check box and rename it as “Validate leave (Taken)” as shown in the image below.

Select the first check box.> Right-click on the mouse.> Click on Format Control.

Click on the Control tab in the Format Object dialogue box.> Select cell $G$6 in the Cell link> Click on OK.

As a result, if you click on the check box it will return TRUE, or it will return FALSE in the adjacent cell G6 if you uncheck it. Similarly, link G7 to the other check box named “Validate Leave (Taken)”.

Select the range C6:C16.> Click on the Data > Data Validation.

Select Custom in the Allow box.> Type the formula in the Formula

=IF($G$6, ISTEXT(C5:C16),FALSE)

Click on OK.

Now the Data Validation is activated. If you try to enter “Marylin Pittaman” in the “Name” column, it will return an error message box as we have not checked the “Validate Name” check box.

But when you check the “Validate Name” check box, it will take ‘Marylin Pittaman” as input. That’s how the check box validates the data entered in the cells of the “Name” column.

Similarly, validate the “Leave (Taken)” column as a whole number using the second check box.

Select the range D6:D16.> Click on the Data > Data Validation.> Select Custom in the Allow box.> Type the following formula in the Formula box.

=IF($G$7, ISNUMBER(D6:D16),FALSE)

Click on OK.

Now, when you uncheck the second check box, you cannot enter any number into the column “Leave (Taken)”.

But, If you check the “Validate Leave (Taken)” check box, it will allow you to enter any number into the cells.

By checking both checkboxes, you can enter the data in the columns. The result will be as the image below.

2. How to Create a Drop-Down List with Data Validation in Excel?

We can create a drop-down list using the Data Validation feature in Excel. A “drop-down list” in Excel is a menu of options that appears when you click on a cell. It helps in data entry by allowing you to choose from a predefined list of items.

Suppose we want to make a drop-down list as shown in the figure below from existing data in the sheet.

To create a drop-down list-

Select the range D6:D16.> Click on the Data > Data Validation.

Select List in the Allow > Click on the Source icon as shown.

Select the range F6:F11. These items will be added to the drop-down list.

Click on OK.

Click on any cell.> Icon.> Drop-down list will appear. From there you can select your desired items.

We have used this drop-down list to fill up the desired column as shown in the image below.

3. How to Use Data Validation in Excel with Color?

Here, we will learn how to use Data Validation in Excel with Color. We will use Data Validation to allow entries of only numbers and highlight them with color. Suppose we have a dataset below, in the “Acquired Number” column we will allow only the whole number (that is greater than 40) as input and highlight those values based on the condition using the Conditional Formatting.

To enable Data Validation for whole numbers:

Select the range D6:D16.> Click on the Data > Data Validation. > Select the Whole number in the Allow box. > Select between in the Data box.> Set 40 as Minimum and 100 as Maximum.> Click on OK.

This will enable the Data Validation for this range. You can now enter the number greater than 40. If you enter any number less than 40 (i.e.,34), it will return you an error message box.

Now, to highlight the numbers less than 60 and greater than 60 we will pick different colors in the conditional formatting tab.

To start adding color based on condition:

Select the range D6:D16.> Click on the Home > Conditional Formatting.> New Rule.

Click on “Format only cells that contain”.> Select between> Type the numbers 40 and 59 in the boxes under the “Format only cells within” option.> Click on Format to add color.

Click on Fill in the Format Cells dialogue box.> Select the color.> Click on OK.

Here, you will have a preview of the color that you are going to add. If you are sure to use this color to highlight numbers less than 60.

Click on OK.

In the column, it will highlight the numbers less than 60 with the selected color.

Similarly, we will highlight the numbers greater than 60 in the column. To do it-

Select the range D6:D16.> Click on the Home > Conditional Formatting.> New Rule.
Click on “Format only cells that contain”.> Select between> Type the numbers 60 and 100 in the boxes under the “Format only cells within” option.> Click on Format to add color.

Click on Fill in the Format Cells dialogue box.> Select the color.> Click on OK.

Hence, this will highlight the numbers greater than 60 with the selected color as shown in the image below.

4. Application of Custom Data Validation in Excel

We can also use Custom Data Validation to set the rules of our choices. To do this we have to set our rules/formula for the data from the Custom rules in the Data Validation dialogue box as shown in the image.

4.1. How to Allow Entries of Alphanumeric Characters Only with Data Validation?

We will use the Custom Data Validation feature to allow entries of Alphanumeric characters in the dataset. Alphanumeric characters are a combination of alphabetical (letters) and numerical (numbers) characters. We have a dataset below; we will add Alphanumeric Characters in the “Model” column.

To add Alphanumeric Characters in the column:

Select the range D6:D16.> Click on the Data > Data Validation.> Select Custom in the Allow box.> Use the following formula.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(B5, ROW(INDIRECT("1:"&LEN(D6))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Click on OK.

This will allow only Alphanumeric characters in the range.

We can only store the Alphanumeric characters in the cells.

4.2. Allow Numbers Only with Custom Data Validation in Excel

We will allow numbers only in the cells using the Custom Data Validation feature. Suppose we have a dataset as the image below. We want to allow only numbers in the column “Stock Quantity”.

To allow only numbers using custom Data Validation:

Select the range D6:D14.> Click on the Data > Data Validation.> Select Custom in the Allow box.> Use the following formula in the Formula box.

=ISNUMBER (D6:D14)

Click on OK.

This will allow only numbers into the cell of the column. Otherwise, it will show an error message box.

We have completed the dataset where the column “Stock Quantity” contains only numbers.

Custom Excel Data Validation Rule Not Working: Reasons & Solutions

If a custom Excel data validation rule is not working as expected, there could be a few reasons for this. Here are some common steps you can take:

Ensure that the custom formula you entered is correct. Double-check for any syntax errors or typos in the formula.
If your custom formula refers to other cells, make sure the cell references are accurate and that the referenced cells contain the expected values.
Verify that you’ve selected the correct cell range for applying the data validation rule.
Check if you’ve set up error alerts correctly in case the validation rule is violated. Sometimes, error alerts may not be visible, or the chosen alert style may not match the situation.
Ensure that the cell format is appropriate for the validation rule. For example, if your rule checks for a date, the cell should be formatted as a date.
If you are working with an older Excel version, be aware of compatibility mode issues. Certain features may not work as expected in compatibility mode.
If the worksheet is protected, check if data validation changes are allowed. Sometimes, protection settings may restrict the application of new rules.
Check for any conflicts with conditional formatting rules. Conflicting rules might override each other.

5. How to Edit Data Validation in Excel?

In this section, you will learn to edit existing Data Validation in Excel. We have a dataset as below. It has an existing drop-down list using the Data Validation. Now, we will edit it and add a new item “Representative” in the drop-down list.

To edit and add the new item “Representative” to the list-

Select any cell in the column “Designation”.> Add “, Representative” in the Source box.> Check the “Apply these changes to other cells with the same setting” to apply to the other cells.> Click on OK.

This will add the new item “Representative” to the list.

You can use this list to complete the data entry into the cell.

6. How to Copy Data Validation to Other Excel Cells?

You can’t copy Data Validation like regular copy and paste. Here, we will teach you to copy Data Validation to other cells. We have a dataset where the column “Stock Quantity” contains Data Validation with the rule of whole numbers only. We will copy this Data Validation to the column named “Sold Quantity”.

To copy Data Validation from “Stock Quantity” and paste it to the column “Sold Quantity”-

Select any cell of “Stock Quantity”.> Press Ctrl+C.> Select all the cells in the column “Sold Quantity”.> Right-click on the mouse.> Click on Paste Special from the menu.

Click on Validation under Paste > OK.

Now, if you enter the number in the cell of “Sold Quantity”, it will allow it. But if you enter any texts, an error message box will pop up.

You can enter the numbers in the cells of the column.

How to Find Cells Containing Data Validation Rules?

In this part, we will learn to find the Data Validation in the worksheet. Here in the image below, we have a dataset that contains Data Validation, but we don’t know in which column. So, we will find in which column the data Validation is.

To find the Data Validation in the worksheet:

Click on the Home > Find & Select.> Data Validation.

This will select the column or cells that contain Data Validation. (i.e., D6:D16)

How to Remove Data Validation in Excel?

In this section, you will learn to remove any existing Data Validation in Excel. We have a dataset below that contains Data Validation in the “Designation” column. We will learn to remove this Data Validation.

To remove the Data Validation:

Select the range D6:D16 in the column.> Click on the Data > Data Validation.> Click on “Clear All” in the Data Validation dialogue box.> OK.

This will remove the existing Data Validation in the selected cells.

Useful Tips to Apply Data Validation in Excel

Here are quick and useful tips for applying data validation in Excel:

Use dropdowns for consistent and error-free entries.
Provide informative messages for user guidance.
Configure alerts for invalid entries.
Use named ranges for flexible dropdowns.
Employ formulas for specific validations.
Consider protecting sheets to enforce rules.
Thoroughly test before finalizing rules.

What are the Limitations of Data Validation in Excel?

Data Validation is a useful tool in Excel, but it does have some limitations:

Errors are detected only after moving to the next cell.
Rules need to be set up individually for each sheet.
Rules apply at the cell level, not across multiple cells.
Dropdowns don’t automatically update if the source list changes.
Built-in date range options are somewhat limited.
Customization options for error alerts are limited.
No built-in option to restrict or allow specific special characters.

Issues with Data Validation & How to Solve Them

There are some other issues like Data Validation not working properly for Copy&Paste, and Data Validation Greyed out in Excel. You can go through the articles to learn about these common issues and learn how to fix them.

Download Practice Workbook

Data Validation.xlsx

In this guide, we covered creating drop-down lists, using custom rules (like allowing only letters or numbers), editing, finding, and removing these rules of Data Validation. We also discussed useful tips and solutions to common issues you might encounter while working with Data Validation.

Hopefully, this article was helpful and easy to understand those methods. Yet, if you have any problem, please drop your comments below.

Data Validation in Excel: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!