Skip to main content

Excel data validation: add, use, copy and remove data validation in Excel

Author Xiaoyang Last modified

In Excel, the Data Validation feature is a powerful tool that you can use to limit what a user can enter into a cell. For instance, you can set rules to limit the length of text, restrict entries to specific formats, enforce unique values, or ensure text begins or ends with certain characters. These capabilities help maintain data integrity and reduce errors in your worksheets.

This tutorial explores how to add, use, and remove data validation in Excel. It covers both fundamental and advanced operations, providing detailed step-by-step guidance to help you effectively apply this feature to your tasks.

Table of contents:

1. What is data validation in Excel?

2. How to add data validation in Excel?

3. Basic examples for data validation

4. Advanced custom rules for data validation

5. How to edit the data validation in Excel?

6. How to find and select cells with data validation in Excel?

7. How to copy the data validation rule to other cells?

8. How to use data validation to circle invalid entries in Excel?

9. How to remove data validation in Excel?


1. What is data validation in Excel?

The "Data Validation" feature can help you to restrict input content in your worksheet. Normally, you can create some validation rules for preventing or allowing only some kind of data to be entered in a list of selected cells.

Some basic usages of the Data Validation feature:

  • 1. "Any value": no validation is performed, you can input anything into the specified cells.
  • 2. "Whole value": only whole numbers are allowed.
  • 3. "Decimal": allow whole numbers as well as decimals to be entered.
  • 4. "List": only values from the predefined list are allowed to be entered or selected. The values are displayed in a dropdown list.
  • 5. "Date": only dates are allowed.
  • 6. "Time": only times are allowed.
  • 7. "Text length": only allow specified length of the text to be entered.
  • 8. "Custom": create custom formula rules for validating users’ input.

 2. How to add data validation in Excel?

In Excel worksheet, you can add data validation with following steps:

1. Select a list of cells where you want to set the data validation, and then click "Data" > "Data Validation" > "Data Validation", see screenshot:

2. In the "Data Validation" dialog box, under the "Settings" tab, please create your own validation rules. in the criteria boxes, you can supply any of the following types:

  • "Values": Type numbers in the criteria boxes directly;
  • "Cell reference": Reference a cell in the worksheet or another worksheet;
  • "Formulas": Create more complex formulas as conditions.

As an example, I will create a rule that allow only whole numbers between 100 and 1000 to be entered, here set the criteria as below screenshot shown:

3. After configuring the conditions, you can go to the "Input Message" or "Error Alert" tab to set the input message or error alert for the validation cells as you like. (If you don’t want to set the alert, please click "OK" to finish directly.)

3.1) Add input message(optional):

You can create a message that appears when selecting a cell containing data validation. This message helps to remind the user what they can input into the cell.

Go to the "Input Message" tab and do the following:

  • Check the "Show input message when cell is selected" option;
  • Enter the title and reminder message that you want into the corresponding fields;
  • Click "OK" to close this dialog box.

Now, when you select a validated cell, a message box will be shown as follows:

3.2) Create meaningful error messages (optional):

In addition to creating the input message, you can also show error alerts when invalid data is entered into a cell with data validation.

Go to the "Error Alert" tab of the "Data Validation" dialog box, please do as this:

  • Check the "Show error alert after invalid data is entered" option;
  • In the "Style" drop down list, select one desired alert type you need:
    • "Stop (default)": This alert type prevents users from entering invalid data.
    • "Warning": Warns users that the data is invalid, but does not prevent entering it.
    • "Information": Informs users about an invalid data entry only.
  • Enter the title and alert message that you want into the corresponding fields;
  • Click "OK" to close the dialog box.

When an invalid value is entered, an alert box will appear, as shown in the screenshot below:

"Stop" option: Click "Retry" to re-enter a value or "Cancel" to discard the entry.

"Warning" option: Click "Yes" to accept the invalid entry, "No" to modify it, or "Cancel" to discard it.

"Information" option: Click "OK" to accept the invalid entry or "Cancel" to discard it.

Note: If you don’t set your own custom message in the "Error Alert" box, a default "Stop" alert box will be displayed, as shown below:

