In a previous article we showed you how to create useful data sets with data validation, however in this walkthrough we are going to show you how to create two different types of drop down menu’s in Excel. One way through data validation (it would be useful if you also read the other article on this one) and the other way through form fields.

So first off we are going to use data validation to create your drop down menu. The benefits of this format is that it is quite quick and can be easily changed and added to in the future. This is personally my favourite way.

  1. To start, enter the headings to your table that you may want a drop down menu to appear in. Ours are below:1
  2. Now in the ‘Membership Type’ column we want to include a drop down list so we can easily select the type of membership each applicant has. In a second sheet, of the same document, write the ‘Membership Type’ as a title and add all the different options underneath.2
  3. Next highlight all the whole range of cells and go to Insert > Table.3
  4. On the box that comes up tick the ‘My table has headers’ option and click OK.4
  5. Your table should now look similar to the below. Next go to Formulas > Define Name.5
  6. The box below will pop up. Give your data range a memorable name, we will call ours Membership, and the click OK.6
  7. Now head back to the front page of your document, and highlight the cell or cells that you want to have the option of the list. Then go to Data > Data Validation.7
  8. On the box that pops us select Allow > List. Then select your named list from the options. You can see ours below being membership.8
  9. You have now completed the first way to create a drop down list in Excel through data validation. Well done. Like I have said, this is my personal favourite way as it is clean, fast and can be edited easily.9

Now onto the second method of creating a drop down list in Excel. This way is through form fields. This way can be useful if you are going to create a professional looking template that others can fill in. You would usually also lock parts of the document so that only certain parts (the parts you want edited) can be edited.

  1. To start you will need to enable the Developer Tab of Excel. Click on the top left hand button and select ‘Excel Options’.10
  2. In the popular tab click on the ‘Show Developer Tab in Ribbon box’.11
  3. Back in your Excel document, locate the Developer tab and click on Insert > List Box. For information on what some of the other form controls are you can look at a previous article that has been written.12
  4. On a separate sheet of the document, or a new document all together, write the list that you would like people to be able to choose from. In our case it is a list of names as we are going to be using it for our names column.13
  5. Now back in the front page of your sheet, right click on your list box and select ‘Format Control’.14
  6. Click in the input range box, and then locate and highlight your new list of names.15
  7. Your form will now look like the below:16
  8. You have now also created a drop down list using form controls. Like we have said this can be used to make a good looking editable and lockable form. It takes some time, but if it’s something that is going to be used for along period of time then it could be worthwhile.17

Well done, you have now learnt how to create a drop down menu in two different ways in Excel. If you have enjoyed this tutorial then please feel free to contact me or comment in the box below. Thank you.