Note: The other languages of the website are Google-translated. Back to English

 How to apply multiple data validation in one cell in Excel worksheet?

In Excel worksheet, applying one data validation to a cell may be common for us, but, have you ever tried to use multiple data validation into one cell? This article, I will talk about some different examples for solving this task in Excel.

Apply multiple data validation in one cell (Example 1)

Apply multiple data validation in one cell (Example 2)

Apply multiple data validation in one cell (Example 3)


Apply multiple data validation in one cell (Example 1)

For example, I want to setup data validation to cells with these criteria: if a number entered, it must be less than 100, if text is entered, it must be in the list from D2 to D7 as following screenshot shown:

1. Select the cells that you want to apply multiple data validation, then click Data > Data Validation > Data Validation, see screenshot:

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

(1.) Choose Custom from the Allow drop down list;

(2.) Enter this formula: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) into the Formula text box.

Note: In the above formula, A2 is the cell which you want to apply the data validation, C2 and D2:D7 are the criteria you need to apply the data validation based on.

3. Then click OK, from now on, only the values which matching the criteria can be entered into the cells, if not, a warning prompt box will pop out to remind you, see screenshot:


Apply multiple data validation in one cell (Example 2)

In this example, I will only allow the text “Kutools for Excel” or the date between 12/1/2017 and 12/31/2017 can be entered into the specific cells as below screenshot shown. To solve this job, please do as this:

1. Go to the Data Validation dialog box, and in the dialog box, please do the following operations:

(1.) Click Settings tab;

(2.) Select Custom option from the Allow drop down list;

(3.) Type this formula: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31))) into the Formula text box.

Note: In the above formula, A2 is the cell which you want to apply the data validation, C2, 2017,12,1 and 2017,12,31 are the criteria you need to apply the data validation based on.

2. Then click OK button, now, only the values matching the criteria can be allowed to type in, others will be restricted as following screenshot shown:


Apply multiple data validation in one cell (Example 3)

The third example, I want the text string must be started with “KTE” or “www”, and if it starts with “KTE”, only 6 characters can be allowed; if it starts with “www”, only 10 characters can be allowed, see screenshot:

The following formula in the Data Validation can help you to deal with it.

1. Go to the Data Validation dialog box, in the dialog, please do the following operations:

(1.) Click Settings tab;

(2.) Select Custom option from the Allow drop down list;

(3.) Type this formula: =OR(AND(LEFT(A2,3)="KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10)) into the Formula text box.

Note: In the above formula, A2 is the cell which you want to apply the data validation, and you can change the criteria in the formula to your own.

2. Then click OK button, and now, only the text values which matching the criteria you specified are allowed to be entered, see screenshot:

Note: May be there are other different data validation criteria needed to be applied, you can create your own formulas for different criteria as you want.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • 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 without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... 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...
  • 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...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
i'm looking for the ability to have a data validation which allows the user to select multiple choices. I.e. the list pops up and user can select Apple, grape, and/or banana. If they select all three, it shows in cell as: "apple, grade, banana". if only 1: "grape" etc... any ideas?
This comment was minimized by the moderator on the site
Hello, jeffe,
Maybe the below article can help you to deal with your problem, please check it:
https://www.extendoffice.com/documents/excel/2448-excel-drop-down-list-multiple-selection.html
This comment was minimized by the moderator on the site
Any idea how to have multiple data validation and a drop down. Drop down uses "List" and this example uses "Custom". Basically if the correct data is present in another column, I want the drop down to be able to be selected to input data. Otherwise if the dropdown is selected the error message appears.
This comment was minimized by the moderator on the site
Is it possible to apply data validation on the calcuated column attribute?
This comment was minimized by the moderator on the site
how can I add another Data validation function with this list function. for example I added a list of "yes" and "no". but I also want to add an OR function to this so that if "yes" is selected in one column then others only have to be "no".
This comment was minimized by the moderator on the site
Hi,
I need to validate multiple columns before entering a value into cell. please find my requirement below,
I have 3 columns for example,

A1-Total Credit limit (Mandatory field)
Should have a minimum amount of 100 and multiples of 100:
=AND(ISNUMBER(A1),A1>=100, MOD(A1,100)=0) - Solved!
B1- Weekly Credit limit (Optional filed)
User should be able to input a value only when A1 is filled, Should not be greater than A1, Minimum amount 100 and multiples of 100
=AND(A1<>"",ISNUMBER(B1),B1<=A1, MOD(B1,100=0) - Solved!
C1-Daily Credit limit (Optional field)
User should be able to input a value only when A1 is filled, Should not be greater than A1&B1, Minimum amount 100 and multiples of 100

I need to add following validation, Please Help
I have to check if A1 is filled or not, then C1 shouldn't be greater than A1 and C1 min value should be 100 and multiples of 100 - I have answer to this
How can add a validation on B1 because B1 is optional it can have value and cannot. so if there's a value I have to make sure C1 is not greater than B1 else ignore B1?

How can i have this in data validation?

Thanks once again for the help
This comment was minimized by the moderator on the site
i am confuse
This comment was minimized by the moderator on the site
How can I apply a data validation to multiple columns?
This comment was minimized by the moderator on the site
Hello Fabian,To apple a data validation to multiple columns, the trick is almost identical to the ways we mentioned above. The most important thing is that you select the columns first, then you can set up the rules in the Data Validation dialog box. Plesase have a try. Any question, please feel free to contact us. Have a nice day.Sincerely,Mandy
This comment was minimized by the moderator on the site
Hi Team, I failed to build example no.1. I copied and pasted the formula:  =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) and failed. Where is the bug ? Can you please assist ? THX a lot.
This comment was minimized by the moderator on the site
Hello Frank,
Glad to help. The reason why the data validation failed is that you probably didn't choose the data validation cell range. Please select the cell range first (say A2:A10), then click Data > Data Validation > Data Validation to input the formula =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1). Please see the screenshots I uploaded here. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations