X
Tech
Why you can trust ZDNET : ZDNET independently tests and researches products to bring you our best recommendations and advice. When you buy through our links, we may earn a commission. Our process

'ZDNET Recommends': What exactly does it mean?

ZDNET's recommendations are based on many hours of testing, research, and comparison shopping. We gather data from the best available sources, including vendor and retailer listings as well as other relevant and independent reviews sites. And we pore over customer reviews to find out what matters to real people who already own and use the products and services we’re assessing.

When you click through from our site to a retailer and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay. Neither ZDNET nor the author are compensated for these independent reviews. Indeed, we follow strict guidelines that ensure our editorial content is never influenced by advertisers.

ZDNET's editorial team writes on behalf of you, our reader. Our goal is to deliver the most accurate information and the most knowledgeable advice possible in order to help you make smarter buying decisions on tech gear and a wide array of products and services. Our editors thoroughly review and fact-check every article to ensure that our content meets the highest standards. If we have made an error or published misleading information, we will correct or clarify the article. If you see inaccuracies in our content, please report the mistake via this form.

Close

How to create a drop-down list in Excel and save yourself hours of time

Adding a drop-down list in Excel is a great way to speed up data entry, keep your sheet tidy, and avoid typos. Here's how to set one up.
How to easily create a drop-down list in Excel (and simplify your data entry life)
Maria Diaz / Elyse Betters Picaro / ZDNET

There are plenty of apps and websites available to keep you organized and on task, but for raw data power, nothing beats Microsoft Excel -- and for repetitive tasks, its drop-down lists are a real game-changer.

Whether you're juggling a side hustle, keeping tabs on family schedules, or just trying to make sense of a mountain of numbers, drop-down lists are a godsend that speed up typing. They can also cut down on typos and just keep everything looking neat. 

Also: How to use ChatGPT to write Excel formulas

Here's how to add them in Excel fast -- plus a few expert tips to tweak and style dropdowns to your liking.

How to add drop-down lists in Excel

What you'll need: You'll need a Microsoft 365 subscription to use Excel, whether you're on the web or the desktop app. I'm working from the desktop version here, so if your menu looks different online, Microsoft's support site can help you bridge the gaps. And yes, Google Sheets (and other tools) have dropdowns too, but the setup steps aren't exactly the same.

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

This article uses an example spreadsheet to better show how to create drop-downs. In our example, column A holds client names, and column B is for their communication preferences. Highlight the cells in column B -- because that's where you'll want a drop-down to pick between Email, Phone Call, or Text for each client.

Also: How to remove Copilot from your Microsoft 365 plan

Show more
Select the cells where you want a drop-down

Select the cells where you want a drop-down.

Maria Diaz / Elyse Betters Picaro / ZDNET

2. Select Data Validation in the menu

Head to the Data tab in the menu bar, then click Data Validation (on Mac or the web, it might just say Validation). You'll see the Data Validation dialog pop up -- this is where the magic happens.

Show more
Select Data Validation in the menu

Select Data Validation in the menu.

Maria Diaz / Elyse Betters Picaro / ZDNET

3. Select List as your validation criteria

In the Settings tab of the Data Validation pop-up, find the Allow drop-down menu and pick List.

Show more
Select List as your validation criteria

Select List as your validation criteria.

Maria Diaz / Elyse Betters Picaro / ZDNET

4. Enter the values for your drop-down

Once you pick List, a Source field will appear. In there, type your values or options separated by commas -- no spaces. For example:

Call,Email,Text,NS

That gives you four choices -- Call, Email, Text or NS ("Not Specified") -- in each drop-down.

Also: How to make LibreOffice look more like Microsoft Office

Show more
Enter the values for your drop-down

Enter the values for your drop-down.

Maria Diaz / Elyse Betters Picaro / ZDNET

5. Click 'OK' to create your drop-down

When you hit OK in the Data Validation pop-up to save your changes, Excel will insert your options right into the sheet. You'll see a little arrow beside each cell -- click it to open your drop-down list and pick an option. Whatever you choose will instantly fill the cell, no typing needed.

Show more
Click 'OK' to create your drop-down

Click 'OK' to create your drop-down.

Maria Diaz / Elyse Betters Picaro / ZDNET

Can you edit a drop-down list after?

Absolutely. Just highlight the cells, go back to Data > Data Validation, and tweak the list of items in the Source box.

Also: How to use ChatGPT's Advanced Data Analysis to create quality charts and tables

Pro tip: If you have a longer list (or want to easily tweak your options later), you can type them into a column on another sheet and then point the Source field in the Data Validation pop-up to that range (e.g. =Sheet1!D1:D10).

Is there a limit to the number of items?

Excel lets you add over 30,000 options to drop-down lists -- but scrolling through that many choices is a real chore. If you have a huge list, try splitting it into categories or using multiple, smaller dropdowns so it's easier.

Want to create a yes/no dropdown?

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". 

  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 dropdowns?

Yes. You can use Conditional Formatting to apply colors to cells. 

  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.

Is a formula needed to create dropdowns?

No. A formula is not needed to create drop-down lists in Excel. All you need to do is use the Data Validation menu.

Are dropdowns the same as data filtering?

People often mix up filtering with dropdowns, but they're not the same. Filters sit in your column headers so you can hide or show rows -- say by month, name, or location -- while drop-down lists live inside individual cells, letting you pick a value to enter.

Can you use Copilot to create dropdowns?

No, Copilot in Excel doesn't yet let you set up Data Validation drop-down lists directly via a prompt. You'll still need to use Excel's built-in Data Validation feature to insert those menus into your spreadsheet.

Get the morning's top stories in your inbox each day with our Tech Today newsletter.

Editorial standards