A screenshot of the default Stop alert box in Excel's data validation


 3. Basic examples for data validation

When using this Data Validation feature, there are 8 built-in options provided for you to set the data validation. Such as: any value, whole numbers and decimals, date and time, list, text length and custom formula. In this section we will discuss how to use some of the built-in options in Excel?

3.1 Data validation for whole numbers and decimals

1. Select a list of cells where you want to allow only whole numbers or decimals, and then click "Data" > "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select the corresponding item "Whole number" or "Decimal" in the "Allow" drop down box.
  • And then, choose one of the criteria in the "Data" box (In this example, I choose the "between" option).
  • Tips: The criteria contain: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.
  • Next, enter the "Minimum" and "Maximum" values you need (in this case, numbers between 0 and 100).
  • At last, click "OK" button.

3. Now, only whole numbers between 0 and 100 can be entered in the selected cells.


3.2 Data validation for date and time

To validate specific date or time to be entered, it is easy by using this "Data Validation", please do as follows:

1. Select a list of cells where you want to allow only the specific dates or times, and then click "Data" > "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select the corresponding item "Date" or "Time" in the "Allow" drop down box.
  • And then, choose one of the criteria in the "Data" box (Here I choose the "greater than" option).
  • Tips: The criteria contain: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.
  • Next, enter the "Start date" you need (I want the dates greater than 8/20/2021).
  • At last, click "OK" button.

3. Now, only dates greater than 8/20/2021 can be entered in the selected cells.


3.3 Data validation for text length

If you need to limit the number of characters that can be typed into a cell. For example, to limit the content to no more than 10 characters for a particular range, this "Data Validation" also can do you a favor.

1. Select a list of cells where you want to limit text length, and then click "Data" > "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Text length" from the "Allow" drop down box.
  • And then, choose one of the criteria in the "Data" box (In this example, I choose the "less than" option).
  • Tips: The criteria contain: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.
  • Next, enter the "Maximum" number you need to limit (I want the text length no more than 10 characters).
  • At last, click "OK" button.

3. Now, the selected cells only allow text strings with fewer than 10 characters to be entered


3.4 Data validation list (drop-down list)

With this powerful "Data Validation" feature, you can create drop-down list in cells quickly and easily as well. Please do as this:

1. Select the target cells where to insert the drop-down list, and then click "Data" > "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "List" from the "Allow" drop down list.
  • In the "Source" text box, type the list items directly separated by commas. For example, to limit the user input to three choices, type Not Started,In Progress,Completed, or you can select a list of cells containing the values to insert the drop-down based on.
  • At last, click "OK" button.

3. Now, the drop-down list has been created into the cells as below screenshot shown:

Click to know more detailed information of drop-down list…


4. Advanced custom rules for data validation

This section, I will introduce how to make some advanced custom data validation rules to solve your kinds of problems, such as: create validation formulas to allow only numbers or text strings, only unique values, only specified phone numbers, email addresses and so on.

4.1 Data validation allow numbers or texts only

Allow only numbers to be entered with Data Validation function

To allow only numbers in a range of cells, please do as this:

1. Select a range of cells that you want only numbers to be entered.

2. Click "Data" > "Data Validation" > "Data Validation". In the "Data Validation" dialog box that appears, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the following formula into the "Formula" text box. ("A2" is the first cell of the selected range you want to limit)
    =ISNUMBER(A2)
  • Click "OK" button to close this dialog.

3. From now on, only numbers can be entered into the selected cells.

Note: This" ISNUMBER" function allows any numeric values in validated cells, including integers, decimals, fractions, dates and times.


Allow only text strings to be entered with Data Validation function

To restrict cell entries to text only, you can use the "Data Validation" feature with a custom formula based on the "ISTEXT" function, please do as this:

1. Select a range of cells that you want only text strings to be entered.

2. Click "Data" > "Data Validation" > "Data Validation". In the "Data Validation" dialog box that appears, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the following formula into the "Formula" text box. ("A2" is the first cell of the selected range you want to limit)
    =ISTEXT(A2)
  • Click "OK" button to close this dialog.

