How to Create a Drop-Down List in Excel

What a drop-down list does and why you'd use one

A drop-down list in Excel is a cell that shows a small arrow when you click it, revealing a list of choices you've already created. Instead of typing the same entries over and over, you pick from the list. This keeps data consistent — everyone enters "New York" the same way, not as "NY" or "new york" or "New York City".

Drop-downs are useful when you're building a spreadsheet other people will fill in, or when you're entering data yourself and want to avoid typos. They work in any version of Excel on Windows or Mac, and they travel with your file if you share it.

Key Takeaways

Create your list of choices first in an empty area of the spreadsheet, then use Data Validation to point the drop-down to those cells.
The simplest method is to select the cell where you want the drop-down, go to Data > Data Validation, choose List, and type your options separated by commas.
If you want the same drop-down in multiple cells, select all of them at once before explore Data Validation.
Drop-downs work in shared files and on different computers, but the person using the file must have Excel or a compatible program that supports this feature.

The fastest way: typing your list directly

If you have only a few choices — say, three or four — you can type them straight into the Data Validation box without creating a separate list first.

Click the cell where you want the drop-down. Go to the Data tab at the top of the ribbon. Click Data Validation (in some older versions of Excel, this is called Validity). A dialog box opens. Under Allow, choose List. In the Source box, type your options separated by commas with no spaces after the commas: Yes,No,Maybe or North,South,East,West. Click OK. The cell now has a drop-down arrow.

This method works fast for short lists. For longer lists or lists you might reuse in other spreadsheets, the next method is cleaner.

The better way: creating a list first, then linking to it

If your list has many items, or if you want to use the same list in multiple places, create it once in a separate area of your spreadsheet. Put each item in its own cell, stacked vertically. For example, in column F starting at F1, type: Product A, Product B, Product C, Product D, and so on down to F10.

Now select the cell (or cells) where you want the drop-down to appear. Go to Data > Data Validation. Under Allow, choose List. In the Source box, type the range of cells that hold your list: $F$1:$F$10. The dollar signs lock the range so it doesn't shift if you copy the drop-down elsewhere. Click OK.

If you later add more items to your list in column F, the drop-down automatically includes them — as long as they're within the range you specified. If you add items beyond F10, you'll need to update the range in Data Validation.

Putting the same drop-down in many cells at once

If you need the same drop-down in ten cells, or fifty, select all of them before you set up Data Validation. Click the first cell, then hold Ctrl (or Cmd on Mac) and click each additional cell. Or click the first cell, hold Shift, and click the last cell to select a continuous range.

With all cells selected, go to Data > Data Validation and set up your list as usual. The drop-down appears in every selected cell. This saves time and ensures they all use the same list.

Fixing a drop-down that isn't working

If you set up a drop-down but the arrow doesn't appear, or it appears but shows an error, check these things first. Make sure you selected List under Allow, not Text or Whole Number. If you typed your list directly into the Source box, verify there are no spaces after the commas. If you linked to a range of cells, make sure those cells actually contain data and the range is correct — click in the Source box and you should see the cells highlight in your spreadsheet.

If you copied a drop-down from one cell to another and it stopped working, the range reference may have shifted. Go back to Data Validation and check that the Source still points to the right cells. Using dollar signs ($F$1:$F$10 instead of F1:F10) prevents this problem.

If someone opens your file in Google Sheets or another program, the drop-down may not work the same way. Excel drop-downs are most reliable when the file stays in Excel.

Controlling what happens when someone picks from the list

By default, when someone picks an item from your drop-down, it just appears in the cell. You can add a message that explains what the list is for. In the Data Validation dialog, click the Input Message tab. Type a title and a message — for example, "Select your department" and "Choose from the list below." When someone clicks that cell, they see your message.

You can also set up an error message that appears if someone tries to type something that's not on the list. Click the Error Alert tab and choose Show Error Alert. Set the style to Stop (which blocks the entry), Warning (which allows it but discourages it), or Information (which just notifies them). Type your message. Now if someone types "Accounting" when the list says "Finance," they'll see your error message and have to pick from the list instead.

Removing or editing a drop-down

To remove a drop-down, select the cell, go to Data > Data Validation, and click Clear All. The cell becomes a normal text cell.

To change what's in the list, go back to Data Validation and edit the Source box. If you linked to a range of cells, you can also just edit those cells directly — the drop-down updates automatically. If you typed the list directly into the Source box, you'll need to open Data Validation again and change the comma-separated list.

Frequently Asked Questions

Can I make a drop-down that shows different lists based on what's in another cell?

Yes, but it requires a more advanced technique called dependent drop-downs. You create multiple lists (one for each category) and use a formula in Data Validation to show the right list based on what someone picks in the first drop-down. This is possible in Excel but takes some setup — search for "dependent drop-down Excel" if you need this feature.

What if I want the drop-down list to appear in a different order?

Rearrange the items in the cells where you stored your list. If you typed them in F1 through F10, just move them around in those cells. The drop-down will show them in the new order. If you typed the list directly into the Source box as comma-separated values, open Data Validation and retype them in the order you want.

Does the drop-down work if I save the file as a PDF or send it to someone using Google Sheets?

No. Drop-downs only work in Excel files (.xlsx or .xls). If you save as PDF, the drop-down becomes a regular cell. If you upload to Google Sheets, Google has its own data validation feature, but Excel drop-downs don't transfer directly — you'd need to recreate them in Google Sheets.

Can I delete items from the list without deleting the whole drop-down?

Yes. If your list is in separate cells (like F1:F10), just delete the text in those cells. The drop-down stays, but those items no longer appear in the list. If you typed the list directly into Data Validation as comma-separated values, open Data Validation and remove the item from the list.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.