Drop Down List and Data Validation
Drop down list in excel or drop down menu is referred to the list created with the help of data validation function. It presents the user with the option of choosing the names of products/customers/employees to pick from the created list. As the name of function suggested (Data Validation), names are compared/match with the initial list. In case the name is not found in the original list, the data is denied/rejected. Using Drop down list in excel give us a couple of advantages, first, it stops us from typing the same names and give us the option to choose from the list. It saves time. secondly, its reduces the chances of making a typing error.
how to create drop down list in excel
In our example, we will create a drop down menu for customers from the customer’s list to the Customer column in the Sales invoice.
Select the cells where you want to the drop down list to show. Go the Data tab and click on data validation. It opens a dialog. In the Allow box as can be noticed in the screenshot below, Select list. In the source box, click on the arrow sign and go to the sheet from where you are planning to pull the list. Select the list and click OK.
Selecting the list
Go to the invoice sheet, you will see a drop down list is ready in the customer column. Click on the drop down list, there we go, you have your list ready.
Testing of the list for data validation
Now let’s test the list for data validation by writing something that was not on the original list. Try to write Facebook in the column and immediately it produces an error. It simply means our list is working fine.
How to create dynamic drop down list
Let’s take drop down list to another level, Dynamic drop list is the list that keeps updating at every addition or deletion to/from the original list. Follow the procedure below to grasp the idea:
Select the whole list including the header, Hit CTRL+T to use the Format as Table function. Click OK.
Now select all the cells except the header and enter the name as Furn_list in the box as highlighted in red, Hit enter. A name has been defined for your list.
Choose cells where you want the drop down list, go the data validation. In the Allow box, select list and in the source box, enter =Furn_list and click OK. Your dynamic drop down list ready.
In the drop down list, an item from 1 to 20 is showing means there is no error in our list. Go to the initial list and add item # 21. Come back to the drop down list and check if the Item # 21 is added to the drop down list or not. If yes, which is the case, your list is dynamic.
Item 21 is appearing in the list. This the procedure, how you add a drop down list and a dynamic drop down list.
Dependent Drop Down List
A drop down list that depends on the input in other cells and changes with the particular input is known as Dependent drop down list.
We have two main product types, Office and Sports equipment. Each of the main categories is having sub-categories. The main list is in column C and Sub Categories in Column D. Column D data is dependent on Column C.
Defined Names for the Lists
Let’s define names for our list both for main and sub categories. Select the Primary list excluding the header. Go to the box on the left corner and enter a name “Products” and hit enter. In the same way, select the Products list in Column I and go to the box and enter a name “Office_equipments” and hit enter. Lastly, do the same for Sports_equipments.
This is the vital step, so keep a close eye, Select the cells where you need the list of the main category to be shown. Go to the Data validation, choose “list” in the Allow box and enter =Products in the source box. Click the Ok button and your first list is finalized. Any idea why we wrote Products in the source box. It is because we have defined a name for the main category products.
Select the first cell in the sub-category column (Cell D2). Go to the data validation, Select list from the allow box and enter the “=INDIRECT(C2) and click OK. Copy the Cell D2 to the range from D3:D6.
Click in Cell C2 and choose Office_equipments and then go to the cell D2 and check if the office_equipments sub category products are displaying. Please see the image below.
Go the cell C2 again and adjust the Office_equipments to sports_equipment. Return to the cell D2 and sub-category will now exhibit sports items. Your dependent list is up and running.
In the same way, you can create drop down list for the product as well.
If you want to download the excel file please click here.