3. Now, when inputting data into the specific cells, only text format data can be allowed.


4.2 Data validation allow alphanumeric values only

For some purposes, you may want to allow only alphabets and numeric values while restricting special characters such as ~, %, $, or spaces. This section introduces some useful methods.

 Allow only alphanumeric values with Data Validation function

To prevent special characters and allow only alphanumeric values, create a custom formula in the "Data Validation" function by following these steps:

1. Select a range of cells that you want only alphanumeric values to be entered.

2. Click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Click "OK" button to close this dialog.

Note: In the above formulas, "A2" is the first cell of the selected range you want to limit.

3. Now, only the alphabets and numeric values are permitted to be entered, and the special characters will be restricted when typing as below screenshot shown:


 Allow only alphanumeric values by using an amazing feature

The formula above may seem complicated to understand and remember. Here, I introduce a handy feature called "Prevent Typing" from "Kutools for Excel", which simplifies this task significantly.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select a range of cells that you want only alphanumeric values to be entered.

2. Then, click "Kutools" > "Prevent Typing" > "Prevent Typing", see screenshot:

3. In the popped out "Prevent Typing" dialog box, select "Prevent type in special characters" option, see screenshot:

4. Then, click "Ok" button, and in the following prompt boxes, click "Yes" > "OK" to finish the operation. Now, in the selected cells, only the alphabets and numeric values are allowed, see screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


4.3 Data validation allow texts begin or end with specific characters

If all values in a certain range must begin or end with a specific character or substring, you can use data validation with a custom formula based on the EXACT, LEFT, RIGHT, or COUNTIF functions.

 Allow texts begin or end with specific characters with only one condition

For example, if you want text entries in specific cells to begin or end with “CN,” follow these steps:

1. Select a range of cells that only allow texts that begin or end with certain characters.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the" Formula" text box.
    Only allow entering texts that begins with CN:
    =EXACT(LEFT(A2,2),"CN")
    Only allow entering text that ends with CN:
    =EXACT(RIGHT(A2,2),"CN")
  • Click "OK" button to close this dialog.

Note: In the above formulas, "A2" is the first cell of the selected range, the number "2" is the number of characters you specified, "CN" is the text you want to begin or end with.

3. From now on, only the text string begins or ends with the specified characters can be entered into the selected cells. Otherwise, a warning alert will appear, as shown in the screenshot below:

Tips: The above formulas are case sensitive, if you don’t need case-sensitive, please apply the below CONTIF formulas:

Only allow entering text that begins with CN (non case sensitive):
=COUNTIF(A2,"CN*")
Only allow entering text that ends with CN (non case sensitive):
=COUNTIF(A2,"*CN")

Note: The asterisk * is a wildcard that matches one or more characters.


 Allow texts begin or end with specific characters with multiple criteria (OR logic)

For example, if you want text entries to begin or end with “CN” or “UK” as below screenshot shown, you need to add another instance of EXACT by using a plus (+) sign. Please do with the following steps:

1. Select a range of cells that only allows texts that begin or end with multiple criteria.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    Only allow entering text that begins with CN or UK:
    =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    Only allow entering text that ends with CN or UK:
    =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • Click "OK" button to close this dialog.

Note: In the above formulas, "A2" is the first cell of the selected range, the number "2" is the number of characters you specified, "CN" and "UK" are the specific texts you want to begin or end with.

3. Now, only the text string begins or ends with the specified characters can be entered into the selected cells.

Tips: To ignore case-sensitive, please apply the below CONTIF formulas:

Only allow entering text that begin with CN or UK (non case sensitive):
=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
Only allow entering text that ends with CN or UK (non case sensitive):
=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Note: The asterisk * is a wildcard that matches one or more characters.


4.4 Data validation allow entries must contain / must not contain specific text

This section, I will talk about how to apply the Data Validation to allow values must contain or must not contain one specific substring or one of many substrings in Excel.

 Allow entries must contain one or one of many specific texts

Allow entries must contain one specific text

To allow entries that contain a specific text string, for example, all the entered values should contain the text “KTE” as below screenshot shown, you can apply the data validation with a custom formula based on the FIND and ISNUMBER functions. Please do as this:

