MS Excel’s dropdown lists are useful for creating quizzes, order forms, surveys, and more. A dropdown list makes data entry easy by making your spreadsheet dynamic.
The list is a set of predefined values. It provides a simple way to choose from a list of choices. If you no longer need the dropdown list in your spreadsheet, you can remove it. There are several options to remove dropdown lists, which we will explain step by step.
3 Methods to remove a dropdown list in Microsoft Excel:
- Using Data Validation Dialog Box
- Using Clear All Button
- Copy-Paste Option
Let us begin.
Method #1: Use the Data Validation Dialog Box
Use this method to remove one dropdown list at a time from your spreadsheet. We will explain how to disconnect the link between the list of items and the dropdown cell. Disconnecting the link converts the cell to a regular cell.
In our example, we use a spreadsheet with two sheets. Sheet1 stores two different predefined source lists for the dropdown. Sheet2 includes two simple queries and two different lists of options to choose from.
Step #1: Open the spreadsheet with the dropdown list
Open your MS Excel spreadsheet containing dropdown lists.
Step #2: Select the dropdown
- Select the cell with the dropdown list. In our example, it is Cell C2.
- In the main menu bar, click on the Data tab.
- Click Data Validation in the Data Tools group.
- The Data Validation dialog box opens.
- Click Clear All.
- Click the OK button.
- Excel removes the dropdown list.
- After removing the dropdown, the last selected data in the cell remains intact.
Use this method to keep the last selected values in your worksheet.
Method # 2: Use the Clear All Button
Use this method to remove one or more dropdown lists from your spreadsheet, as well as the selected entry. We will use the same example spreadsheet for this method.
Step #1: Open the spreadsheet with the dropdown list
Open your MS Excel spreadsheet containing dropdown lists.
Step #2: Select the dropdown list
- Select the cells with dropdown lists. In our example, it is C2 and C4.
- In the main menu bar, click on the Home tab.
- In the Home ribbon, select the Clear icon.
Step #3: Delete the selected dropdowns
- From the dropdown menu that opens, click Clear All.
- Excel removed the dropdown lists from C2 and C4 cells.
This method deletes the content, format, and data validation rules. Use the technique cautiously, as it does not keep the last selected value.
Method #3: Use Copy and Paste Option
This method is simple to use, but use it sparingly as it overwrites the cell formats and contents.
Step #1: Open the spreadsheet with the active dropdown list
- Open your MS Excel spreadsheet containing dropdown lists.
- Select any blank cell on your spreadsheet.
- Press CTRL+C to copy the blank cell.
Step #2: Remove the dropdown list
- Select the cell with the dropdown list. In our example, it is C2.
- Press CTRL+V to copy the blank cell to C2.
- Pasting the blank cell removes the dropdown list, cell content, and cell format.
- Repeat the step to remove the dropdown in C4.
In the illustration above, pasting the blank cell replaced the formatting of the target cell.
When you copy-paste a cell, the formatting of the target cell is replaced by the formatting of the source cell.
Conclusion
The dropdown list is a data validation function to ease your data entry and selection. All three methods are easy to use. Whether you want to delete one or more dropdown lists from your workbook, you can do so with only a few clicks.