Create Dropdown List in Excel

Cover Image - Create Dropdown Lists in Excel

Dropdown lists in Excel can make entering and managing data in Excel a lot easier! This post will explore how to create dropdown list in Excel!

Dropdown lists are common in web-forms and databases – they’re possible in Excel but they’re a little tucked away. Let’s explore how to get them added to your workbook in no time!

What do Dropdown Lists in Excel Look Like?

Let’s first take a look at what dropdown lists in Excel look like:

An example of what dropdown lists in Excel look like

How to Create Dropdown List in Excel

To create dropdown lists in Excel, select the Data Validation tool in the Data Tab. In the image below, we click first on the Data Tab, then select the Data Validation tool:

Selecting Data Validation in the Data Tab of Excel

Adding Items Directly to the Data Validation Tab

If you know the items you want to add, you can type them directly into a list within Excel. First, select List under the Allow menu, and then enter your items directly into the Source field, seperated by commas. Check out the image below to see how to do this:

Example showing how to type items directly into data validation tab

Preferred Method: Linked Lists to Create Dropdown Lists in Excel

Entering your data directly is ok, but the better way to do this for multiple items is to use a linked list. This allows us to easily update values, add new values, and remove values. To get started with our preferred method, let’s create a field where we want someone to enter their favorite color from a predefined list:

Creating a field to describe the data we're capturing

On another sheet, which in our example is called sheet2, we’ll enter a list of the data that we want to be able to have in our dropdown list in excel.

Showing a list of colors on sheet2

Check out our other Excel tutorials on datagy!

Now, we’ll switch back to our first sheet, and click on Data Validation again. Then, select List under the Allow menu, and enter a reference to our list of items under the Source item:

How to enter a linked list for dropdown lists

When we enter the fields as shown and click OK, we get the following result:

End result of our dropdown list in Excel

Are you enjoying our content? Consider following us on social media! Follow us on LinkedIn, Twitter, or Instagram!

How To Add More Items to Dropdown List in Excel

If you wanted to add more items to the list, you could simply append to the list. However, you would then need to update the reference in the data validation tool. If you want to skip this step, go to your list and click Insert Row and add an item anywhere in the middle:

Image shows how to add a new row in Excel

Once you add in your new item, Excel will update the reference automatically and your new item will be included in the list.

Add a Dropdown List in Excel to Multiple Cells

If you wanted to add the same list to multiple items, simply select all the cells and click data validation and follow the steps outlined above:

Image showing how to add dropdown lists to multiple cells

Conclusion: Create Dropdown List in Excel

In this post, we explored how to create dropdown list in Excel using two different methods. We also looked at how to update our lists once we create them.

Feel free to check out some of our other tutorials for Excel, such as how to use VLOOKUP in Excel or how to set up a personal macro workbook.

Check out our eBook on Python