How to Remove a Drop-Down List in Excel

How to remove a drop-down list in Excel

To remove a drop-down list in Excel, select the cell, go to Data > Data Validation, click Clear All on the Settings tab, and click OK. The arrow disappears, and whatever value was already chosen stays in the cell.

One check first, because it saves a lot of frustration. Not every arrow on a cell is a drop-down list. Arrows across a header row usually belong to a table or a filter, and Clear All will not touch them. The section further down covers those.

TL;DR: Key takeaways

Data > Data Validation > Clear All > OK removes a drop-down list.
Ctrl + G > Special > Data validation selects every drop-down on the sheet.
The chosen values stay. Only the list and its restriction go.
Header-row arrows are usually a table or filter, not a drop-down.
Removing a drop-down is called clearing data validation, if you are searching menus.

Remove a drop-down list

Microsoft's steps:

Select the cell with the drop-down. For several cells, hold Ctrl and click each one.
Go to Data > Data Validation.
On the Settings tab, click Clear All.
Click OK.

A drop-down list is a kind of data validation, which is why the command lives there rather than anywhere near the list itself. Clear All removes the list and any restriction it placed on the cell, so anything can be typed there again.

Remove every drop-down on a sheet

In an inherited workbook you often do not know where the drop-downs are. Excel can find them for you:

Press Ctrl + G, then click Special.
Choose Data validation, then All, and click OK.
With those cells selected, go to Data > Data Validation > Clear All > OK.

Choosing Same instead of All selects only cells that share the validation rule of the cell you started in, which is useful when you want to remove one list and keep another.

When Clear All does not remove the arrow

If the arrow survives Clear All, it was never a drop-down list. Two things put arrows on cells that look the same at a glance.

A table. Data formatted as an Excel table shows sort and filter arrows in its header row. Click inside it, go to Table Design, and choose Convert to Range. Microsoft notes that afterwards "the row headers no longer include the sort and filter arrows." The table's colours stay, so see removing table formatting if you want plain cells as well.

A filter. Arrows on an ordinary range come from a filter. Go to Data and click Filter to switch it off.

A quick way to tell them apart: a drop-down arrow appears only on the cell you have selected, while table and filter arrows sit on every header cell at once.

What happens to the values

Clearing the drop-down does not clear the cell. If a row says "Approved" because someone picked it from the list, it still says "Approved" afterwards. Only the list of choices and the restriction are removed.

If you want the cells emptied as well, select them and press Delete after clearing the validation.

The same habit applies when tidying any workbook someone else built. Auditing an inherited workbook covers the other things worth checking before you change anything.

Frequently asked questions

How do I delete a drop-down list in Excel?

Select the cell, go to Data > Data Validation, click Clear All on the Settings tab, and click OK. For several cells at once, select them all first, or use Ctrl + G, Special, Data validation to find every drop-down on the sheet.

How do I remove data validation in Excel?

Data validation and drop-down lists are removed the same way: select the cells, open Data > Data Validation, and click Clear All. To find validated cells you cannot see, use Ctrl + G, then Special, then Data validation.

Will removing a drop-down delete my data?

No. The value already in each cell stays exactly as it is. Only the list of choices and the restriction on what can be entered are removed. Press Delete afterwards if you also want the cells emptied.

Why is the drop-down arrow still there after Clear All?

Because it is probably a table or filter arrow, not a drop-down list. For a table, use Table Design > Convert to Range. For a filtered range, click Data > Filter to turn the filter off.

How do I remove only one drop-down list and keep others?

Select a cell using the list you want gone, press Ctrl + G, click Special, choose Data validation and then Same. Excel selects only the cells sharing that rule. Then clear them with Data > Data Validation > Clear All.

What to do next

Clear the drop-downs you no longer need, then check whether any header arrows are a table or a filter rather than a list. For ready-made macros that clean up other parts of an inherited workbook, browse the free tools library.