1. Select a range of cells that only allows texts that contain certain text.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the Allow drop-down list.
  • And then, enter one of the below formulas into the "Formula" text box.
    Case sensitive:
    =ISNUMBER(FIND("KTE",A2)) 
    Non case sensitive:
    =ISNUMBER(SEARCH("KTE",A2))
  • Click "OK" button to close this dialog.

Note: In the above formulas, "A2" is the first cell of the selected range, the text "KTE" is the text string the entries must contain.

3. Now, when the entered value does not contain the required text, a warning prompt box will pop up.


Allow entries must contain one of many specific texts

The above formula only works for one text string, if you need any one of several text strings to be allowed in the cells, as shown in the following screenshot, you should use the SUMPRODUCT, FIND and ISNUMBER functions together to create a formula.

1. Select a range of cells that only allows texts that contain any one of many items.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • Then, enter one of the below formulas as you need into the "Formula" text box.
    Case sensitive:
    =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0
    Non case sensitive:
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0
  • And then, click "OK" to close the dialog box.

Note: In the above formulas, "A2" is the first cell of the selected range, "C2:C4" is the list of values you want to allow entries contain any one of them.

3. And now, only the entries contain any one of the values in the specific list can be entered.


Allow entries must not contain one or one of many specific texts

Allow entries must not contain one specific text

To validate the entries must not contain specific text, for example, to allow values that must not contain the text “KTE” in a cell, you can use the ISERROR and FIND functions to create a data validation rule. Please do as this:

1. Select a range of cells that only allows texts that do not contain certain text.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter one of the below formulas into the "Formula" text box.
    Case sensitive:
    =ISERROR(FIND("KTE",A2))
    Non case sensitive:
    =ISERROR(SEARCH("KTE",A2))
  • Click "OK" button to close this dialog.

Note: In the above formulas, "A2" is the first cell of the selected range, the text "KTE" is the text string the entries must not contain.

3. Now, entries containing the specific text will be prevented from being entered.


Allow entries must not contain one of many specific texts

To prevent any one of many text strings in a list from being entered, as shown in the screenshot below, follow these steps:

1. Select a range of cells that you want some texts to be prevented.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, please do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • Then, enter the below formula into the" Formula" text box.
    Case sensitive:
    =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0
    Non case sensitive:
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0
  • And then, click "OK" to close the dialog box.

Note: In the above formulas, "A2" is the first cell of the selected range," C2:C4" is the list of values you want to prevent if entries contain any one of them.

3. From now on, entries containing any of the specific texts will be prevented from being entered.


4.5 Data validation allow only unique values

If you want to prevent duplicate data from being entered into a range of cells, this section introduces some quick methods for solving this task in Excel.

Allow only unique values by using Data Validation function

Normally, the Data Validation feature with a custom formula based on the COUNTIF function can help you, please do with the following steps:

1. Select the cells or column that you only want unique values to be entered.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =COUNTIF($A$2:$A$9,A2)=1
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2:A9" is the range of cells that you want to only allow unique values, and "A2" is the first cell of the selected range.

3. Now, only unique values can be entered, and a warning message will appear if duplicate data is input, as shown in the screenshot below:


 Allow only unique values by using VBA code

The following VBA code also can help you to prevent duplicate values from being entered, please do as this:

1. Right click the sheet tab that you want to allow only unique values, and choose the "View Code" from the context menu, in the pop-up "Microsoft Visual Basic for Applications" window, please copy and paste the following code into the blank Module:

VBA code: Only allow unique values in a range of cells:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub
A screenshot of the View Code option on the sheet tab context menu Arrow A screenshot of the pasted code in the code editor

Note: In the above code, the "A1:A100" and" A:A" are the cells in the column that you want to prevent duplicate, please change them to your need.

2. Then save and close this code. Now, when entering a duplicate value in cells A1:A100, a warning prompt box appears, as shown in the screenshot below:

A screenshot of a warning prompt box when duplicate values are entered in cells A1:A100


Allow only unique values by using a handy feature

