How to Create a Drop-Down List in Excel (6 Easy Steps) (2024)

How to Create a Simple Drop-down List in Excel

  1. Create a list of items you want to include in your drop-down.
How to Create a Drop-Down List in Excel (6 Easy Steps) (1)
  1. Go to the location where you want the list to appear, select all the cells.
How to Create a Drop-Down List in Excel (6 Easy Steps) (2)
  1. Go to Data (tab) > Data Tools (group) > Data Validation.
How to Create a Drop-Down List in Excel (6 Easy Steps) (3)
  1. For Allow select List.
How to Create a Drop-Down List in Excel (6 Easy Steps) (4)
  1. For Source, click on the Source button, and select the range with the prepared list of items.
How to Create a Drop-Down List in Excel (6 Easy Steps) (5)
  1. Click OK.
How to Create a Drop-Down List in Excel (6 Easy Steps) (6)

You now have the ability to select an item from a drop-down.

How to Create a Drop-Down List in Excel (6 Easy Steps) (7)

For short lists, you can skip the preparation and input the drop-down options directly in the Source box, separated by a comma (or semicolon, depending on your regional settings), e.g. Yes, No.

How to Create a Drop-Down List in Excel (6 Easy Steps) (8)
How to Create a Drop-Down List in Excel (6 Easy Steps) (9)

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.

Learn More

How to Create a Drop-Down List in Excel (6 Easy Steps) (10)

Why Use Drop-down Lists

As you saw, it’s an easy process. We’ve created a simple data entry form to collect preferences.

Data entry is one of the most common applications of drop-down lists. Like the other data validation options, they allow you to control user inputs. This saves time and ensures accuracy and consistency of replies.

Some potential applications include:

  • Project Management: status updates, setting priority, etc.
  • Inventory Management: categorizing items.
  • Surveys and Forms: standardizing responses.

But you could also use drop-down menus to create dynamic and interactive reports that recalculate automatically based on user’s choice.

You can even combine them with charts to make them interactive.

Best Practices when Working with Drop-down Lists

Since the most common use of drop-downs involves collecting user inputs, it’s good practice to protect the list’s source from being tampered with.

You can do this by:

  • Keeping the source tables or ranges in a separate sheet.
  • Hiding the column that includes your drop-down list item – just right-mouse-click and select “Hide”.
  • Selecting the column, go to the Data tab / Group / Group. This way you can collapse the column.
  • protecting the cells that contain your source range.

Leave Instructions – Customizing Input Messages and Error Alerts

There are various additional settings in the Data Validation tool that help you to improve user experience.

While most users are probably well-versed in using drop-down menus, you may still want to include a message with some custom instructions. Select all the cells and reopen the Data Validation window. There, select the Input Message tab and populate the title and/or content of the message.

How to Create a Drop-Down List in Excel (6 Easy Steps) (11)

It will appear as a tooltip in the grid when you select a cell with the drop-down.

How to Create a Drop-Down List in Excel (6 Easy Steps) (12)

Understand Error Alert Types and Allow Other Entries

You can also customize the error message the users get when they try to override the data validation and type out something that’s not included in the list. More importantly, if you go to the Error Alert tab in the Data Validation dialog box, you get to change the “Style”.

How to Create a Drop-Down List in Excel (6 Easy Steps) (13)

It is a crucial setting, because it affects not only the style and icon of the error message, but also the behavior.

The default style – Stop – won’t let the users override the validation, no matter how many times they retry.

How to Create a Drop-Down List in Excel (6 Easy Steps) (14)

However, if you change it to either Warning or Information, the users only have to acknowledge the rule and can continue. Once they click “Yes” or “OK”, they can commit their manually entered value.

This can be useful when the drop-down list is intended to speed up data entry but shouldn’t restrict it. For example, you want to leave the option to populate a new address.

Note that even with the Stop Error enabled, data validation is not entirely foolproof. You can still find invalid data in your spreadsheet. Lists in Excel can be pasted over which overwrites the data validation in the cell.

Adding Items to the List

What if you want to expand the list of items in the drop-down? How can you do it dynamically to avoid adjusting the source range in the Data Validation window each time?

You can choose from a number of methods, depending on your needs and preference.

Add Items Before the Last Item

If you’re sticking to a range (i.e. not using an Excel table), you can insert a new row in the middle of the range. That way the reference in the Data Validation tool will shift automatically.

Using a (Named) Range

You can name the ranges using the Name Manager, which will make referencing them in the Data Validation tool more user-friendly.

Using an Excel Table

For a fully dynamic approach, using Table references as a source is the way to go. Convert your range to a table using the shortcut Ctrl + T. You can remove the table style (I always do 😉) but you retain its functionality, like automatic inclusion of new rows.

If you already have a table with unique values in your workbook, some sort of master data table, you can refer to the relevant column of this table. In the latest version of Office 365, it doesn’t even have to be unique. Excel will deduplicate it for you.

Using a Spilled Range (Dynamic Arrays)

