How to Remove a Drop-Down List in Excel (Step-by-Step)

Drop-down lists are the most popular data validation tool in Microsoft Excel.

It allows you to enter data faster by selecting values from a list of options rather than typing them manually in a cell. Aside from that, drop-down lists look pretty awesome too! 😎

Because of this, we’ve made a step-by-step article on how to create a drop-down list in Excel.

But what do you do when you want to remove it? 🤔

In this article, we’ll cover how to exactly do that. Download this free Excel workbook we’ve created for you to work along the way.

Table of Contents

How to Remove A Specific Drop-down List in Excel

While drop-down lists are super helpful in data entry, there may be cases when you need to remove them.

Usually, you remove the drop-down lists when you no longer need them, or you want to remove items in the existing options in the list.

Whatever the reason, deleting drop-down lists is a straightforward process and we’ll show you 3 methods on how. 👇

Method 1: Using the Data Validation Dialog Box

Open the sample Excel workbook. In the “Drop-Down” worksheet, you’ll see an Excel table.

Under the Department column, you will find cells with drop-down lists.

When you click a cell with a drop-down, in this example, cell C2, a drop-down arrow is seen. 👀

A drop-down menu will appear when you click the drop-down. This contains the list items which a user can only select from.

To delete the drop-down lists in your Excel spreadsheet, the first method is by using the Data Validation Dialog Box. To do this,

Step 1) Select the cells that have the drop-down lists you want to delete.

Step 2) Go to the Data Tab.

Step 3) In the Data Tools group, click the Data Validation icon. This will open the Data Validation Dialog Box.

Step 4) In the Data Validation Dialog Box, click the Settings Tab.

Step 5) Click the Clear All button.

Step 6) Finally, click OK.

Voila! You’ve successfully removed the drop-down lists in your Excel worksheet.

Note that the values remained in the selected cells. This is because you’ve only cleared the data validation rules in the selected cells.

As drop-down lists are data validation rules, this applies. ✅

Using the Data Validation Dialog Box method is super helpful when you want to remove the drop-down lists but want to keep the values.

Pro tip

You can also open the Data Validation Dialog Box by pressing the keys Alt + A + V + V, one after another on your keyboard.

Method 2: Using the Clear All Button

If you don’t want to keep the values in your drop-down lists and want to remove everything (rules, values, and formatting) in your cells, you can use the Clear All button.

Here’s how.

Step 1) Select the cells that have the drop-down you want to remove.

Step 2) Click the Home Tab.

Step 3) In the Editing group of the Home Tab, click the Clear button.

Step 4) In the options shown, select Clear All.

Cleared! Using the Clear All button allows you to clear everything in the selected cells including rules, values, and formatting applied.

This method is best used when you want to clear everything and start anew in your Excel worksheet. ✨

Pro tip

One faster way to Clear All is to use its keyboard shortcut. First, select the cells, then press the keys ALT + H + E + A one after another. This works whenever you want to remove everything in your selected cells, not only to remove drop-down lists.

Method 3: Using the Copy & Paste Method

Another method to remove drop-down lists in Excel is by using the Copy & Paste Method.

In Microsoft Excel, drop-down lists are considered a type of cell formatting. 🎨

Just like you can copy and paste a drop-down list from one cell to another, you can also copy a blank cell (a cell without a drop-down list) and paste it on the cell with a drop-down.

This will remove the drop-down.

Let me show you how.

Step 1) Select a blank cell in your Excel worksheet.

Step 2) Press Ctrl + C to copy the blank cell. (Press Command + C for Mac users)

Step 3) Select a cell with a drop-down list.

Step 4) Press Ctrl + V to paste. (Press Command + V for Mac users)

You can also use the Copy & Paste method when removing a range of cells with drop-down lists. Just repeat Steps 1 and 2, then select the cells and paste.

How to Remove ALL Drop-Down Lists in your Excel Worksheet

If you’re dealing with a complex Excel worksheet with multiple drop-down lists, you can remove them all at once.

Here’s how you can do that.

Step 1) Press the F5 key on your keyboard, this will open the Go To dialog box.

Step 2) Click on the Special button. This will open the Go To Special dialog box.

Step 3) Select Data Validation.

Step 4) Under Data Validation, click All.

Step 5) Click OK.

This will select all the cells that have data validation rules applied to them. Now that the cells are all selected…

Step 6) Click the Data Tab.

Step 7) In the Data Tools group, click on the Data Validation button to open the Data Validation Dialog Box.

Step 8) Click the Settings Tab in the Data Validation dialog box

Step 9) Click on the Clear All button

Step 10) Finally, click OK.

This will remove all the drop-down lists in your Excel worksheets in one go. ⚡

That’s It — Now What?

Nice work! 🙌

Removing drop-down lists in Microsoft Excel is as easy as creating them.

If you want to learn more Excel tips and tricks, here are related articles that could help you:

• How to Create a Drop-down List in Excel (in 60 Seconds)
• How to Edit Drop-Down Lists in Excel
• How to Use Data Validation in Excel: Full Tutorial
• How to Remove Formulas in Excel (and Keep Values)

FAQ