If you have "Kutools for Excel", with its "Prevent Duplicate" feature, you can quickly set data validation to prevent duplicates in a range of cells with just a few clicks.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the range of cells that you want to prevent the duplicate values but allow only unique data.

2. Then, click "Kutools" > "Prevent Typing" > "Prevent Duplicate", see screenshot:

3. A warning message will appear, notifying you that Data Validation will be removed when applying this feature. Click "Yes", and in the subsequent prompt box, click "OK", as shown in the screenshots below:

4. Now, when you enter some duplicate data in your specified cells, a prompt box is displayed to remind you the duplicate data is not valid, see screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


4.6 Data validation allow uppercase / lowercase / proper case only

The Data Validation feature is a powerful tool that can help users enforce uppercase, lowercase, or proper case entries in a range of cells. Please do with the following steps:

1. Select the range of cells that you only want uppercase, lowercase or proper case text to be entered.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter one of the below formulas that you need into the "Formula" text box.
    only allow uppercase text:
    =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))
    Only allow lowercase text
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))
    Only allow proper case text
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, only the entries that conform to the rule you created will be accepted.


4.7 Data validation allow values that exist / not exist in another list

Allowing or preventing values based on their presence in another list can be a challenging task for many users. In fact, you can use data validation feature with a simple formula based on the COUNTIF function to deal with it.

For example, I want only the values in the range C2:C4 to be entered in a range of cells as below screenshot shown, to solve this job, please do as this:

1. Select the range of cells that you want to apply the data validation.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter one of the below formulas that you need into the "Formula" text box.
    Only allow values exist in another column
    =COUNTIF($C$2:$C$4,A2)>0
    Prevent values exist in another column
    =COUNTIF($C$2:$C$4,A2)=0
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use, "C2:C4" is the list of values you want to prevent or allow if entries are one of them.

3. Now, only the entries that conform to the rule you created can be entered; others will be prevented.


4.8 Data validation force only phone number format to be entered

When you input the information of your company employees, one column needs to type the phone number, for ensuring to input phone numbers quickly and accurately, in this case, you can set data validation for the phone numbers. For example, I just want the phone number as this format (123) 456-7890 to be allowed entering in a worksheet, this section will introduce two quick tricks for solving this task.

 Force only phone number format with Data Validation function

To allow only a specific phone number format to be entered, follow these steps:

1. Select the list of cells that you want specific phone number format to be entered, and then right click, choose" Format Cells" from the context menu, see screenshot:

2. In the "Format Cells" dialog box, under the "Number" tab, select "Custom" in the left "Category" list box, and then input the phone number format you need into the Type text box, for example, I will use this "(###) ###-####" format, see screenshot:

3. Then, click "OK" to close the dialog box.

4. After formatting the cells, select them again and open the "Data Validation" dialog box by clicking "Data" > "Data Validation" >" Data Validation". In the pop-up dialog, under the" Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the following formula into the Formula text box.
    =AND(ISNUMBER(A2),LEN(A2)=10)
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to validate the phone number.

5. Now, when entering a 10-digit number, it will be converted to the specific phone number format automatically as you need, see screenshots:

Note: If the entered number is not 10 digits, a warning message box will pop out to remind you, see screenshot:


 Force only phone number format by using a useful feature

"Kutools for Excel"’s "Validate Phone Number" feature also can help you to force only phone number format to be entered with only several clicks.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the list of cells that allow only specific phone number, then, click "Kutools" > "Prevent Typing" > "Validate Phone Number", see screenshot:

2. In the "Phone number" dialog box, select the specific phone number format you need or you can create your own formatting by clicking the "Add" button, see screenshot:

3. After selecting or setting the phone number formatting, click "OK", now, only the phone number with the specific formatting can be entered, otherwise, a warning message will pop out to remind you, see screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


4.9 Data validation force only Email addresses to be entered

Supposing, you need to type multiple email addresses in a column of a worksheet, to prevent some incorrect email addresses formatting to be entered, normally, you can set a data validation rule for allowing only email addresses formatting.

Force only Email addresses format by using Data Validation function

