It’s also a useful tool when I’m sharing my files with other people, as I can restrict data entry in some cells and only allow the selection to be made from the drop-down. However, in some cases, you may have a need to remove the drop-down from your Excel file. This could be because you no longer need the drop-down, or you want to enter something else other than the options in the drop-down. In this tutorial, I’m going to show you how to quickly remove a drop-down in Excel. I will also cover how you can remove all the drop-downs in your excel file in one go.
Why Remove Drop-down Lists in Excel?
The biggest reason people want to remove a drop-down list in Excel is that they want to enter some other text apart from the one specified in the drop-down. If you have a drop-down list in a cell, and you try and enter something else, you will see an error message (as shown below). Another common reason could be when you want to redo the drop-down list, but first, get rid of all the existing ones in the worksheet
Remove Specific Drop-down Lists in Excel
Let me first show you how to remove a specific drop-down list in the worksheet. For this method to work, you will need to select the drop-downs (all the cells that contain multiple drop-downs) that you want to delete. I will also show you how to remove all the drop-downs in the worksheet in the next section
Using the Data Validation Dialog Box
Below I have the Employee name data set in column A, and I have created drop-down lists in the adjacent cell (column B) that would show the department names. From this data, I want to delete all the drop-downs in column B. Below are the steps to remove the drop-down from column B using the data validation dialog box. The above steps would clear all the data validation rules from the selected cells, and since a drop-down list is also a type of data validation rule, these would be cleared as well. Note that in case you had made selections earlier using the drop-down lists, that value would still remain in the cell. Only the drop-down list would be removed Pro Tip: You can use the following keyboard shortcut to open the data validation dialog box – ALT + A + V + V (press these keys one after the other)
Using the Clear All Button
In the above method, we only remove the drop-down list from the cells. The values in the cell (if there were) and the formatting remained as is. In case you want to remove everything from the selected cells (including the drop-down lists as well as the values and formatting), you don’t need to go through the data validation route. Instead, you can use the ‘Clear All’ option available in the ribbon. Below I have a data set where I have the employee names in column A and the drop-down list in column B (where the drop-down list shows the department names for the employees). Below are the steps to remove the drop downs in the cells using the ‘Clear All’ method: The above steps would remove everything from the cells (including the drop-down list, any values in the cells, as well as any formatting applied to the cells). This method is more suited in situations where you want to start afresh and want to get rid of everything that is already there in the cells. Pro Tip: You can also use the following shortcut to clear everything from the selected cells – ALT + H + E + A (press these keys one after the other)
Using Copy-Paste Method
Excel treats drop-down lists just like cell formatting. This allows you to copy and paste a drop-down list using a simple copy-paste operation. And vice versa, if you copy a cell that does not have a drop-down list and paste it over a cell that has it, the drop-down list would be removed (as the formatting and the data validation rules of the copied cells are applied to the destination cell) Below I have this data set where I have drop-down lists in column B, that I want to remove. Here are the steps to delete the drop-down list by using a simple copy-paste method: The above steps would remove the drop-down list from the selected cells. One drawback of this method is that it removes all the formatting in the cells and copies the formatting from the cell that we copied in step 2.
Remove All Drop-Down Lists in the Worksheet
In the above section, I showed you three methods to remove the drop-down list from the selected cells. For those methods to work, you have to know where these drop-down lists are, and then select those cells first before removing them. In this section, I will show you how you can remove all the drop-down lists in the worksheet without manually selecting each cell that has it. And there are two scenarios where you can use this method:
Based on the Same List
Below I have a data set where I have drop-down lists in two columns (columns B and E), which have been created using the same source list (so all the cells show the same data in the drop-down menu) And I want to remove both of these drop-down lists. Here are the steps to remove all the drop-down lists in one go: The above steps will select all the cells that have the drop-down list that uses the same source list of the cell that we selected in step 1. Now that we have selected all the cells that have the drop-down list, here are the steps to remove that drop-down list. The above steps would remove all the drop-down menu that is based on the same list as the one in the cell that was selected in Step 1.
Based on the Different Lists
In case you want to remove all the drop-down lists in the worksheet in one go (irrespective of the source list), you can use the steps in this section. The above steps would remove all the drop-down lists in your worksheet. The good thing about this method is that you do not need to know that all cells have drop-down menus in them. The ‘Go To Special’ option does that for you. Caution: One important thing to remember is that the above steps would remove all types of data validation rules from the worksheet. While a drop-down list is the most used data validation option, a lot of people also use data validation rules that that does not involve creating a drop-down menu (such as restricting data entry between two numbers or two dates). The above steps would select any cell that has any data validation rule applied to it (not just the drop-down lists)
Keep the Drop Down List But Allows All Entries (No Error Message)
In some cases, users are ok with keeping the drop-down lists as long as the cells allow entering the data manually as well. For example, if I have a drop-down list that shows department names for employees, and for one employee I want to enter ‘NA’ or ‘Not Assigned’, I won’t be able to do it as it is not a part of the drop-down list. But what if there was a way to allow you to enter values that are not part of the drop-down list, and keep the drop-down list at the same time? Let me show you how to do this. Below I have data set where I have drop-down lists in column B, and I want to keep the drop-down list as well as be able to enter data manually in these cells. Here are the steps to do this: After you’re done with the above steps, you will be able to enter any data in the cell without triggering the error message prompt. And at the same time, you can also use the options in the drop-down list to do the data entry (so the best of both worlds). In this tutorial, I showed you multiple ways to remove drop-down lists from cells in Excel. If you want to remove it from specific cells, you can use the inbuilt clear all option in the data validation dialog box, or you can use the ‘Clear All’ option that removes Everything including the drop-down lists. I’ve also covered how you can remove all the drop-down lists from a worksheet (based on the same list or irrespective of the source lists) I hope you found this Excel tutorial useful. Other Excel tutorials you may also like:
Drop Down Lists To Show Numbers Between Two Specified Numbers How to Make a Yes/No Drop-Down in Excel? Show Symbols in Drop Down Lists in Excel Creating Multiple Drop-down Lists in Excel without Repetition Display Main and Subcategory in Drop Down List in Excel How to Make Multiple Selections in a Drop Down List in Excel Creating a Searchable Drop Down list in Excel How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells) How to Create a Dependent Drop Down List in Excel
