This guide will explain how you can remove a drop-down list in Excel.
Since drop-down lists are a type of data validation, some users may find it difficult at first to remove the drop-down list from a spreadsheet. We will explore a few ways you can use to clear out the data validation.
Drop-down lists are one of many ways you can validate user input in Excel. Cells with this type of data validation are only permitted to have values from a certain set of values.
If you try to remove the value of a cell with a drop-down list, you will notice that the drop-down arrow is still present beside the cell.
If you try to add any value other than the approved list of values, Excel will return an error pop-up indicating that the sheet has data validation.
When making changes to your tables, it can be frustrating to have these drop-down lists still even after you’ve tried to clear the range. Removing drop-down lists in Excel requires you to clear the data validation of the actual cell.
Excel offers a few ways to remove data validation. First, users can open up the Data Validation dialog box to clear validation. Since this method retains any selected values, the dialog box method is best used if you don’t want to lose any values.
For example, let’s say we have a cell with a drop-down list with the choices A, B, and C. If the user chooses the value B from the drop-down list and removes data validation through the Data Validation dialog box, the value of B will still remain in the cell.
Users can also use the generic Clear All command. This command removed data validation as well as the contents and formatting of the target cell.
Now that we know when to remove drop-down lists in Excel, let’s go into how it works on an actual spreadsheet.
A Real Example of Removing Drop-down List in Excel
Let’s take a look at a real example of a spreadsheet that shows the result of removing a drop-down list from a table in Excel.
The first table below is an inventory with three fields. Data validation has been added to the Size field so that the user must choose a value from a predetermined list of options. In the second table, we have the same data with the data validation removed. The cells in C4:C15 no longer produce a drop-down list when clicked.
To get the values in Column C, we just used the Data Validation tool to clear out the existing data validation.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try removing drop-down lists in Excel, follow our guide in the next section!
How to Remove Drop-down List in Excel
This section will guide you through each step needed to remove drop-down lists in Excel.. You’ll learn how to clear data validation with two methods. First, we will use the Data Validation dialog box to clear only the data validation from the cell. Second, we’ll show how you can use the Clear All option to clear a cell or cell range fully..
Follow these steps to start removing the drop-down list in your spreadsheet:
- First, select the cell or cell range that has the drop-down list. In this example, we’ll remove the drop-down lists in the range C2:C13.
- Next, navigate to the Data tab and click on the Data Validation option.
- In the Data Validation dialog box, click on the Clear All option found in the lower-left corner.
- Your selected cells should now be stripped of any data validation.
- Alternatively, we can use the Clear All option directly. This option removes all values, formatting, and data validation from the selection. To use this method, first select the cell range you want to clear. In this example, we will select the range C2:C13.
- In the Home tab, click on the Eraser icon. In the drop-down menu, select the Clear All option.
- After using the Clear All option, Excel will remove the drop-down list data validation from the selected cells.
- One last method you can use to remove the drop-down list in your spreadsheet is to copy a blank cell over a cell with the drop-down list.
Frequently Asked Questions (FAQ)
- How do I select all cells in my sheet with data validation?
To select all drop-down lists in your spreadsheet, you can use the Go To Special tool. In the Go To Special dialog box, you can select the option to highlight all cells with data validation. You can find the tool by navigating to Home > Editing > Find & Select > Go to Special.
This step-by-step guide should be all you need to remove drop-down lists in Excel yourself. Our guide shows how to easily clear data validation for cells with drop-down formatting.
Clearing data validation from cells is one way you can modify an existing spreadsheet in Excel. With so many other Excel functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us.
