A drop-down list in Excel helps you input data correctly and properly from a list of pre-defined inputs. It works as a data validation check and avoids any chance of inputting invalid data. As it restricts the values that can be entered into a cell, it is one of the most used functions of Excel.
A drop-down list carries a list of multiple inputs and makes data entry time-saving and error-free. However, you can remove the drop-down list when you have finished data entry or if you do not want to limit the user to a small set of values.
Apart from the benefits that a drop-down list has to offer, it takes a fine deal of effort to remove a drop-down list from Excel.
Removing any existing drop-down list or multiple drop-down lists from your Excel spreadsheet is not as easy as just selecting the cell and pressing the Delete key.
To know the exact steps involved in deleting a drop-down list from Excel, continue reading the article that follows.
There are three ways how you can remove a drop-down list in Excel – let’s look into each of them.
Table of Contents
1. Data Validation
This is the most commonly used method of removing drop-down lists from Excel. Take a look at the example below.
The above example demonstrates the sales record of a store, displaying various brands of mobile phones, along with their prices. Every phone brand has its unique product ID.
Column F represents the payment methods used for selling these mobile phones. The column ‘Payment Type’ contains a drop-down list of various options used to receive payments against mobile phone sales.
If you want to remove the drop-down list in cell F4, the Data Validation dialogue box is the best option. To learn how you can remove the said drop-down list using the data validation technique, follow the steps below.
Step 1
Select the cell containing the drop-down list, which is F4 in this case.
Step 2
Go to the Data tab in the Ribbon and select Data Validation from the drop-down menu that appears.
Step 3
Select the “Clear All” button and press the “Ok” button. Make sure to have selected the “List” option under the “Allow” tab.
This removes the drop-down list from cell F4. Also, the arrow at the right side of cell F4 has been removed, as shown in the following image.
Pro Tip: This method removes the drop-down list from any cell but keeps the last values selected from the list within the cell. So, if you have used the drop-down list for easy data entry and later want to remove the list, this option would work best for you. Using Data Validation, you can remove the drop-down list without having to lose the cell values.
How to remove the drop-down list from multiple cells all at once?
If you want to remove multiple drop-down lists simultaneously, you can still do it by using Data Validation.
Select the cells from where you want to remove the drop-down list and repeat the above-mentioned steps.
Excel would remove drop-down lists from all the selected cells while preserving the cell values.
How to remove all drop-down lists from a spreadsheet at once?
If you want to remove all the drop-down lists from your worksheet, the process remains the same.
All you need to do is check the “Apply these changes to all other cells with the same settings” option from the Data Validation window, as shown below:
Selecting the “Apply these changes to all other cells with the same settings” option automatically selects every cell in the worksheet containing the specified drop-down list.
Thereafter, clicking “Clear all” and then the “OK” button will remove all those drop-down lists without you having to lose the cell values.
2. The Clear All Button
This method is helpful if you want to remove the cell’s content along with the drop-down list.
Step 1
Select a single cell or multiple cells from where you want to have the drop-down lists, as well as the cell values, removed.
Step 2
To delete drop-down lists from your worksheet in Excel using the ‘Clear All’ option, take the route established below.
Home Tab > Ribbon > Clear > Clear All
The ‘Clear All’ option clears everything from the selected cells – this includes the values, the formatting, the drop-down lists, and everything else.
3. Copy-Paste Method
Another quick way to remove drop-down lists in Excel is to copy a blank cell from your worksheet and paste it over the cell or the range of cells that have the drop-down list.
Since you’ve pasted an empty cell, the target cell will turn empty and will not contain any drop-down list or other value.
Conclusion
The drop-down list acts as the easiest data validation check when inputting large volumes of data. Adding drop-down lists to Excel is super easy. However, once added, it might be a little tricky to get rid of them.
The above techniques will help you steer your spreadsheet clear of any unwanted drop-down lists. Hope this helped you learn what you’ve been looking for.
Happy Excel Exploring!