Excel Drop-Down Lists: Quick Tip & Tutorial

There are plenty of apps and services designed to help you stay organized and productive, but when it comes to raw data processing power, nothing beats Microsoft Excel. And for repetitive tasks, its drop-down lists are a real asset.

They reduce typos, speed up data entry and ensure a clear and consistent presentation.

Here’s how to quickly add drop-down lists in Excel, plus some expert tips for customizing and formatting them the way you want.

How to add drop-down lists in Excel

What you’ll need: A version of Excel (desktop or online) that supports data validation. This includes Microsoft 365 and recent standalone versions, such as Excel 2021 and later. I’m working here with the web version. If your menus are slightly different, the Microsoft Support Guide may be able to help you.

1. Select the cells where you want to insert a drop-down list

I created a sample spreadsheet to demonstrate how drop-down lists work. In this example, column A contains the customers’ names and column B their communication preferences. Select the cells in column B, as this is where the drop-down list will be inserted.

© Elyse Betters Picaro / ZDNET

2. Select Data Validation from the menu

Open the tab Data in the Excel toolbar, and then click Data Validation. The Data Validation panel then opens; the tab Settings is selected by default.

© Elyse Betters Picaro / ZDNET

3. Select List as validation criterion

In the Settings tab of the Data Validation window, open the menu Authorize and select List.

© Elyse Betters Picaro / ZDNET

4. Enter the values for your drop-down list

After selecting Lista field Source appears. Enter your options separated by commas, without spaces. For example: Call, Email, SMS, N/A

© Elyse Betters Picaro / ZDNET

This creates four choices in each drop-down list: Call, Email, SMS, or N/A (for “Not Available”).

5. Click on Apply to create your drop-down list

Click again Apply to save your settings. Excel adds a small arrow to each selected cell. Click on it to open the drop-down list and choose a value; the cell is then displayed instantly.

© Elyse Betters Picaro / ZDNET

6. Bonus: Customize your drop-down lists

To give a particular style to your drop-down list, use conditional formatting. It allows you to automatically apply colors and formatting based on the selected value.

• Create your drop-down list and select the cells that contain it.
• Click on Welcomethen on Conditional formattingand finally on New rule.
• In the section Highlight cells with, choose Specific textthen set the condition (for example, Contains) and enter the value.
• Choose a formatting style or click the icon Plus (+) to create a custom color.
• Repeat these steps for each drop-down option you want to format.
• Click on Finished to enforce the rules.

© Elyse Betters Picaro / ZDNET

Questions/Answers

Can I follow these steps for Google Sheets?

Yes, for the most part. Google Sheets supports drop-down lists, but the process is slightly different.

Instead of Data > Data Validation, use Data > Data Validation > Add rulesthen choose Drop-down list or Dropdown list from a range . The principle is the same, but the menu titles and options are slightly different. See Google’s support guide for more information.

Can we modify a drop-down list after creating it?

Yes. Select the cells, return to DataThen Data Validation and change the values in the field Source.

Tip: For long or frequently updated lists, place your options in a column on another sheet and reference that range in the Source field, for example: =Sheet1!D1:D10.

Is there a limit to the number of items?

Excel does not set a practical limit on the number of items, but the total length of an entered list is capped at 32,767 characters.

Do you want to create a Yes/No drop-down list?

• Follow the steps above and enter Oui, Non in the Source field.
• Select the cells from the drop-down list.
• Go to Datathen to Data Validation or Validation.
• Choose List sous Authorize.
• Enter Oui, Non in the Source field.
• Click on OK to save.

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

No. Dropdowns use data validation, not formulas.

Are dropdowns the same as filters?

No. Filters are located in column headers and determine which rows are visible. Drop-down lists are located in individual cells and determine which values can be entered.

Can we use Copilot to create drop-down lists?

As of 2026, Copilot in Excel can explain how to create drop-down lists and facilitate the generation of source data, but it does not provide the ability to directly add data validation drop-down lists via a query. You should always use the Data Validation menu. Copilot in Excel is a paid feature and requires a Microsoft 365 subscription.