How to Allow Only Certain Values in Excel Using Data Validation

Sometimes we need to restrict data range for specific values. It could be yes/no answers or even certain text/numbers or even characters. This is why in this advanced excel tutorial i'm about to show you how you can allow only yes or no entry in excel. You can also say we are restricting data range or rows or columns to enter only specific values. Hope you can use your imagination and solve versatile problems using this excel tutorial. Lets get started.
Step 1: First select the data range where you want to use data validation.
Step 2: Now click on "Data" tab and click on "Data Validation"
Step 3: Now under the Validation criteria click on the drop-down list where you have predefined "Any Value".
Step 4: Select "Custom" and you'll find a formula bar.
Step 5: Inside the formula bar write this formula =(OR(A1="Yes",A1="No"))
Step 6: Click Ok and Done. Now you can only enter yes or no data in column A

Special Note: 1. In the formula A1 indicates the first cell of selected column
2. Yes/No is the text i want to allow on the selected data range.
Change 1 & 2 according to your need.

Now you know how to allow only certain values in Excel using data validation.

Thanks for watching.
#DataValidation #Yes/NoAnswer
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Playlists:

Social media:

[sarahahmed]

Exactly what I was looking for. Thanks a lot!

[mariafurci]

Thanks for the video, what do I need to add to the formula to allow only UPPERCASE characters. For e.g. in your video, when you typed "yes", how do you make it populate as "YES"

[samanthaleong]

hi, above is helpful but any solution if i would like to district certain words.
like user can insert any wording but not listed in table 1
appreciate if you can help.

[trilochanjena]

can the same done in textbox of userform in excel? plz guide with codes if so

[anujajoshi]

What do we need to be if we want to provide more than 3 options as for example EP 07, EP12, EP 21

[Amazzinguy]

Hi i need to restrict data shown in an Excel List based on a Validated list -- do you have a video showing that

[zulpurwanto]

Halo, can you write the OR formula here.
I tried it in my excel, but I failed.

[lucekl]

Hello, why i can add zero as number? "0"