Creating Expanding Dropdown Lists | Excel | Tutorial

A drop-down list also referred to as a pull-down menu, is a user-friendly method of displaying a large list of choices. It ensures consistency when entering large quantities of data.

This tutorial will show you how to create an expanding drop-down list.

Video Guide

Instructions

• Create a table which you would like to use a dropdown list for, this can be a simple table with just employee name and departments.
• We then need to create a table with the list of departments, this can be done on the existing sheet or it can be done on a new sheet. For this example, we are going to do it on a new sheet. To do this click the ‘Add’ button next to the sheet 1 tab.
• Once you are on the new sheet create the table of departments (Accounts, Admin, IT, Marketing, Research, Sales)
• Now we want to make this table into one that Excel will recognise. To do this you need to click anywhere within the table and then click ‘Insert’ and click ‘Table’.
• This should automatically highlight the whole table, we can keep the option ‘My table has headers’ checked if you have a title in your table, if not you can uncheck this box. Once this is done we now need to highlight all of the departments excluding the heading and then create a table name.
• To create the table name you need to type your table name into the box above column A. We are going to call this table ‘departments’ once you have typed the table name you can click enter.
• We then need to go back to sheet 1 using the tab at the bottom of the page. Then highlight cells C2 to C15 as this is where we want the drop down to appear. If you want it to appear further down you can continue selecting as necessary.
• Once you have selected the cells you need to click ‘Data’ and then ‘Data Validation’. Then from the validation criteria select ‘list’. For the source, we are going to type ‘=departments’ so that it selects the table that we had created in sheet 2 and then click ok.
• You should now be able to select your desired department from the drop-down menu.
• If you want to add more departments to that list all you have to do is go back to sheet 2 and type in another department below the previous entry and this should now appear in your drop-down box.