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:
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.
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:
- Select the range of cells.
Note: CTRL+Click to select non-adjacent cells. - Go to the Data tab > Data Tools group > Data Validation.
- In the Data Validation dialog box, go to the Settings tab > Clear All button > OK.
The drop-down lists should now be removed from the selected cells.
Case 2: From Entire Worksheet
To remove all drop-down lists from a worksheet in Excel, follow these steps:
- Select any cell containing a drop-down list.
- Go to the Data tab > Data Tools group > Data Validation.
- 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.
The drop-down lists should now be removed from the selected cells.
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:
- Go to the Developer tab > Code group > Visual Basic to open the Visual Basic Editor. Or, press ALT+F11.
- 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.
- Go to the Developer tab > Code group > Macros.
- Select the new Macro > Run.
The drop-down lists from the defined range are now removed using Excel VBA.
Applying the ‘Clear’ Option
To apply the ‘Clear’ option for removing drop-down lists in Excel, follow these steps:
- Select the cells with drop-down lists.
- Go to the Home tab > Editing group > Clear > Clear All.
As a result, the drop-down lists will be removed, including all the content and formatting.
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:
- Select an equal number of blank cells you want to remove the drop-down lists from.
- Press CTRL+C to copy the selected range.
- Select the range of cells with the drop-down lists.
- Press CTRL+V to paste the blank cells.
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
- How to Create a Drop Down List from Another Sheet in Excel
- Create Excel Drop Down List from Table
- How to Edit Drop Down List in Excel
- How to Link a Cell Value with a Drop Down List in Excel
- How to Auto Update Drop-Down List in Excel
- How to Create Excel Drop Down List with Color
- How to Create Drop Down List with Filter in Excel
- How to Add Item to Drop-Down List in Excel
- How to Create a Drop Down List with Unique Values in Excel
- How to Copy Filter Drop-Down List in Excel
- Excel Drop Down List Not Working
<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This DOES NOT WORK. Neither method works.
I’ve gone to several websites, and nothing works.
I CAN’T EVEN DELETE THE CELL!!!
You can send the file to us, we shall take a look at your problem.