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.
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.
Step1: 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.)
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.
Step 3: Then all the Drop-Down lists will be removed from this worksheet
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.
Step 1: Select the Visual Basic option under the Developer tab (Shortcut key Alt + F11)
Step 2: A window will be opened. Then Insert a Module
Step 3: Write the following code in the VBA console
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
Step 5: 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.|
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.