Dropdowns in Excel are usually created to let users choose from the pre-defined options. However, once they submit their preferred option, you may no longer require the dropdown except for their final answer.
And, in some cases, you may need to edit and remove only the particular dropdown values.
In both cases, you can use the Data validation tool to remove these lists along with all their associated dropdowns on your worksheets. Also, you can search across the whole worksheet for both similar and different kinds of dropdowns to remove them.
By Removing the Data Validation
Using this method, you can remove a dropdown along with all its associated dropdowns in the whole worksheet. Once you remove the list from a cell, it will only contain the most recently selected values.
- Select the cell (s) containing the drop-down list.
- Under the Data tab, select Data validation > Data validation. You can find the settings inside the Data tools section.
- On the next window, click Clear All.
- Additionally, enable the Apply these changes to all other cells with the same settings checkbox to select all the cells of the cell range with the same dropdown values. Then, click Clear All.
On the same Data validation window, you can select replace List with Any value under the Allow field to remove the dropdown.
Note: When selecting multiple dropdown lists with different data validation settings, you might be prompted with a message like “The selection contains more than one type of validation.” If so, just click OK to continue and then click Clear all.
Using the Copy-Paste Method
This method utilizes the fact that when you copy a cell and paste it on another cell, its formatting is applied to the new cell. It’s useful in cases where you want to remove the dropdown and replace it with a preferred cell formatting.
- Copy a cell whose formatting you want to apply to the cell containing the dropdown list. Choose a blank cell if you want no formatting after the removal.
- Select the cell (s) containing the dropdown lists.
- Right-click and select the Paste option.
Note: This method will remove the dropdown list completely along with all of its options.
Using the Clear All Option
With this option, you can delete the dropdown list completely with all its values. However, you must already know the cell references containing the dropdown lists.
- Select the dropdown lists.
- Click the Home tab.
- Select Clear > Clear all.
Using Go To Method
If you have dropdowns scattered all over your worksheet, finding and deleting them manually can be inconvenient. Using this method, you can choose to remove them irrespective of whether they are based on the same dropdown values or not.
- Select a cell inside a dropdown list and press Ctrl + G.
- On the Go To window, click Special.
- Then, select Data validation and choose one of the following options and click OK.
- All: Every dropdown cell that exists on the worksheet
- Same: Only the dropdown cells are based on the same dropdown list and data validation settings.
- Once all the dropdowns are selected, select Clear > Clear all under the Home tab.
How to Delete Dropdown List Items in Excel?
Instead of deleting the entire list, you may sometimes require to delete specific dropdown values. In that case, you can follow a slightly different process depending on what kind of dropdown list you have.
Dropdown with Manually Entered Inputs
- Select the cell (s) containing the dropdown list.
- Under the Data tab, select Data Validation > Data Validation.
- Select the Settings tab.
- Now, you can delete the dropdown items under the Source field.
- When done, click OK. Alternatively, enable the Apply these changes to all other cells with the same settings checkbox to update the dropdown options across all cells.
Dropdown Based on Cell Range or Named Range
- Select the worksheet where you have stored the dropdown list.
- Select the cell (s) with the dropdown values you want to remove.
- Then, right-click on it and select Delete. Here, we are removing the TipMonth option from the dropdown list.
- On the Delete window, select Shift cells up.
If you can’t figure out where the cell range or named range based dropdown values are stored in the list, follow the following steps.
- Select one of the cells with the dropdown list.
- Click Data validation under the Data tab.
- Click anywhere under the Source tab.
- Once the dropdown values are highlighted with the dotted lines, close the Data validation window.
- Now, repeat steps 3 and 4 mentioned above to delete the dropdown values.