By using the Data Validation feature with a custom formula, you can quickly create a rule to prevent invalid email addresses from being entered. Follow these steps:

1. Select the cells that you want only email addresses to be entered, then click "Data" > "Data Validation" > "Data Validation".

2. In the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select" Custom" from the "Allow" drop-down list.
  • And then, enter the following formula into the Formula text box:
    =ISNUMBER(MATCH("*@*.?*",A2,0))
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, if the entered text does not match the email address format, a warning message box will appear to notify you, see screenshot:


 Force only Email addresses format by using a handy feature

"Kutools for Excel" supports an amazing feature – "Validate Email Address", with this utility, you can prevent the invalid email addresses with only one click.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells that you only allow email addresses to be entered, then click "Kutools" > "Prevent Typing" > "Validate Email Address". See screenshot:

2. And then, only email address formatting allows to be entered, otherwise, a warning message box will pop out to remind you, see screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


4.10 Data validation force only IP addresses to be entered

This section, I will introduce some quick tricks to set data validation to accept only IP addresses in a range of cells.

Force only IP addresses format with Data Validation function

To allow only IP addresses to be entered in a specific range of cells, follow these steps:

1. Select the cells that you want only IP address to be entered, then click "Data" > "Data Validation" > "Data Validation".

2. In the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, if an invalid IP address is entered into the cell, a warning message box will appear, as shown in the screenshot below:


 Force only IP addresses format with VBA code

Here, the following VBA code also can help to allow only IP addresses to be entered and restrict other inputting, please do as this:

1. Right click the sheet tab and click "View Code" from the context menu, in the opening "Microsoft Visual Basic for Applications" window, copy the below VBA code into it.

VBA code: validate cells to only accept IP address

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub
A screenshot of the View Code option on the context menu Arrow A screenshot showing the VBA editor with the IP address validation code added to a worksheet

Note: In the above code, "A2:A10" is the cell range you want to accept only IP addresses.

2. Then save and close this code. Now, only valid IP addresses can be entered into the specified cells.


Force only IP addresses format by using an easy feature

If you have "Kutools for Excel" installed in your workbook, its "Validate IP Address" feature can help you to solve this task as well.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells that you only allow IP addresses to be entered, then click "Kutools" > "Prevent Typing" > "Validate IP Address". See screenshot:

2. After applying this feature, now, only IP address allows to be entered, otherwise, a warning message box will pop out to remind you, see screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


4.11 Data validation restrict values that exceed the total value

Supposing, you have a monthly expense report, and the budget total is $18000. You need to ensure that the total amount in the expense list does not exceed this preset total, as shown in the screenshot below. In this case, you can create a data validation rule by using the SUM function to prevent the sum of values from being exceeded a preset total.

1. Select the list of cells where you want the values to be limited.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =SUM($B$2:$B$7)<=18000
  • Click "OK" button to close this dialog.

Note: In the above formula, "B2:B7" is the range of cells you want to limit entries.

3. Now, when entering the values in the range B2:B7, if the total of the values is less than $18000, the validation passes. If any value causes the total to exceed $18,000, a warning message box will appear to notify you.


4.12 Data validation restrict cell entry based on another cell

When you want to limit data entries in a list of cells based on the value in another cell, the Data Validation feature can help to solve this job as well. For example, if the cell C1 is the text “Yes”, the range A2:A9 allows any entries. However, if cell C1 contains any other text, entries in the range A2:A9 are restricted, as shown in the screenshots below:

To solve this solve, please do as this:

1. Select the list of cells where you want the values to be limited.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =$C$1="Yes"
  • Click "OK" button to close this dialog.

Note: In the above formula, "C1" is the cell contain the specific text you want to use, and the text “Yes” is the text you want to limit cells based on, please change them to your need.

3. Now, if cell C1 has the text “Yes”, anything can be entered into the range A2:A9, if cell C1 has other text, you will not be able to enter any value, see below demo:


4.13 Data validation allow only weekdays or weekends to be entered

If you need only weekdays (from Monday to Friday) or weekends (Saturday and Sunday) to be entered in a list of cells, the "Data Validation" also can help you, please do with the following steps:

