How to insert drop down list in excel

How to insert drop down list in excel

Background:

You may have seen drop down in various excel file . You may want to have same drop down list in your excel template. Let me tell you this is a fantastic idea as you can control what user should fill . If you provide free field to enter data , it can be of no use for the purpose of analysis and Management Information System(MIS).  You can use same field for using vlookup function to get corresponding field hence it is important to know, How to insert drop down list in excel .

Objective:

This post will provide step by step guide to insert drop down list in excel . At the end of post you can get to know how to insert drop down list in excel .

Excel Function Name :

Data Validation>List

Steps How to insert drop down list in excel using Data Validation function of Excel:

1. Look at below excel Data . Kindly paste data mention in A1:B6  to your new excel sheet at same location (cell). Let us plan to have drop down in cell number “C1” as highlighted in grey . Click here to download excel sheet with Drop down list.

2. Once  you copied data to your excel sheet from A1:B6,  move cursor to cell number “C1”

3. Our objective is to have all material name as mention in B2 to B6 should be available in drop down in cell number C1.

4. Keep cursor at “C1” and Go to Data Menu

5.Click on  Data Validation Function and again on Data Validation  as shown in image below:

6. Now click on List:

7.Click on below highlighted blue button, which will allow you to select range to include in drop down, kindly select range from B2:B6.

8.Again click on blue button, you will be back to above screen. Now click ok . (In case drop down items are less, you can directly mention the same in source field with “,” as separator. e.g. Table,Chair,Box,Sofa,Cupboard)

9. Yes you have done it ! You are ready with your first drop down as highlighted below:

10. You can provide custom message in Input Message tab e.g. “Enter Material Name” so that it will be displayed when cursor is placed in cell “C1”

11.In case user tries to enter data other than defined input(material name), system will provide Error in Alert box. You can customise error message e.g. “Kindly select  correct Material Name”

12.In case you want to copy same drow down to other cell, you have to just copy Cell number “C1” and paste to desire cell .

13. In case you want to remove this validation , Keep cursor on “C1” , you have to again go to Data Menu>Data Validation>Data Validation>List and click on clear all as highlighted in below screen. Drop down will be removed.

Use of Drop Down or list function:

Some time you want user to fill only restricted data , you can you this function and keep file protested .

In case you want to use same field for vlook up , you can use drop down . Further to clarify e.g. if you give free text field to user , he can write Table as “Table” or “table” and it may create problem.

Click here to down load excel sheet to check List function and whether drop down created by you is correct or not .

Hope you have liked this post , please give your valuable comment at the end of post.