An Easy Guide to Remove Drop-down List in ExcelStep by Step Instructions with Screenshots

Written By:
Adiste Mae
Reviewed By:
FundsNet Staff

An Excel drop-down list helps users to work more efficiently by letting them select from a list of items. 

Thanks to Excel having this function, now it is easier and faster to do data processing by limiting the cell’s group of values. 

But what if you don’t want the user to be restrained to a small selection of items?

There are still instances where we prefer to not use the drop-down function from our spreadsheet.

You might think that it is as effortless as just the usual select and delete method but it’s actually quite tricky.

This tutorial will demonstrate to you how to revert a drop-down list in Excel in two different ways.

We will also show you how to remove more than one drop-down list in just a snap at the end of this article. 

Why Get Rid of Drop-down Lists in Excel?

Maybe one of the reasons you might also think to include drop-down lists is to provide users convenience in adjusting the values that they are entering in the cell. Or simply for more efficiency in doing data entry.

On the other hand, there are times when using dropdown lists is unnecessary, and you just want to get rid of it for aesthetic purposes or to comply with certain formatting requirements.

We are going to illustrate to you how to get rid of the drop-down lists in Excel.

Removing Drop-down Lists in Excel in 3 Easy Ways

With Excel, there are three ways in which you can get rid of a drop-down list. They are:

  • Using the Dialog Box of Data Validation 
  • Using the Clear Key
  • Using Any Blank Cell Duplicate

The three methods work the same way. Although the second method is faster to be done, the first method is always what users usually use.

To understand better, let’s break down the three methods.

Delete Drop-down Lists in Excel By Data Validation Dialog Box

In Excel, a drop-down list refers to a selection of cells that is either on the same or separate worksheets. 

In the illustration below, the drop-down list in cell D2 refers to the selection in the range A2:A11.

When you click on the drop-down arrow icon, consequently, you will view the same ten listed items.

Therefore, to undo and delete the drop-down list, same with its icon, you have to stop linking it to this list. This will then revert it to the original cell format.

In Excel, this first method-using the Data Validation dialog box is the best option to do the task.

To demonstrate the said method, let’s refer again to the previous example as our list. The following are the steps for deleting the dropdown list in excel: 

  1. Pick the cell having the drop-down list (in our example, Cell D2).
  2. Go to the Menu. Go to the Data Tab. Under the Tools group, click ‘Data Validation’.
  3. The Data Validation dialog box will pop up.
  4. At the bottom left of the dialog box, select Clear All.
  5. After that, at the bottom right of the dialog box, select OK.
  6. Now, check your sheet, and viola! No more drop-down arrows, meaning we already got rid of the drop-down list.  

Using this, even when the drop-down selection is cleared, all the values listed before will still be kept. 

This method is preferably useful when after creating the drop-down list for flexibility in doing the data entry and, soon after, you decide to delete the drop-down lists (either for aesthetic or format complying purposes).

This method ensures convenience while keeping the listed values. Moreover, you can also remove the list of reference data if it’s not already needed. 

Delete Drop-down Lists in Excel By Clear All Key

This second method is fast and useful for the simultaneous removal of multiple cell contents (including the cells with drop-down functions).

Warning: this method clears the whole cell contents, not just the drop-down selection. 

Hence, if your goal is to keep the previous drop-down selection, use the first method.

To demonstrate how the second method works, let’s refer to the previous example as our list. 

The following are the steps for deleting the dropdown list in excel:

  1. Pick the cell(s) having the drop-down list(s).
  2. Go to the Menu. Go to Home Tab. Under the Editing group, click ‘Clear’.
  3. A drop-down option will show. From these options, choose ‘Clear All’.
  4. The whole selected cell(s) contents should now be deleted. Any rules of data validation or formatting cells should be included.

Delete Drop-down Lists in Excel By Using a Blank Cell Duplicate

This third method is a really quick trick. Just duplicate a blank cell and put it over a cell with drop-down lists.

This will adapt the formatting of the blank cell resulting in the deletion of the drop-down list.

There will be no text when copied because a blank cell has no content.  

The downside of this method is the possibility of removing any cell formatting already created in a cell with a drop-down list. For instance, any cell colors or borders. The copied cell’s format will be the one eventually applied. 

How to Delete Every Drop-Down List in Excel Sheet?

There are also instances where you want to remove all the neighboring drop-down lists (either the same row or the same column) in your spreadsheets. 

Supposing that is the situation, just do the cell selection and the Clear All technique.

But then it would consume time if you have several cells with drop-downs and much more if they are scattered in your Excel sheet. Doing it manually would take time to do the task.

A shortcut to simultaneously select every cell with a drop-down list in your spreadsheet is by using the Go to Special dialog box.

There are two scenarios under this shortcut method:

  • First scenario – every cell with a drop-down list should all be the same. For example, they all came from one list and particularly, a duplicate of one another.
  • Second scenario – drop-down lists are different. For example, different cell references or settings with different validation.

Remove All Same-Lists Drop-Downs

For the first scenario, let’s refer to the following steps:

  1. Pick the cell(s) of every drop-down list(s) you like to delete.
  2. There are several ways to Go to the Special dialog box:
    • Press the CTRL key + G on your keyboard
    • Press the Special Key
    • Go to Menu. Go to Home Tab. At the Editing tools, click ‘Find & Select’. A drop-down will show. Finally, choose ’Go to Special’.
  3. The Go to Special dialog box will then pop up. Mark ‘Data Validation’.
  4. Mark ‘Same’.
  5. This will automatically pick every same-validations-rule cell with a drop-down list like the one we chose in the first step.

Remove All Different-Lists Drop-Downs

For the second scenario, let’s refer to the following steps:

  1. There are several ways to Go to the Special dialog box:
    • Press the CTRL key + G on your keyboard
    • Press the Special Key
    • Go to Menu. Go to Home Tab. At the Editing tools, click ‘Find & Select’. A drop-down will show. Finally, choose ’Go to Special’.
  2. The Go to Special dialog box will then pop up. Mark ‘Data Validation’.
  3. Mark ‘All’.
  4. This will automatically pick all cells with a drop-down list in your spreadsheet. This includes those cells with data validations.

At this point, you already know how to remove these drop-down lists by using either of the two methods. 

Among the two methods, the second method is easier and quicker compared to the first one which might require you more time and effort to finish.

When you want to delete several drop-down lists in your spreadsheet, the second method is recommended. 

Important Reminder: All the other rules on your spreadsheet’s data validation will also be picked and deleted.

Conclusion

In this tutorial, we demonstrated:

  • How you can delete drop-downs in your spreadsheet (2 Ways).
  • What you should do when you like to remove several cells with drop-downs scattered in your excel sheet all in a snap.

We hope you found it easy to follow and useful.