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

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.

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).

• 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.)

• 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).

• 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

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

• How to Create Dynamic Dependent Drop Down List in Excel
• Create Drop Down List in Multiple Columns in Excel
• How to Auto Update Drop-Down List in Excel (3 Ways)
• Create a Drop Down List From Another Sheet in Excel
• How to Use IF Statement to Create Drop-Down List in Excel
• [Fixed!] Drop Down List Ignore Blank Not Working in Excel
• How to Fill Drop-Down List Cell in Excel with Color but with No Text