More times than I could think of in my life I have been asked a question from one of my business colleagues on how to remove or type something else in an Excel cell that has limited options- options being a range of cells, a list of options, or anything else.
Typically, these “problematic” cells are formed as a so-called dropdown list, so the problem can be easily solved by removing this list. We are going to show you how to do it.
Create a Dropdown List
To understand how to remove a Dropdown list in Excel, we first have to understand how to create one.
Let’s assume that we have a cell A1 into which we type simply Name. In cell B1 we want to limit our options for the name and to choose from Jessica, Hank, or Mark (a random list of names).
To do this, we have to type these names into the computer’s memory. For this example, we will type them into column K (into cells K1, K2, and K3).
Then we go to cell B1, and then into the Data tab located on our ribbon. On the Data tab, we can find Data Tools. The most popular data tool is Data Validation, which we need for this example.
When we select the dropdown list of Data Validation (picture below), we choose Data Validation again:
Data Validation is a feature in Excel used to control what a user can enter into a cell.
When we click on Data Validation, we will get a pop-up window as in the picture below:
Not to go into too much detail about the meaning of all the tabs, we will just say that there are several validation criteria in the Settings tab: any value, whole number, decimal, list, date, time, text length, and custom.
This means that we can limit the possible input of our cell on anything we want.
In our example, the easiest way is to choose the option List and then to select our range (K1:K3), as in the picture below.
To make our example more neatly, we can hide column K, which stores our data for Data Validation. To do this, we just have to select the column, right-click on it and then choose the second to last option, which is Hide:
We can notice that our cell B1 now has a dropdown menu of our range to choose from.
We can either choose or type these options into cell B1. If the user tries to enter anything other than these options, a generic warning message will appear:
We can either choose Retry to try again or Cancel to move from this cell.
Remove Dropdown List in Excel
Once you know how to create a dropdown list in Excel, it is not so hard to learn how to remove it.
Dropdown lists usually have a downward arrow in the cell that contains them.
The first step when removing the dropdown list is to select the cell with the dropdown list. If you have multiple cells with dropdown lists that you want to delete, you can use the Ctrl+Left click to select them.
Then you go to the Data Tab– Data– Data Validation. On the Settings tab (where we defined our values above) you just click on Clear All.