Data Validation in Excel : - How to create a drop down in Microsoft Excel

In todays world excel became a very good source of information and almost used extensively by managers,IT Professionals etc.

All  this data store in excel can be wrong or this information can lead to wrong decision too ?

Surprised check the below Excel

Yes even though you have maintained the above data where person is interested to know how many Customers are satisfied with only knowing Yes or No answer but still there is a difference wrong data is filled by certain customer as shown above.

This lead to Question

How you can be sure that everything is correct.To optimize the data storage in excel we can create Drop Down list.

With the help of this list the chances are NILL for entering wrong data in it.

For e.g

For our above scenario in same excel in the Column B; I just want Yes or No answer;

Without drop down a user can enter whatever he want,so we create a drop down as shown below

Step 1:

Click on column B ,

Step 2

Then goto Data tab now,click on Data Validation

Step 3:

menu choose the “List” option in the Allow as shown,Source box enter values “Yes” and “No” as shown below;

Thus now you have a drop down created in excel with values required for us and user’s can’t enter any other garbage or unwanted value in it as shown below,

Also the same values appear in first or Label column to remove the same proceed as below;

Step 4
Select the Label column

Step 5

Click on data validation again and following pop up will come;

Step 6
Now make the changes as below

Press ok button.

Step 7
Now you can see the LABEL doesn’t have a drop down as expected

Thus above steps will help to customize or guided you to have your own Drop Down list.

Now want to do more click here Create Messages in Excelto learn how to create messages as warning,information or error in Excel.