Creating a drop down list is very easy. Its is very important function in excel if you want to select cell value from preconfigured items list and save your time in again and again typing it. For example , you are maintaining an inventory of cloths and there are 5 Size of a particular product then it will be really easy for you if you will create a drop down list and store all sizes values in another reference sheet , now you will just have to select the cell values from preconfigured list.
With the help of drop down lists you can avoid errors like typing mistakes and it will save a lot of time as you are just selecting the options from a preconfigured list. You can also add and edit items in your list any time.
Lets see and understand how to do it.
FIRST METHOD: DIRECTLY TYPING THE LIST
- Select cell where you want to create drop down list and go to data tab in upper menu. Now select data validation option.
- Data validation window will open , In ” settings ” tab move to ” validation criteria” and under “allow ” select “List”. Now go to sources and insert your list as shown in the image below.
- Now you will get a drop down list in your select cell with values you had inserted in sources box.
This method is very easy and straight forward but it is only when you have only few entries in your list , what if you have to select from a list of 200 entries then this method will become a big mess . Under such condition you will have to take help of second method.
SECOND METHOD : CREATING PRE CONFIGURED LIST
In this method we are going to create a pre configured list in different sheet and we we will used it in our desired cell. This is major method in creating drop down list and can be used when you have a lot of entries in your list or you have to use same list many times in a sheet.
- First create a list in ” sheet2 ” or any other sheet.
- Select the complete list and right click on it . Then select “Name Range” .
- A ” New Name ” window will open , provide a name to our list like size , shape , weight or whatever you want.
- Now move to sheet where you wan to insert drop down list .
- Go to data tab in top menu . Select “Data Validation” .
- Data validation window will open . Now select “List” in “Allow” tab under settings .
- It will display a “Source” option . Now insert your list name which you have entered in “Name Range ” option in previous step. If you have named it “Size” then enter “=size” in source option.
- Alternatively , insert your cells source for example my list is in “sheet2″ and from A1 to A6 so i will insert “=sheet2!$A$1:$A$6″ similarly you will have to insert your cell ranges.
- Press OK and you have created your drop down list in excel !
HOW TO INSERT INPUT MESSAGE AND ERROR MESSAGE IN DROP DOWN LIST
If you want to further increase appearance of your drop down list then you can display a input message and error message in drop down list.Lest see how to do it.
- Select the cell in which you have created drop down list.
- Go to “Data Validation” option and then move to input message and insert your message title and message description.
- Similarly you can also insert error alert using the same method as input message.
Well i hope now you can create drop down list in excel. if you have any further problems in creating list then please comment and i will solve your problems.