If you need to prepare your data beforehand – create a unique and sorted list – you can rely on the newer functions like UNIQUE and SORT which return a spilled array. To refer to a spilled array, you use a # (hash, or pound) symbol. The reference is fully dynamic, updating automatically whenever the underlying source expands.

Managing a Drop-down List

Extending Data Validation to New Cells

If you add new rows to your input form and want to extend the drop-down to those new rows, all you have to do is copy a cell with data validation and paste it in the new row (or simply drag it down).

If your input template is formatted as an official Excel table, you don’t have to do anything. The drop-down will be automatically added to any new rows of a table.

Removing Data Validation

To remove the drop-down list from certain cells, select them and reopen the Data Validation window. There, select Clear All.

How to Create a Drop-Down List in Excel (6 Easy Steps) (15)

Searchable Drop-down List

The downside of drop-down lists in old Excel was the fact that they weren’t sorted and they weren’t searchable. You had to scroll down the list until you found the value you wanted. If it was a long list, it could be more bothersome than actually typing out the value.

If you have the latest version of Office 365, that problem belongs to the past, because the drop-down lists are now natively searchable.

This means that when you start typing the value you want to input, the drop-down menu automatically filters down to only show the entries that begins with the typed-out word or phrase.

How to Create a Drop-Down List in Excel (6 Easy Steps) (16)

It also automatically deduplicates your list, removing the need to prepare and clean the data beforehand (as long as you have a source for the list anywhere in your workbook).

Before we finally got this solution natively, we could use a dynamic array formula (available in Excel 2019 and later as well as Excel for Office 365) to achieve this effect. It is a bit more complex if you wanted searchable drop-downs for every row, but not impossible.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.

Learn More

How to Create a Drop-Down List in Excel (6 Easy Steps) (17)

Dependent Drop-down Lists

Imagine filtering a drop-down list by the selection in another drop-down to only show the related items. Dynamic Arrays also made creating such dependent drop-downs much easier.

It is possible without dynamic arrays as well; it just requires more complicated formulas. You will need either a combination of OFFSET, MATCH and COUNTIF or COUNTA functions, or the powerful combo of INDEX and MATCH.

Final Thoughts

Drop-down lists are a powerful feature in Excel that can enhance data integrity and user experience. Whether you’re handling a small project or dealing with large datasets, understanding how to create drop-down lists will significantly improve your productivity.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt?You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.

Learn More

How to Create a Drop-Down List in Excel (6 Easy Steps) (18)

Published on: December 20, 2023

Last modified: December 20, 2023

Category: ,Excel

Tagged as: Data Validation, Drop-down

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses

How to Create a Drop-Down List in Excel (6 Easy Steps) (2024)

FAQs

How to Create a Drop-Down List in Excel (6 Easy Steps)? ›

Keyboard shortcut – Alt + A + V + V.

What is the shortcut to create a drop-down list in Excel? ›

Keyboard shortcut – Alt + A + V + V.

How do I create a drop-down list in Excel for multiple sheets? ›

This can be done in the same worksheet as the dropdown list or in a different sheet. Select the cell(s) that are to contain the list. On the ribbon, click the Data tab > Data Validation. In the Data Validation dialog window, select List from the Allow drop-down menu.

How do I create a drop-down list in Excel with times? ›

Then select the cell(s) that you want the drop-down to appear in, and go to the Data tab on the Ribbon. Click on Data Validation, select Data Validation... and then pick "List" from the drop-down. In the Source box, enter "=MyTimeList" (without the quote marks). Now you should have drop-downs for selecting time!

How to create a drop-down sort list in Excel? ›

To do this, select the 'Data' tab in the ribbon and then select the 'Filter' option. This will add a drop-down arrow to the right of each column header. Click on the drop-down arrow of the column that you want to sort by and then select the 'Sort A to Z' or 'Sort Z to A' option.

How do I select multiple options in a dropdown? ›

Windows: We need to hold down the CTRL button to select multiple options. Mac: We need to hold down the command button to select multiple options.

How do I add more selections to a drop-down list in Excel? ›

If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you. To add an item, go to the end of the list and type the new item. To remove an item, press Delete.

How do I create a drop-down list in Excel with different values? ›

From the Excel ribbon, go to the Data tab, and under the Data Validation menu, click Data Validation. A dialog box opens. Select List in the Allow menu. Under Source, enter values manually (with separator) or select a range containing the values that you will allow in the drop-down menu.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Margart Wisoky

Last Updated:

Views: 5821

Rating: 4.8 / 5 (58 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Margart Wisoky

Birthday: 1993-05-13

Address: 2113 Abernathy Knoll, New Tamerafurt, CT 66893-2169

Phone: +25815234346805

Job: Central Developer

Hobby: Machining, Pottery, Rafting, Cosplaying, Jogging, Taekwondo, Scouting

Introduction: My name is Margart Wisoky, I am a gorgeous, shiny, successful, beautiful, adventurous, excited, pleasant person who loves writing and wants to share my knowledge and understanding with you.