How to Remove Drop-down List in Excel? 3 Easy Ways!

A drop-down list provides a great way to let your user choose from a list of options.

It makes data entry quick and easy, which is why it is often used in situations where a cell can only contain a limited set of values.

However, it’s possible that you later decide that you don’t want to limit the user to a small set of options.

So, you might want to remove the dropdown list(s) from your spreadsheet. You’ll find that doing this is not as easy as just selecting the cell and pressing the Delete key.

In this tutorial, we will show you 2 ways to remove a drop-down list in Excel.

If you have more than one of them and want to get rid of them all in one go, then we will explain how you can do that too in the last part of this tutorial.

Table of Contents

Why Remove Drop-down Lists in Excel?

You might, at some point, decide that you no longer want to keep one or more dropdown lists in your spreadsheet.

Maybe you no longer want to limit the input options and want to allow the user more flexibility in the value that they enter into the cell.

There might also be cases where you’ve included dropdown lists in your sheet for easier data entry.

But once the data has been entered, you might want to remove the dropdown lists for aesthetic reasons or formatting requirements.

Whatever the reason, this tutorial is going to show you how to remove the dropdown lists from your sheet in Excel.

3 Ways To Remove Drop-down Lists in Excel

There are two ways to remove a drop-down list in Excel:

By using the Data Validation dialog box
By using the Clear button
By copy-pasting any blank cell

Both methods work equally well. However, the first method is the most commonly used, even though the second method is much quicker.

Let us look at each of the above methods one by one.

Remove Drop Down List in Excel using the Data Validation Dialog Box

A drop-down list in Excel is usually based on a list of cells (which could be on the same sheet or a separate sheet).

For example, in the screenshot below, the drop-down list in cell D2 is based on the list in the range A1:A6.

Drop-down list with the list it's based on

So, when you click on the dropdown list’s arrow, you see the same 6 items that are in the list.

This means that if you want to remove the dropdown list (in particular, you want to remove the dropdown arrow), you need to remove its connection to this list. This will convert it back to a regular cell.

The best way to do this in Excel is by using the Data Validation dialog box.

Let us see step by step how to use this method to remove a dropdown list in Excel. Let us use the same list shown in the above screenshot to demonstrate this method:

Select the cell containing your drop-down list (Cell D2 in our case).
From the Data tab, click on the ‘Data Validation’ button (you will find it in the Data Tools group).

This opens the Data Validation dialog box.

Now click on the Clear All button. You should find it at the bottom of the dialog box, as shown in the screenshot below:

Click OK.

You should now find your drop-down arrow, and subsequently, the drop-down list removed from your selected cell.

This method keeps the last value selected from the list within the cell, even after the drop-down list is removed.

This is especially helpful if you’ve created dropdown lists in your sheet for easy data entry, but later want to remove the lists (for formatting or aesthetic reasons).

With this method, you can easily do so, without having to lose the selected values.

If you don’t need the source data list that your drop-down list was based on anymore, then you can go ahead and delete them as well.

Also read: How to Break an External Link in Excel

Remove Drop Down List in Excel using the Clear All Button

This method is quick and quite helpful if you want to remove the contents of multiple cells at the same time (cells containing drop-down lists included).

However, it’s important to note that this method doesn’t just remove the drop-down list, it also clears the entire contents of the cell.

Therefore, if you want to retain the last selected item from your drop-down list, you might be better off using the previous method.

Let’s assume that we are using the same example data that we used to demonstrate the previous method.

Here are the steps to remove the drop-down list using the Clear All Button in Excel:

Select the cell(s) containing your drop-down list(s).
From the Home tab, select ‘Clear’ from the Editing group.

This displays a list of Clear options. Select Clear All from this list.

This should clear the entire contents of your selected cell(s) including any formatting or data validation rules (or drop-down lists).

Drop-down from the cell has been removed

Also read: How to Remove Formulas in Excel But Keep Data

Remove Drop Down List using Copy-Paste Method

One quick and dirty way to remove drop-down lists from any cell or range of cells is to simply copy any blank cell in your worksheet and paste it over the cells that have the drop-down list.

Doing this would remove the drop-down list and copy the formatting of the cell that you copied.

Since you have copied a blank cell there is no content in it, so no text gets copied.

One drawback of this method is that it would remove any formatting that is already there in the cell that had the drop down (such as any borders or colors). It would instead apply the format of the copied cell.

Also read: How to Select Multiple Items from a Drop Down in Excel?

Removing All Drop-Down Lists in a Spreadsheet

Now, what if you have multiple drop-down lists in your sheets that are next to each other (say in the same column or row), and want to delete all of them in one go?

In that case, all you need to do is select the cells and use the Clear All method.

However, if you have them spread out over your spreadsheet, then selecting them individually to delete them can be quite time-consuming.

The Go to Special dialog box provides an easier way to select (and subsequently remove) all the drop-down lists in your sheet at the same time.

Now, there are two cases to consider if you want to use this method to remove all drop-downs in your sheet.

The first case is when all your drop-down lists are the same, i.e., they are based on the same list and are basically copies of each other.

The second case is when your drop-down lists are different, i.e., based on different data lists, or have different validation settings.

Remove All Drop-Downs Based on the Same List

In the first case, follow the steps shown below:

Select any one of the drop-down lists that you want to remove.
Open the Go to Special dialog box, either by pressing CTRL+G and then clicking on the Special button, or by navigating to Home -> Editing -> Find & Select -> Go to Special.

Click on Special button in the Go To dialog box

From the Go to Special dialog box, check the radio button next to Data Validation.

Select the radio button next to ‘Same’.

This will select all the drop-down lists that have the same validations rule as the one you had selected in step 1.

Now you can remove these drop-down lists using any of the two methods discussed in this tutorial.

Remove All Drop-Downs Based on the Different Lists

In the second case, follow the steps shown below:

Open the Go to Special dialog box, either by pressing CTRL+G and then clicking on the Special button, or by navigating to Home -> Editing -> Find & Select -> Go to Special.

From the Go to Special dialog box, check the radio button next to Data Validation.

Select the radio button next to ‘All’.

This will select all the drop-down lists (and also any other cells containing data validation) in your sheet.

Now you can remove these drop-down lists using any of the two methods discussed in this tutorial.

However, note that the first method (using the Data Validation dialog box) can be a little cumbersome in this case and might slow your processing down.

It is advised to use the second method (the one using the Clear All button) when you want to remove multiple drop-down lists.

It’s also important to note that any other data validation rules that you might have in your sheet will also get selected and removed.

In this tutorial, we showed you two ways to remove drop-down lists from your Excel sheet.

We also showed you what to do when you have multiple drop-down lists spread out over your Excel sheet, and want to remove them all in one go.

We hope you found the tutorial helpful.

Other articles you may also like: