For example, you’re managing a project at work, and you have an Excel sheet with a list of tasks. Each task has a drop-down list assigned to it for assigning team members. Now the project is over, you need to send this sheet to the finance department for budget review.
They only need to see the tasks and costs, not the team member assignments. So, you decide to remove the drop-down lists from the sheet to make it clearer and more relevant for the finance team.
We will learn three methods in this tutorial:
- The first method only removes the drop-down list, not the value that you have in the cell.
- The second method clears the drop-down list as well as the value from the cell.
- The third method deletes the cell entirely.
The quickest way to remove the drop-down list is to use the keyboard shortcut Alt ⇢ H ⇢ E ⇢ A.
Steps to Remove a Drop-Down List
To get rid of a drop-down list, use the following steps:
- First, select the cell or range of cells where you have the data validation drop-down list.
- After that, go to the Data Tab ⇢ Data ⇢ Data validation ⇢ Data Validation (You can also use the keyboard shortcut Alt ⇢ A ⇢ V ⇢ V).
- Now, you have the Data Validation dialog box, and you need to click the “Clear All” button.
- In the end, click OK to close the data validation dialog box.
As you can see the value which you have in the cell is still there, not deleted.
Get Rid of Drop Down List with Clear All Option
- First, select the cell with the drop-down list.
- After that go to the Home Tab ⇢ Editing ⇢ Clear All.
- Now, click on the “Clear All” option from here.
The moment you click on the option, it clears everything from the cell including the drop-down list that you have in the cell.
You can also use the shortcut key Alt ⇢ H ⇢ E ⇢ A to clear content from the cell. Apart from that, from the right-click menu you can use the clear content option to delete the drop-down list from the cell.
Delete Cell Entirely to Remove the Drop Down List
There could be a situation when you want to delete the cell to delete the drop-down list.
- First, select the cell and right-click on it.
- And, from the right-click menu, click on the “Delete” option.
- Now, here you have the dialog box to delete the cell.
- From here, choose any of the above two options and click Ok to delete the cell.
This will delete the selected cell data validation will also be gone with it.
Remove All the Drop Down Lists with GoTo Special
Using the “Go To Special” to delete drop-down lists in Excel is easy because it selects all cells that contain data validation rules, including drop-down lists, with just a few clicks.
Here are the steps you need to follow:
- Once you open the worksheet where you have drop-down lists, make sure to check that you want to delete all the lists from the sheet.
- Then, go to the “Home” tab, then in the Editing group, click on “Find & Select” and choose “Go To Special” from the dropdown.
- In the Go To Special dialog box, select “Data Validation” and then choose “All” to highlight all cells that have data validation. This will select all the cells with the data validation in the sheet.
- Now, with these cells selected, go to the “Data” tab and click “Data Validation”. Click “Clear All” and then click “OK” to remove all the drop-down lists.
Remove All Drop-Down Lists in the Worksheet (Data Validation)
You can also use a VBA code that deletes all the drop-down lists from a worksheet in one go. It iterates through every cell in the sheet’s used range and clears any data validation rules that might be applied.
Sub RemoveDropDowns()
Dim cell As Range
Dim ws As Worksheet
' Set the worksheet object to the active worksheet
Set ws = ActiveSheet
' Loop through each cell in the used range of the worksheet
For Each cell In ws.UsedRange
' Check if the cell has data validation applied
If Not cell.Validation Is Nothing Then
' Remove data validation from the cell
cell.Validation.Delete
End If
Next cell
' Inform the user that all drop-downs have been removed
MsgBox "All drop-down lists have been removed from the active sheet.", vbInformation
End Sub Only Delete Drop-Down Lists which Isn’t Referred in Formula
There might be a situation when you have multiple drop-down lists in your worksheet. Some of these drop-downs are referred to in some formulas within the worksheet. Now, you only want to delete drop-down lists that are not linked or referred to in any formulas.
The best way for this is to use a VBA code, as we have used in the above example:
Sub RemoveIsolatedDropDowns()
Dim cell As Range
Dim ws As Worksheet
Dim hasDependents As Boolean
' Set the worksheet object to the active worksheet
Set ws = ActiveSheet
' Loop through each cell in the used range of the worksheet
For Each cell In ws.UsedRange
' Check if the cell has data validation applied
If Not cell.Validation Is Nothing Then
' Check if the cell has dependents
On Error Resume Next ' Use error handling to manage cells with no dependents
hasDependents = Not cell.Dependents Is Nothing
On Error GoTo 0 ' Turn off error handling
' If the cell has no dependents, delete the data validation
If Not hasDependents Then
cell.Validation.Delete
End If
End If
Next cell
' Inform the user that the process is complete
MsgBox "Isolated drop-down lists have been removed.", vbInformation
End Sub Points to Take Care
With all the methods we have learned here, there are a few points you need to take care of.
- Before removing drop-down lists, verify that no formulas or other workbook functions depend on the drop-down. That’s why we have shared a specific code to delete the non-dependent list.
- Ensure that removing the drop-down does not delete or remove the existing values in the cells.
- Always make a backup of your Excel file before removing drop-down lists.