Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 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.


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.