How to Remove a Drop Down List in Excel

Q: Why can't I remove a drop-down list from a Protected Sheet?

Drop-down lists cannot be removed from a protected sheet because protection restricts changes to the sheet's structure and content. You must unprotect the sheet first before making any changes: Review tab > Unprotect Sheet.

Q: How to remove multiple different types of drop-down lists?

If your spreadsheet contains multiple drop-down lists, you'll need to remove each type individually or select a range of cells of the same types where these lists are located.

Q: What to do if I accidentally remove a drop-down list?

If you accidentally clear more than intended, remember you can use the Undo feature (Ctrl + Z) to revert the changes.

How to Remove a Drop Down List in Excel – 4 Methods

This is an overview:

The sample dataset showcases Product, Price, and Payment Method. Payment Method contains a drop-down list.

Method 1 – Using the Data Validation Feature

Case 1: From Selected Cells

Select the cell range.
Note: Press CTRL+Click to select non-adjacent cells.
Go to the Data tab > Data Tools > Data Validation.
In the Data Validation dialog box, go to the Settings tab > Clear All > OK.

The drop-down lists are removed from the selected cells.

Case 2: From the Entire Worksheet

Select a cell containing a drop-down list.
Go to the Data tab > Data Tools > Data Validation.
In the Data Validation dialog box:
- Go to the Settings tab.
- Check ‘Apply these changes to all other cells with the same settings’ .
- Click Clear All > OK.

The drop-down list is removed.

Method 2 – Using Excel VBA

Go to the Developer tab > Code > Visual Basic . You can also press ALT+F11.

In the Microsoft Visual Basic for Applications Window:

Go to the Insert tab > Module.

Enter the following code:

Sub Delete_Drop_Down_List()
'To Delete List in the Selected Range"
ThisWorkbook.Worksheets("VBA").Range("D5:D11").Validation.Delete
'To Delete All the List in the Worksheet
ThisWorkbook.Worksheets("VBA").Cells.Validation.Delete
End Sub

Save and close the window.

Go to the Developer tab > Code > Macros.
Select the new Macro > Run.

The drop-down lists are removed.

Method 3 – Applying the ‘Clear’ Option

Select the cells with drop-down lists.
Go to the Home tab > Editing > Clear > Clear All.

The drop-down lists are removed, including all content and formatting.

Method 4 – Using the Copy and Paste Method

Select a number of blank cells equal to the cells containing the drop-down lists.
Press CTRL+C to copy.
Press CTRL+V to paste the blank cells.

Download the Practice Workbook

Remove Drop-Down List.xlsm

Frequently Asked Questions

What happens to existing data when I remove a drop-down list?

Existing data in the cells will remain unchanged when you remove a drop-down list.

Why can’t I remove a drop-down list from a Protected Sheet?

You must unprotect the sheet first before making any changes: Review tab > Unprotect Sheet.

How to remove multiple different types of drop-down lists?

You’ll need to remove each type individually or select a range of cells of the same type.

What to do if I accidentally remove a drop-down list?

Use the Undo feature (Ctrl + Z) to revert the changes.