1. Select the list of cells where you want the weekdays or weekdays to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter one of the below formulas into the "Formula" text box as you need.
    Allow only weekdays
    =WEEKDAY(A2,2)<6
    Allow only weekends
    =WEEKDAY(A2,2)>5
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, you can only enter weekday or weekend dates in the specified cells based on your selection.


4.14 Data validation allow entered date based on today’s date

Sometimes, you may need to allow only the dates greater or less than today to be entered in a list of cells. The "Data Validation" feature with the "TODAY" function can do you a favor. Please do as this:

1. Select the list of cells where you want only the future date (date greater than today) to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the "Allow" drop-down list.
  • And then, enter the below formula into the "Formula" text box.
    =A2>Today()
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, only dates later than today can be entered into the cells. Otherwise, a warning message box will appear to notify you, see screenshot:

Tips:

1. To allow the past date (date less than today) to be entered, please apply the below formula into the Data Validation:

=A2<Today()

2. Allow date within a specific range of date to be entered, such as the dates in the next 30 days, please enter the below formula into the Data Validation:

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Data validation allow entered time based on current time

If you want to validate data based on the current time, for example, only times before or after current time can be typed into the cells. You can create your own data validation formula, please do as this:

1. Select the list of cells where you want only the times before or after current time to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Time" from the "Allow" drop-down list.
  • Then choose" less than" to allow only times before the current time, or "greater than" to allow times after the current time as you need from the "Data" drop down.
  • And then, in the "End time" or "Start time" box, enter the below formula:
    =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use.

3. Now, only the times before or after the current time can be entered into the specific cells.


4.16 Data validation the date of specific or current year

To allow only dates in a certain year or current year to be entered, you can use data validation with a custom formula based on the YEAR function.

1. Select the list of cells where you want only the dates in a certain year to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Custom" from the" Allow" drop-down list.
  • And then, enter the below formula into the" Formula" text box.
    =YEAR(A2)=2020
  • Click "OK" button to close this dialog.

Note: In the above formula, "A2" is the first cell of the column you want to use, "2020" is the year number you want to restrict.

3. And then, only the dates in year 2020 can be entered, if not, a warning message box will pop out as below screenshot shown:

Tips:

To allow only dates in the current year, you can apply the below formula into the data validation:

=YEAR(A2)=YEAR(TODAY())

4.17 Data validation the date in current week or month

If you want to allow users to enter dates for the current week or month in specific cells, this section introduces some formulas to accomplish this task in Excel.

Allow to enter date of current week

1. Select the list of cells where you want only the dates in current week to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Date" from the "Allow" drop-down list.
  • And then, choose the "between" from the "Data" drop down.
  • In the "Start date" text box, enter this formula:
    =TODAY()-WEEKDAY(TODAY(),3)
  • In the "End date" text box, enter this formula:
    =TODAY()-WEEKDAY(TODAY(),3)+6
  • At last, click "OK" button.

3. Then, only the dates within current week can be entered, other dates will be prevented as below screenshot shown:


 Allow to enter date of current month

To only allow the dates of current month to be entered, please do as follows:

1. Select the list of cells where you want only the dates in current month to be entered.

2. Then click "Data" > "Data Validation" > "Data Validation", in the popped out "Data Validation" dialog box, under the "Settings" tab, do the following operations:

  • Select "Date" from the "Allow" drop-down list.
  • And then, choose between from the "Data" drop down.
  • In the "Start date" text box, enter this formula:
    =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
  • In the "End date" text box, enter this formula:
    =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))
  • At last, click "OK" button.

3. From now on, only dates within the current month can be entered into the selected cells.


5. How to edit the data validation in Excel?

To edit or change an existing data validation rule, follow the steps below:

1. Select any one of the cells with the data validation rule.

2. Then, click "Data" > "Data Validation" > "Data Validation" to go to the "Data Validation" dialog box, in the box, edit or change the rules to your need, and then check "Apply these changes to all other cells with the same settings" option to apply this new rule to all other cells with the original validation criteria. See screenshot:

