How to Remove Drop Down List in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

A drop-down list helps keep data consistent and makes entering data easier. Sometimes, you may need to remove these lists to match new data or improve your spreadsheet. You may also need to do this if your data needs changing, you don’t need list validation anymore, errors happen because of list limits, or you want to clean up your worksheet.

In this Excel tutorial, we’ll walk you through the process of removing a drop-down list by directly using the Data Validation tool or by automating the procedure through VBA.

The following image shows the result of removing some of the drop-down lists in a column using the Data Validation feature:

example result of removing a drop-down list in excel


What Is a Drop-Down List in Excel?

A drop-down list in Excel is a customizable list that displays preset options for a user to choose from within a cell. This is particularly useful for ensuring data consistency and speeding up data entry.

For example, if you have a column where the only valid entries are “Yes” and “No”, a drop-down list containing these two options can be created in the cells of that column. When a user clicks on a cell, they can then select either “Yes” or “No” from the list, instead of typing it manually.


4 Ways to Remove a Drop-Down List in Excel

To showcase the processes, we have the following spreadsheet. It contains three unique columns: Product, Price, and Payment Method; where Payment Method contains a drop-down list of the said payment methods to choose from.

data table containing drop-down lists in a column

Here are 4 ways to remove a drop-down list in Excel:


Using Data Validation Feature

Excel’s Data Validation feature can be used to both create and remove drop-down lists from Excel. It is the most common feature used to do so.

An advantage of using Data Validation is that it helps to prevent users from entering invalid data unintentionally, thereby reducing the likelihood of errors and the need for time-consuming data cleanup later.

You can use Data Validation to remove a drop-down list in Excel from the 2 following cases:

Case 1: From Selected Cells

To remove drop-down lists from selected cells in Excel, follow these steps:

  1. Select the range of cells.
    Note: CTRL+Click to select non-adjacent cells.
  2. Go to the Data tab > Data Tools group > Data Validation.
    navigating to Data Validation from the Data tab
  3. In the Data Validation dialog box, go to the Settings tab > Clear All button > OK.
    configuring Data Validation setting to clear drop-down list

The drop-down lists should now be removed from the selected cells.

result of using Data Validation to remove drop-down list in Excel

Case 2: From Entire Worksheet

To remove all drop-down lists from a worksheet in Excel, follow these steps:

  1. Select any cell containing a drop-down list.
  2. Go to the Data tab > Data Tools group > Data Validation.
    navigating to Data Validation from the Data tab
  3. Apply these actions in the Data Validation dialog box:
    • Go to the Settings tab.
    • Check the ‘Apply these changes to all other cells with the same settings’ box.
    • Click the Clear All button > OK.

    configuring Data Validation to remove all drop-down lists

The drop-down lists should now be removed from the selected cells.

removing all drop-down lists in Excel using Data Validation

Note: After removal, consider reviewing other cells for data validation rules to ensure consistency across your spreadsheet.


Applying Excel VBA

VBA in Excel is an extremely powerful tool for automating repetitive tasks like removing a drop-down list from a large number of cells across different sheets.

Here is how we can use VBA to remove a drop-down list in Excel:

  1. Go to the Developer tab > Code group > Visual Basic to open the Visual Basic Editor. Or, press ALT+F11.
    opening VBA from the Developer tab
  2. In the Microsoft Visual Basic for Applications Window, perform these actions:
    • Go to the Insert tab > Module to create a new module for writing your code.
    • Input 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.
  3. Go to the Developer tab > Code group > Macros.
    opening Macros from the Developer tab
  4. Select the new Macro > Run.
    running macro to remove drop-down list

The drop-down lists from the defined range are now removed using Excel VBA.

removing drop-down list in Excel using VBA


Applying the ‘Clear’ Option

To apply the ‘Clear’ option for removing drop-down lists in Excel, follow these steps:

  1. Select the cells with drop-down lists.
  2. Go to the Home tab > Editing group > Clear > Clear All.
    navigating to the Clear option from the Home tab

As a result, the drop-down lists will be removed, including all the content and formatting.

result of using Clear All to remove drop-down list in Excel

Note: Before removing a drop-down list, especially from a shared or important document, consider making a backup of your workbook.


Using the Copy and Paste Method

To use Copy and Paste to remove drop-down lists in Excel, we must:

  1. Select an equal number of blank cells you want to remove the drop-down lists from.
  2. Press CTRL+C to copy the selected range.
  3. Select the range of cells with the drop-down lists.
  4. Press CTRL+V to paste the blank cells.
    using the copy and paste method to remove drop-down list in Excel

Note: This is an alternative method that will remove pre-existing data at the paste location.


Download the Practice Workbook


Conclusion

In conclusion, the removal of a drop-down list in Excel is a quick and simple procedure. This capability is crucial in dynamic work environments where data entry requirements frequently evolve. We have shown multiple approaches you can take to remove the drop-down list, each catered to its own requirements. The Data Validation feature is the most straightforward and Excel VBA is the most efficient for repetitive tasks. For other, less automatic cases, we have the Clear All and Copy and Paste features.

Feel free to leave any queries you may have in the comments below.


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. The removal only affects future data entries.

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.

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.

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.


Further Readings


<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

2 Comments
  1. This DOES NOT WORK. Neither method works.
    I’ve gone to several websites, and nothing works.
    I CAN’T EVEN DELETE THE CELL!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo