How to create a drop down list in Excel

Want to know how to create a drop down list in Excel? Well, we’ve got you covered. Creating a simple drop down list in Excel might sound a bit intimidating at first, but it’s actually very simple. It’s so simple, in fact, that even an elementary school kid can do it – all it takes is a few quick clicks of the mouse.

That’s the beauty of Excel and spreadsheet applications like it. Their existence is to make what would be otherwise complicated tasks so much easier to do and much more accessible to even those who are not very computer savvy.

Creating a drop down list is as easy as pie, regardless of your Excel skill level. There’s a more technical process, but we’re only covering the easy methods. Whether it’s for your small business or for work, you’ll master soon enough how to create a drop down list in Excel.

1. How to create a drop down list in Excel using existing data

In this first method of creating a drop down list in Excel, we’re working under the presumption that you already have a working list of items you want to use in your drop down list, already entered in your spreadsheet.

Step 1. Select the cell for your drop down list.

Make sure that this is the cell you want to use for the drop down list, so you won’t have to adjust later.

Step 2. Go to the Data tab then click on Data Validation.

This opens the Data Validation dialogue box.

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list. 

This selects those cells all at once and automatically fills in the Source field with the data you need for your drop down list. 

Step 5. Click OK. 

2. Creating a drop down list in Excel by manually entering items

Using the second method, you don’t have an existing list in your spreadsheet to work with so you will be entering the items manually. To do that, you just need to follow the steps above until Step 4.

Step 1. Select the cell for your drop down list.

Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

Step 4. Click on the Source field. Then enter every single item for your drop 

down list, separating each item by a comma.

Step 5. Click OK.

3. How to create several drop down lists at once

If you want to create several of the same drop down list all at once, you’ll be happy to know that the process is just as easy as creating a single drop down list. The only difference is that you’re selecting several cells – the cells you want your drop down lists in.

Step 1. Select the exact cells you want to use for the drop down lists.

Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list. If you’re entering the items manually, simply enter every single item, separating each item by a comma.

Step 5. Click OK. 

It’s worth noting that there are other, perhaps trickier ways, to do this. You can use Excel formulas, for example, to create a simple drop down list. You can even create a dynamic drop down list that can easily be extended when necessary. But, that’s a tutorial for another day.