How-To

How to Add or Remove a Drop-Down List in Excel

By Jeff Butts

Updated November 16, 2023

To help enter consistent data in your spreadsheets, drop-down lists are invaluable tools. Here’s how to add, change, or remove a drop-down list in Excel.

When I create a spreadsheet in Excel, the idea is to simplify and better organize some parts of my life. Whether it’s for personal budgeting, preparing to invoice a client, or making an inventory of my movies and video games, Excel can be a game-changer.

There are plenty of tips to help make Excel an even more effective tool for you. To help your spreadsheets be even more powerful and efficient, it’s important to make sure certain values are all entered the same way. Whether it’s to help filter the data later or search through it, ensuring all of my Blu-ray discs are called the same thing is crucial. That’s why knowing how to add (or remove) a drop-down list in Excel can be so handy. Let’s walk through using this advanced feature for yourself.

Add a Drop-Down List in Your Excel Spreadsheet

For this example, I’ll use a movie inventory. I’ve got some films on VHS, some on DVD, and some on Blu-ray. To help with filtering, sorting, and searching through the catalog later, I’ve got a column for “Media Type.”

I’ll add a drop-down list for “Media Type” so I can be sure I always have Blu-ray and the other formats spelled the same way. You do this using Data Validation.

How to Add a Drop-down List in Excel:

1. In your spreadsheet, select the cells you want to use in the drop-down list.
2. Either choose Data > Validation from the menu bar or the Data Validation icon in the Data ribbon.
   
3. The Data Validation dialog will appear. Under Validation criteria > Allow, choose List.
   
4. Next, in the Source field, enter the values you want to allow, separated by commas.
   
5. If the values are found in another sheet, you can also select them there by clicking the icon at the right side of the input box, then the cell or cells containing the allowed values.
   
6. If you want the drop-down list to show up when you click in the cell, make sure In-cell dropdown is checked.
7. Click OK to finalize your drop-down list.

Once done, you can click the value from the drop-down list rather than typing it. Of course, you can still type the value.

If you enter text that doesn’t match one of the values you specified for the drop-down list, Excel will alert you to the data validation error. For this reason, it’s often a good idea to make sure Ignore blank is enabled in the Data Validation dialog.

Changing or Removing a Drop-Down List in Excel

Should you decide you don’t want the data validation your drop-down list gives you anymore, it’s easy to remove. It’s also easy to add or remove values from the list of acceptable entries.

How to Change the Allowed Values in a Drop-down List

1. In the spreadsheet, select the cells using your drop-down list.
2. As before, choose Data > Validation from the menu bar. Alternatively, click the Data Validation button in the Data ribbon.
3. If you’ve input a list of values in the Source: field, edit that list. Otherwise, edit the range of cells included.

Easy, right? Now, what if you want to remove the dropdown totally? That’s also simple to do.

How to Remove a Drop-Down List from a Spreadsheet

1. Go back to your spreadsheet and select the cells that use your drop-down list.
2. Click Data > Validation in the menu bar. You can also just use the Data Validation icon in Excel’s Data ribbon.
3. Click Clear All.
   
4. Click OK, and the drop-down list is no longer present in the cells.

More Advanced Data Validation Settings in Excel

Sometimes, you might be creating this drop-down list for someone else’s benefit. If that’s the case, you may need to customize what appears as they’re doing the data entry.

Excel offers some advanced features even beyond functions and formulas. Among these, you’ll find you can even customize the message someone sees when they’re entering values. You can also modify what the alert shows and the text displayed when an invalid input is entered.

Here’s how you can use the features to really make your spreadsheet user-friendly.

How to Change the Input Message for a Drop-down List

1. Select the cells containing your drop-down list, then enter the Data Validation dialog as in the previous instructions.
2. Click Input Message from the available tabs.
   
3. To make sure the message appears whenever one of the cells is selected, enable the checkbox for Show input message when cell is selected.
4. If you want, enter a title and an input message to display.

Once configured, Excel will pop up a message like the one below when a cell, including the drop-down list, is selected.

Next, the error message. The default error message when someone enters invalid text is okay, but you may want to customize it a bit. I personally always like to add a bit of snark, but that might not be your style.

However, if you want to customize those error alerts, here’s how you can do it.

How to Set a Custom Error Alert for a Drop-Down List

1. From the Data Validation dialog, choose Error Alert.
2. Make sure Show error alert after invalid data is entered is checked. Of course, if you don’t want an error message displayed, you could uncheck the box.
   
3. Select the style you want for the error alert. This changes what icon is shown. Available styles include:
   • Stop, which displays a red circle with a white x in the middle.
   • Warning, which shows a yellow triangle around a black exclamation mark.
   • Information, the standard blue circle with a white “I” inside it.
4. Next, customize the title and error message to your own preferences.
5. To finish, click OK.

Once that’s done, you’ll see a custom error message any time you provide data in one of the drop-down cells that fails to match the values you’ve specified.

Get Excel to Do Your Heavy Lifting

There’s plenty of power in Excel. When you’re trying to extract, collect, or analyze data, it’s definitely useful to let your spreadsheet software handle the heavy lifting for you. You can find partial text matches with the right formula, calculate someone’s age based on their date of birth, and much more.

What’s your favorite Excel tip or trick? Let us know in the comments below.