How to Create a Drop-Down List in Excel:
Introducing the 1 MinuteDropDown
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
The drop-down list is a great way to seem like a superuser and impress your co-workers and boss
At the same time, its a very user-friendly asset in almost all custom-made Excel sheets.
In this tutorial, Im going to show you:
The 5 steps to create a drop-down list in Excel in 1 minute or less.
I call it the 1 Minute Drop-Down.
In the animation right below you can see the drop-down menu in action. Behold its mighty glory!
A drop-down list is actually a tiny window from where you can peek into some other data you have constructed.
This also gives you the possibility of controlling exactly what can be entered into a cell. This makes the drop-down menu a very common and powerful data validation tool.
Luckily, creating a drop-down menu is supereasy if you know the exact steps.
In a few seconds, Im going to show you the exact steps to create your very own drop-down.
*This tutorial is for Excel 2019/Microsoft 365 [for Windows]. Got a different version? No problem, you can still follow the exact same steps.
Follow along by downloading our sample file right below!
BONUS:Download theDrop Down Excel Workbook Sample File [with 2 pre-loaded exercises] to go along with this post.
Before we start the clock, lets take a look at the data in the sample file.
First we have a sheet with all the names of our employees.
This is the data we would like to see when opening our drop-down menu.
Then we have the gateway where we want to be able to scroll through all the names on the other sheet.
This gateway is placed in the Dropdown sheet of the Excel sample file.
Well put our drop-down menu in cell B1.
Step 1: Go to the Data tab
This step is easy!
Simply move your cursor to the Data tab and click it.
Step 2: Click Data Validation
Now click the button Data validation in the middle of the ribbon [do not click the little arrow simply click the top part of the button].
Step 3: Choose the List
In the data validation dialog box, you need to click the drop-down menu to pick what kind of data validation method you want to use [how meta is that?!].
Click the arrow next to the field and click List from the well list!
Then click OK to move on to the next step.
Step 4: Select the source
Remember the data sheet?
Thats where Ive put all the employees for our drop-down list.
Now its time to tell Excel that our drop-down should be connected to that data.
Click once in the Source: field.
Then click the Data sheet and select all the employees.
When youre done click OK.
Protip:Want to speed up this process even more?
Theres a neat shortcut [more here] you can use to select all the employee names in [almost] an instant.
When selecting the cells for the source, select the first name you encounter [in cell A2] then hold down Ctrl + Shift and click the down arrow on your keyboard. Youve now selected all the names in the sheet in just a few seconds.
Step 5: Heres the end result!
And thats the 1 Minute Drop-Down! Beautiful isnt it?
You have now created a drop-down list in Excel in 60 seconds [or hopefully even less!].
With some practice, you can almost create these drop-downs instantly. Ive managed to create one in a mere 15 seconds.