How to Remove Drop-Down List in Excel

How to Remove Drop-Down List in Excel

In MS-Excel drop-down list helps us to enter input correctly and properly. It is one of the most used 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 dropdown list is used 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 Dropdown list as the user will be able to input any data instead of choosing from a list.

How to Remove Drop-Down List in Excel

That’s how we can offer a limited option of input to the user using a drop-down list.

2 Ways 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 ID, Model, Brand, Unit Price, and Payment Type. In the payment type column, all the values are entered using a drop-down list. Now our task is to clear or remove the drop-down list using the Data Validation option.

Clear Drop-Down List from Data Validation Option in Excel

Step1: Select the Data Validation option under the Data tab. (In the subsection of Data Tools)

Select the Data Validation option under the Data tab. (In the subsection of Data Tools)

Step 2: Then the first click on the Clear All button then the OK button (Make sure Allow is selected as List and the Source is valid.)

Then the first click on the Clear All button then the OK button

If you select Apply these changes to all other cells with the same setting checkbox, then all the cells which are containing the Drop-Down list will also be removed.

If you select Apply these changes to all other cells with the same setting checkbox,

Step 3: Then all the Drop-Down lists will be removed from this worksheet

How to Remove Drop-Down List in Excel

2. Remove 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 will remove the list using VBA.

Remove Drop Down List Using VBA in Excel

Step 1: Select the Visual Basic option under the Developer tab (Shortcut key Alt + F11)

Select the Visual Basic option under the Developer tab

Step 2: A window will be opened. Then Insert a Module

A window will be opened. Then Insert a Module

Step 3: Write the following code in the VBA console

Step 3: Write the following code in the VBA console

Code:

Sub Delete_Drop_Down_List()
   'To Delete List in the Selected Range"
   ThisWorkbook.Worksheets("Sheet3").Range("F4").Validation.Delete
   
   'To Delete All the List in the Worksheet
   ThisWorkbook.Worksheets("Sheet3").Cells.Validation.Delete
   
End Sub

Step 4: Now go to the worksheet and run the code

Now go to the worksheet and run the code

Step 5: All the Drop-Down lists will be removed

All the Drop-Down lists will be removed

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.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo