Removing a Drop-Down List in Microsoft Excel

A drop-down list in Excel is a data validation rule. To remove it, you clear that rule from the cells. The values people already picked stay in the cells, so you only lose the list, not your data.

Remove a drop-down list from selected cells

1. Select the cells that have the drop-down list. To pick cells that aren’t next to each other, hold Ctrl (Windows) or Command (Mac) while you click.
2. Open the Data tab and, in the Data Tools group, select Data Validation.
3. On the Settings tab, select Clear All, then OK.

The arrow next to the cells disappears, and people can type any value again.

Find every cell that has a drop-down

In a large sheet it’s easy to miss a few cells. Let Excel select them for you:

1. On the Home tab, select Find & Select, then Data Validation. Excel selects every cell on the sheet that has a validation rule.
2. Open Data → Data Validation. If Excel says the selection contains more than one type of validation, select OK to erase them.
3. Select Clear All, then OK.

Remove the list and the values

If you want empty cells as well, clear the drop-down first, then select the same cells and press Delete.

Tidy up the source list

Many drop-downs read their options from a range of cells, often on a separate sheet. Once no cell uses the list any more, you can delete that range or sheet. If the drop-down used a named range, remove the name in Formulas → Name Manager as well.

Changing the list instead

If you only need different options, you don’t have to remove the drop-down. Open Data → Data Validation on one of the cells, edit the Source box, and tick Apply these changes to all other cells with the same settings before you select OK.

Need Excel? Our Office 2021 Professional Plus key includes Excel, Word, PowerPoint, Outlook, Access and Publisher, as a one-time purchase.