3. Click "OK" to save the changes.


6. How to find and select cells with data validation in Excel?

If you have created multiple data validation rules in your worksheet, now, you need to find and select the cells that applied the data validation rules, the "Go To Special" command can help you to select all kinds of data validation or specific type of data validation.

1. Activate the worksheet that you want to find and select the cells with data validation.

2. Then, click "Home" > "Find & Select" > "Go To Special", see screenshot:

3. In the "Go To Special" dialog box, select "Data validation" > "All", see screenshot:

4. All cells with data validation have now been selected in the current worksheet.

Tips: If you want to select a specific type of data validation, first, select a cell containing the desired data validation, then go to the "Go To Special" dialog box, and choose the "Data validation" > "Same".


7. How to copy the data validation rule to other cells?

Supposing, you have created a data validation rule for a list of cells, and now, you need to apply the same data validation rule to other cells. Instead of creating the rule again, you can copy and paste the existing rule to other cells quickly and easily.

1. Click to select one cell with the validation rule you want to use, and then press "Ctrl + C" to copy it.

2. Then, select the cells you want to validate, to select multiple non-adjacent cells, press and hold the "Ctrl" key while selecting the cells.

3. And then, right-click the selection, select" Paste Special" option, see screenshot:

4. In the "Paste Special" dialog box, select" Validation" option, see screenshot:

5. Click "OK" button, now the validation rule is copied to the new cells.


8. How to use data validation to circle invalid entries in Excel?

Sometimes, you may need to create data validation rules for existing data, in this case, some invalid data may appear in the range of cells. How to check the invalid data and modify them? In Excel, you can use the "Circle Invalid Data" feature to highlight the invalid data with a red circle.

To circle the invalid data you need, you should apply the "Data Validation" feature to set a rule for the data range. Please do with the following steps:

1. Select the data range that you want to circle invalid data.

2. Then, click "Data" > "Data Validation" > "Data Validation", in the" Data Validation" dialog box, set the validation rule to your need, for example, here, I will validate the values greater than 500, see screenshot:

3. Then, click "OK" to close the dialog box. After setting the data validation rule, click "Data" > "Data Validation" > "Circle Invalid Data", then all the invalid values which are less than 500 have been circled with red oval. See screenshots:

Notes:

  • 1. As soon as you correct an invalid data, the red circle will be gone automatically.
  • 2. This "Circle Invalid Data" feature only can circle 255 cells at most. When you save the current workbook, all the red circles will be removed.
  • 3. These circles are non-printable.
  • 4. You can also remove the red circles by clicking" Data" > "Data Validation" > "Clear Validation Circles".

9. How to remove data validation in Excel?

To remove data validation rules from a range of cells, the current worksheet, or the entire workbook, use the following methods.

Remove data validation in selected range with data validation function

1. Select the cells with data validation you want to remove.

2. Then click" Data" > "Data Validation" > "Data Validation", in the popped out dialog box, under the "Settings" tab, click "Clear All" button, see screenshot:

3. Then, click "OK" button to close this dialog box. And the data validation rule applied to the selected range have been removed at once.

Tips: To remove the data validation from the current worksheet, please just select the whole sheet first, and then apply above steps.


 Remove data validation in selected range with a handy feature

If you have "Kutools for Excel", its "Clear Data Validation Restrictions" feature also can help to remove the data validation rules from selected range or the whole worksheet.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the range of cells or the whole worksheet contain the data validation you want to remove.

2. Then, click "Kutools" > "Prevent Typing" > "Clear Data Validation Restrictions", see screenshot:

3. In the prompt box that appears, click "OK", and the data validation rule will be cleared as you need.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


 Remove data validation from all worksheets with VBA code

To remove the data validation rules from the whole workbook, above methods will be time-consuming if there are lots of worksheets, here, the below code can help you to deal with this task quickly.

1. Hold down the "ALT + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Then, Click "Insert" > "Module", and paste the following macro in the "Module" window.

VBA code: Remove data validation rules in all worksheets:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Then, press "F5" key to run this code, and all the data validation rules have been deleted from the whole workbook immediately.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!