How to Make a Dropdown List in Excel

Dropdown lists can really improve the efficiency and effectiveness of your spreadsheet. The more complex your spreadsheet is, the more helpful dropdown boxes can be. If you’re having trouble creating one in your spreadsheet, don’t worry. I’m here to help. Here’s how you can make a dropdown list in Excel.

There are actually a couple of ways to create a dropdown list in Excel. They both involve the same basic steps but offer a bit of flexibility in how you create your list.

Creating dropdown lists in Excel

Here’s the main way you can create a dropdown list in Excel 2013 and onwards. You’ll need to create one sheet to store the data and another sheet to contain the actual spreadsheet. For example, let’s say you want the dropdown list to appear on Sheet 1. You’ll add the data for that list in Sheet 2. This way, everything is kept organized and separate.

First, type the entries you want to include in your dropdown list in Sheet 2 in Excel.
Select all the entries, right click, and choose ‘Define name’ from the options.
Give the cell range a name and click OK.
Next, click on the cell in Sheet 1 where you want your dropdown box to appear.
Go to the Data tab and click on Data Validation.
In the Allow box, select List and type ‘=NAME’ in the Source box. Replace NAME with the name you gave to the cell range in step 3.
Choose whether you want to ‘Ignore blank’ and have an ‘In-cell dropdown’ based on your preference.
If you want, you can go to the Input Message tab and either uncheck the box or add a message to be displayed when a selection is made in the dropdown box.
If you need to make any changes, click on the Error Alert tab.
Otherwise, click OK.

Your dropdown list should now appear in the cell you specified. Test it out quickly to make sure it’s working.

Create a Dropdown List Using a Table

Would you like to learn how to create a dropdown list in Excel using a table? It’s a useful feature that allows you to make changes on the go without the need to edit named ranges. This can save you a lot of time, especially if your spreadsheet is constantly evolving.

Here’s how you can do it:

Begin by typing the entries you want to include in your dropdown list on Sheet 2 in Excel. You can see an example in the image below.
Select the entries and go to the Insert tab. Click on Table and define the table by giving it a name. Check out the image below for reference.
Go to Sheet 1 and click on the cell where you want the dropdown box to appear. This is where you will see the options from your table.
Head over to the Data tab and click on Data Validation.
In the Allow box, select List and click on the small cell icon next to the Source box. This is how you will choose the cells that will be included in the dropdown box. Take a look at the image below for guidance.
Highlight the cells in the table that you want to feature in the dropdown box. The Source box should then show something like ‘=Sheet2!$B$2:$B$4’ as seen in the image below.
If you prefer, you can select ‘Ignore blank’ and ‘In-cell dropdown’ options.
Move on to the Input Message tab and add a message to be displayed when a selection is made in the dropdown box. If you don’t want a message to appear, simply uncheck the box.
If you wish to make changes to the Error Alert box, go ahead and click on it. Otherwise, you can click OK.