Skip to main content
 

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

In excel, the Data Validation is a useful feature that you can use to limit what a user can enter into a cell. For example, the data validation feature can help you to limit the length of text strings, or text beginning / ending with specific characters, or unique values to be entered and so on.

This tutorial, we will talk about how to add, use and remove the data validation in Excel, some basic and advanced operations of this feature will be demonstrated in details as well.

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.

And now, when entering an invalid value, the message alert box will be popped out as below screenshot shown:

Stop option: You can click Retry to type another value or Cancel to remove the entry.

Warning option: Click Yes to enter the invalid entry, No to modify it, or Cancel to remove the entry.

Information option: Click OK to enter the invalid entry or Cancel to remove the entry.

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

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 you need in the Data box (In this example, I choose 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 (I want the numbers between 0 and 1 00).
  • At last, click OK button.

3. Now, only the whole numbers from 0 to 100 are allowed to be entered in your 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 you need in the Data box (Here I choose 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 the dates greater than 8/20/2021 are allowed to be entered in your 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 you need in the Data box (In this example, I choose 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 to type the text string less than 10 characters.

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 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 this formula: =ISNUMBER(A2) into the Formula text box. (A2 is the first cell of the selected range you want to limit)
  • 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 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 this formula: =ISTEXT(A2) into the Formula text box. (A2 is the first cell of the selected range you want to limit)
  • 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 just want to allow alphabets and numeric values to be entered, but restrict the special characters like ~,%,$, space, etc., this section will introduce some tricks for you.

 Allow only alphanumeric values with Data Validation function

To prevent the special characters but only allow alphanumeric values, you should create a custom formula into the Data Validation function, please do as follows:

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 with an amazing feature

May be the above formula is complicated for us to understand and remember, here, I will introduce a handy feature – Prevent Typing of Kutools for Excel, with this feature, you can quickly solve this job with ease.

After installing Kutools for Excel, please do as this:

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:

4.3 Data validation allow texts begin or end with specific characters

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

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

For example, I want the texts must begin or end with “CN” when entering the text strings in specific cells, please do as this:

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.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =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 be popped out to remind you as below screenshot shown:

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

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (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 the texts must 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.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =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:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (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.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • 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 designed text, a warning prompt box will pop out.

Allow entries must contain one of many specific texts

The above formula only works for one text string, if you need any one of many text strings to be allowed in the cells as following screenshot shown, 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.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • 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.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • 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, the entries that contain the specific text will be prevented to be entered.

Allow entries must not contain one of many specific texts

To prevent one of many text strings in a list to be entered as below screenshot shown, you should do as this:

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.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • 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, the entries that contain any one of the specific texts will be prevented to be entered.

4.5 Data validation allow only unique values

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

 Allow only unique values with 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 allow to be entered, and a warning message will pop out when duplicate data is input, see screenshot:

 Allow only unique values with 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 View Code from the context menu, in the popped out 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

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 duplicate value in cell A1:A100, a warning prompt box is popped out as below screenshot shown:

 Allow only unique values with a handy feature

If you have Kutools for Excel, with its Prevent Duplicate feature, you can set data validation to prevent duplicates for a range of cells with only several clicks.

After installing Kutools for Excel, please do as this:

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. And a warning message will pop out to remind you the Data Validation will be removed if applying this feature, click Yes and in the following prompt box, click OK, see screenshots:

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:

4.6 Data validation allow uppercase / lowercase / proper case only

This Data Validation is a powerful feature, it also can help to allow a user to enter only uppercase, lowercase or proper case entries in a range of cell. 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.
  • =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
  • 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 conform the rule you created will be accepted.

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

To permit the values exist or not exist in another list to be entered in a range of cells may be a painful problem for most of us. 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.
  • =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
    =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
  • 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, the entries only conform 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 only allow specific phone number format to be entered, please do as this:

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, go on selecting the cells, and then open the Data Validation dialog box by click Data > Data Validation > Data Validation, in the popped out dialog, under the Settings tab, do the following operations:

  • Select Custom from the Allow drop-down list.
  • And then, enter this formula =AND(ISNUMBER(A2),LEN(A2)=10) into the Formula text box.
  • 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 with 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.

After installing Kutools for Excel, please do as this:

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:

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 with Data Validation function

By using the Data Validation feature with a custom formula, you can create a rule for preventing the invalid email addresses from being entered quickly, please do as this:

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 this formula =ISNUMBER(MATCH("*@*.?*",A2,0)) into the Formula text box.
  • 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 is not email address format, a warning message box will pop out to remind you, see screenshot:

 Force only Email addresses format with 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.

After installing Kutools for Excel, please do as follows:

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:

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

Allow only IP addresses to be typed into a specific range of cells, please do as this:

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 you enter an invalid IP address into the cell, a warning message box will pop out to remind you as below screenshot shown:

 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

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 the correct IP addresses allow to be entered into the specific cells.

 Force only IP addresses format with 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.

After installing Kutools for Excel, please do as this:

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:

4.11 Data validation restrict values that exceed the total value

Supposing, you have a monthly expense report, and the budget total is $18000, now, you need the total amount in the expense list does not exceed the preset total $18000 as below screenshot shown. 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 makes the total to exceed $18000, a warning message box will pop out to remind 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 is allowed to enter anything, but, if the cell C1 is another text, nothing allows to be entered in the range A2:A9 as below screenshots shown:

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.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • 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 the date of weekday or weekend in the specific cells based on your need.

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 the dates greater than today’s date can be entered into the cells, otherwise, a warning message box will pop out to remind 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 user to be able to enter the dates of current week or month in specific cells, this section will introduce some formulas for dealing with 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 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 the dates of current month allow to be entered into the selected cells.

5. How to edit the data validation in Excel?

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

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. And all cells with data validation have been selected at once in current worksheet.

Tips: If you just want to select one specific type of data validation, first, please select one cell contains the certain data validation you want to find out, then go to the Go To Special dialog box, and choose 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 the data validation rules from a range of cells, current worksheet or the whole workbook, the following methods can do you a favor.

 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.

After installing Kutools for Excel, please do as this:

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 popped out prompt box, click OK, and the data validation rule have been cleared as you need.

 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.

 

  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom

 

Comments (1)
No ratings yet. Be the first to rate!
Ghomergher
Ghomergher
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations