How to Insert a Drop-Down List in Excel

To insert a drop-down list in Excel, select the cell, go to Data > Data Validation, choose List in the Allow box, type your choices or select a range of cells in the Source box, and click OK. An arrow appears next to the cell, and clicking it shows your choices.

These steps are for Excel for Microsoft 365 on Windows, and they also work in Excel 2024, 2021, 2019 and 2016. Excel for Mac and Excel for the web use the same Data Validation command on the Data tab; the differences are covered in their own section below.

Decide Where the Choices Will Come From

Before you start, decide how you’ll store the items in the list. There are three options:

• Type them in the Source box. Best for a short list that rarely changes, such as Yes, No, Maybe or High, Medium, Low.
• Use a range of cells. Good when the choices are already on a worksheet. The drop-down shows whatever is in those cells.
• Use an Excel table. Best when the list will grow. Microsoft recommends this in its guide to creating a drop-down list, because a list based on a table picks up new items automatically when you add rows to the table.

If you’re not sure, use the table method. It takes about 30 seconds longer to set up and saves you from editing the rule every time the list changes.

Method 1: Type the Choices Directly

1. Select the cell where you want the drop-down. To add the same list to several cells, drag across all of them.
2. Click the Data tab at the top of the window.
3. Click Data Validation in the Data Tools group. The Data Validation dialog box opens on the Settings tab.
4. Click the Allow drop-down and choose List. A Source box appears.
5. Type your choices in the Source box, separated by commas, for example Yes,No,Maybe. In some regions Excel uses a semicolon as the separator instead, so use Yes;No;Maybe if commas don’t work.

6. Make sure In-cell dropdown is checked.
7. Click OK.
8. Click the cell. A small arrow appears on its right side. Click the arrow and pick an item to make sure every choice shows up.

Example: A Category Drop-Down

In Excel for Microsoft 365 on a Windows 11 laptop, I selected an empty cell under the Category column in a small sample sheet of garden supply orders (item, category, quantity, price, order date and a Qty times Price total), opened Data > Data Validation, set Allow to List and typed Seeds,Supplies,Tools in the Source box. In-cell dropdown was checked by default.

After I clicked OK, selecting the cell and pressing Alt + Down Arrow opened the list with the three categories in the order I typed them.

Method 2: Use a Table or Range of Cells

1. On a worksheet, type the list items in a single column, one per cell, with a header above them (for example, Status in A1 and the choices in A2 through A6). You can put the list on a separate sheet to keep it out of the way.
2. Click any cell in the list and press Ctrl + T. Confirm the range in the Create Table box, check My table has headers, and click OK. Your list is now an Excel table.
3. Go to the cell where you want the drop-down and select it.
4. Click Data > Data Validation.
5. On the Settings tab, choose List in the Allow box.
6. Click inside the Source box, then select the list items on the worksheet. Don’t include the header. Excel fills in a reference such as =Sheet2!$A$2:$A$6.

7. Check Ignore blank if it’s fine for people to leave the cell empty, and make sure In-cell dropdown is checked.
8. Click OK, then test the arrow.
9. Add a new item in the next empty row of the table, then open the drop-down again. The new item should be in the list.

If you skip step 2 and use a plain range, the list still works, but new items added below the range won’t appear until you edit the Source reference. If your list has repeated entries, remove duplicates in Excel first so each choice only shows once.

Add an Input Message or Error Alert (Optional)

The Data Validation dialog has two more tabs that control how the cell behaves.

Input Message shows a small note when someone selects the cell. Check Show input message when cell is selected, type a title and message (for example, “Pick a status from the list”), and click OK.

Error Alert controls what happens when someone types something that isn’t in the list. Check Show error alert after invalid data is entered and pick a Style:

• Stop blocks the entry completely. This is the default and the best choice for most lists.
• Warning shows a message but lets the person keep the entry if they click Yes.
• Information shows a message and accepts the entry.

Keep in mind that validation only checks values that are typed in. Values that are pasted into the cell, or filled in with the fill handle, can get past it. To find entries that don’t match the list, click Data, click the arrow next to Data Validation, and choose Circle Invalid Data.

Excel for Mac and Excel for the Web

Excel for Mac: Select the cell, click the Data tab, and click Data Validation. On the Settings tab, choose List in Allow, enter the choices or select the range in Source, keep In-cell dropdown checked, and click OK. To turn a list into a table on a Mac, press Command + T.

Excel for the web: Select the cell, click Data > Data Validation, and use the same Settings tab. Choose List, enter your source, and click OK. You need edit access to the workbook.

Edit the Choices in a Drop-Down List

How you change the list depends on where the choices live:

• Table source: Type a new item in the next row of the table, or delete a row to remove an item. The drop-down updates on its own.
• Range source: Change the cells, then select the drop-down cell, open Data > Data Validation, and adjust the Source reference if the list got longer or shorter.
• Typed source: Open Data > Data Validation and edit the text in the Source box.

If the same list is used in many cells, check Apply these changes to all other cells with the same settings on the Settings tab before you click OK, so they all update together.

Copy a Drop-Down to Other Cells

To copy only the drop-down rule without overwriting what’s in the other cells:

1. Select a cell that already has the drop-down and press Ctrl + C.
2. Select the cells where you want the same list.
3. On the Home tab, click the arrow under Paste and choose Paste Special.
4. Choose Validation and click OK.

A regular paste also copies the rule, but it replaces the destination cells’ values and formatting too.

Remove a Drop-Down List

1. Select the cell or cells with the drop-down.
2. Click Data > Data Validation.
3. On the Settings tab, click Clear All, then click OK.

The value that was already picked stays in the cell, but the arrow and the rule are gone. Deleting the cell’s contents doesn’t remove the drop-down. To find every validated cell on a sheet at once, read about removing data validation in Excel.

If the Drop-Down Isn’t Working

• No arrow appears. Select the cell, open Data Validation, and check that In-cell dropdown is checked. The arrow only shows when the cell is selected.
• Data Validation is grayed out. The sheet may be protected or shared. Go to Review > Unprotect Sheet (you may need the password), or stop sharing the workbook, then try again.
• New items don’t show up. The source is a plain range that doesn’t include the new cells. Convert the list to a table or update the Source reference.
• Blank lines show in the list. The source range includes empty cells. Shrink the range so it only covers the choices.
• Commas didn’t split the choices. Your region uses a different list separator. Try semicolons.

Once the list is working, you can use a VLOOKUP formula in the next cell to pull in a price or other detail for whichever item is picked.

After the list is set up, you can also use IF formulas to color cells in Excel based on the choice someone picks.

Excel Drop-Down Questions