Excel data validation: add, use, copy and remove data validation in Excel
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
- 3.1 Data validation for whole numbers and decimals
- 3.2 Data validation for date and time
- 3.3 Data validation for text length
- 3.4 Data validation list (drop-down list)
4. Advanced custom rules for data validation
- 4.1 Data validation allow numbers or texts only
- Allow only numbers to be entered with Data Validation function
- Allow only text strings to be entered with Data Validation function
- 4.2 Data validation allow alphanumeric values only
- 4.3 Data validation allow text begins or ends with specific characters
- 4.4 Data validation allow entries must contain / must not contain specific text
- 4.5 Data validation allow only unique values
- 4.6 Data validation allow uppercase / lowercase / proper case only
- 4.7 Data validation allow values that exist / not exist in another lis
- 4.8 Data validation force only phone number format to be entered
- 4.9 Data validation force only Email addresses to be entered
- 4.10 Data validation force only IP addresses to be entered
- 4.11 Data validation restrict values that exceed the total value
- 4.12 Data validation restrict cell entry based on another cell "
- 4.13 Data validation allow only weekdays or weekends to be entered
- 4.14 Data validation allow entered date based on today’s date
- 4.15 Data validation allow entered time based on current time
- 4.16 Data validation the date of specific or current year
- 4.17 Data validation the date in current week or month
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?
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:
![]() |
|
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:
![]() |
|
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:
![]() |
|
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:
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)
- 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)
- 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.
- 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.
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:Only allow entering text that ends with 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:
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:Only allow entering text that ends with CN or 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:
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: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 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: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. Case sensitive: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, 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: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, 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.
- 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:
![]() | ![]() | ![]() |
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:
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.
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:Only allow lowercase textOnly 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 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 columnPrevent 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, 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.
- 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.
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:
- 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.
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.
- 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
![]() | ![]() | ![]() |
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.
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.
- 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.
- 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 weekdaysAllow 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 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.
- 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:
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:
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:
- 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.
- 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:
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:
- In the "End date" text box, enter this formula:
- 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:
- In the "End date" text box, enter this formula:
- 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.
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:
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
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!