Whether you're running a business or managing a project, data organization is a must. There are plenty of apps and websites available to help you stay organized, but nothing beats the tried-and-true Microsoft Excel.

It can be a useful tool in anyone's arsenal -- even someone looking to map out their household finances or family schedules. Whatever reason you have for using Excel, drop-down lists are one of the many useful features you can and should use if you're looking for ways to speed up data entry.

Also: How to use ChatGPT to write Excel formulas

This guide will show you how to quickly create drop-down lists in Excel. These lists let you choose from preset options instead of typing everything out in every cell. This not only simplifies data entry but also helps reduce mistakes and maintain consistency across your sheet or workbook.

How to easily add drop-down lists in Excel

What you'll need: A Microsoft Office subscription is required to access Excel, whether it's on the web or via the desktop suite.  I'm using Excel on a desktop for this guide. Go to Microsoft's support hub if you find any differences when using the web version. Alternative software like Google Sheets has similar drop-down list functions, but the processes for adding them will likely differ.

1. Select the cells where the drop-down lists are needed

This guide uses an example spreadsheet with a list of clients in column A on the left and their Communication Preferences in column B on the right. You'll need to select cells in column B. We're adding drop-down lists to the Communication Preferences column, so you can specify whether each client prefers to be contacted by email, phone call, or text message. 

Show more

Selected cells to add to drop down list. 

Maria Diaz

2. Select Data Validation in the menu

Go to Data in the menu bar and select Data Validation or Validation, depending on whether you're using Excel on Windows, Mac, or the web. After clicking on Data Validation, a Data Validation popup will appear.

Show more

Go to Data and select Data Validation.

Maria Diaz

3. Select List as your validation criteria

In the Settings tab of the Data Validation popup, locate the drop-down menu under Allow and select List from the validation criteria options.

Show more

Under Allow, select List.

Maria Diaz

4. Enter the values for your drop-down list

After selecting List from the options, you'll see a Source field appear. Enter the values or items you want for your drop-down list here -- each separated only by a comma with no spaces in between. 

For example, I used "Call,Email,Text,NS". This will add Call, Email, Text, or NS to the drop-down list options in Column B, to represent each client's communication preferences (with "NS" meaning "not specified").

Show more

Enter data for the drop-down list.

Maria Diaz

5. Click "OK" to create your drop-down list

When you click OK in the Data Validation pop-up to save your changes, your drop-down lists will be inserted into your Excel spreadsheet and ready to use immediately. Now, whenever you click on a cell, a drop-down arrow will appear next to it. Click on that arrow to see the drop-down list and all your options to choose from. When you pick a value or item from the list, it will automatically fill the cell.

Show more

A drop-down list

Maria Diaz

FAQs

Show more

Can you edit a drop-down list after creating it?

Yes, you can edit a drop-down list by selecting the cells with the list, going to Data > Data Validation, and updating the values in the Source field.

Is there a limit to the number of items in a drop-down list?

Excel does not have a strict limit for drop-down items (it's over 30,000), but large lists can make navigating and using your sheet difficult. Consider breaking up long lists into smaller, more categorized drop-downs for better usability.

How do you create a yes/no drop-down in Excel?

Creating a yes/no drop-down in Excel uses the process we followed for the example above. Just change what you put in the Source field to say "Yes,No". Here are the exact steps to follow:

1. Select the cells that you want to contain the drop-down lists.
2. Click on Data > Data Validation or Validation from the menu bar.
3. In the Settings tab of the Data Validation pop-up window, select List under Allow for your validation criteria.
4. Insert "Yes,No" in the Source field on the pop-up, separated by a comma only and no spaces.
5. Click OK in the Data Validation pop-up to save your yes/no drop-down list.

Can you add colors to drop-down options?

Yes. You can use Conditional Formatting to apply colors to cells. Here are the steps to follow:

1. Create your drop-down list using Data Validation.
2. Select the cells containing the drop-down list.
3. Open Conditional Formatting by going to Home, then Conditional Formatting, and selecting New Rule.
4. Set a rule for a value by choosing "Format only cells that contain," entering a value, and assigning a color through Format and Fill.
5. Repeat the process for other drop-down options.
6. Click OK to save and apply the color-coding.

Do you need a formula to create drop-down lists?

No. A formula is not needed to create drop-down lists in Excel. All you need to do is data validation.

Is a drop-down list the same as data filtering?

Although they are often mistaken, Data Filtering is different from drop-down lists. Data filtering is added to the headers of each column, so you can filter the visible data by category -- for example, filter the data in a column by month, name, place, and more. A drop-down list lets you select an item to fill the cell with.

Featured

The Microsoft 365 Copilot launch was a total disasterYour TV's USB port has superpowers: 4 useful benefits you're not taking advantage ofGalaxy S25 Ultra vs. iPhone 16 Pro Max: We tested both, and the winner is not so obviousIf you're ready to break up with Windows, this is the Linux distro I suggest for new users

• The Microsoft 365 Copilot launch was a total disaster
• Your TV's USB port has superpowers: 4 useful benefits you're not taking advantage of
• Galaxy S25 Ultra vs. iPhone 16 Pro Max: We tested both, and the winner is not so obvious
• If you're ready to break up with Windows, this is the Linux distro I suggest for new users