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

Get FREE Advanced Excel Exercises with Solutions!

In MS Excel drop down list helps us to enter input correctly and properly. It is one of the most common properties in Excel. Fortunately, Excel provides all the options regarding this drop down list like creating, editing, or removing the drop down list. In this article, we will see how we can remove any existing drop down list in Excel.


Download the Practice Workbook


What Is Drop Down List in Excel?

The drop down list is useful to restrain the user from entering input data and provides the option to choose from the list. A drop down list carries a list of multiple inputs. We need to delete or remove the drop down list as the user will be able to input any data instead of choosing from a list. Although we can offer a limited input option to the user using a drop down list which is useful in many cases.


2 Ways to Remove Drop-Down List in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we formatted all the cells containing dollar values in Accounting format. For all the datasets, we have 3 unique columns which are Product, Price, and Payment Method. Although we may vary the number of columns later on if that is needed.

how to remove drop down list in excel


1. Clear Drop-Down List from Data Validation Option in Excel

Let’s say that we have a product information dataset with their Product Name, Unit Price, and Payment Method. In the payment method column, we have inserted all the values using a drop-down list. Now our task is to clear or remove the drop down list using the Data Validation option in Excel.

Steps:

  • To begin with, select the Data Validation option under the Data tab (in the subsection of Data Tools).

using data validation to remove drop down list in excel

  • Then check the Apply these changes to all other cells with the same settings box. Next, click on the Clear All button and select the OK button (make sure to select Allow as List and the Source is valid.)

clearing data validation to remove drop down list in excel

  • Finally this should remove all the drop down lists from the dataset.

Read More: How to Create List from Range in Excel (4 Easy Ways)


2. Removing Drop Down List Using VBA in Excel

Excel also provides us with the option to remove the Drop Down list using VBA code also. In this section, we will use the same dataset above and remove the list using VBA.

Steps:

  • First, select the Visual Basic option under the Developer tab (shortcut key Alt + F11).

using vba code to remove drop down list in excel

  • Now, in the new window click on Insert and select Module.

  • After that write the following code in the VBA console:

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
  • Next, click on Macros under the Developer tab.

  • Then, select the name of the macro and click Run.

  • Consequently, the VBA code will remove will the drop down lists.

Read More: Excel Drop Down List Not Working (8 Issues and Solutions)


Things to Remember

Common Errors When they show
Unable to delete List In the Data Validation, if the Allow is not equal to List and the Source is not correctly selected then the drop-down list can’t be deleted.
Deleted all the Drop-Down lists after using VBA code If you want to delete some worksheets or range, Drop Down list then you must include the worksheet name and cell reference in the code. Otherwise, all the Drop-Down lists will be deleted.

Conclusion

These are some ways to remove the drop down list in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website.


Further Readings

Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

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