How to remove dropdown in Excel?
A dropdown list is inserted in an Excel column to restrict the user to input the data manually in its below cells. The Excel user can choose one of the values from the dropdown list to insert the data in cell. The users are not able to provide value outside the dropdown list options.
By creating the dropdown list, users cannot insert invalid input to the cell as he/she only have to choose from the dropdown list. Hence, you can say that the dropdown list validates the input in this way. All the time, you no longer require the dropdown in your Excel sheet. You will learn how you can remove the dropdown from the Excel sheet.
What is a dropdown list?
A dropdown list is a list of predefined options created by an Excel user for a column. When a dropdown list is created for a column in a sheet, Excel can choose one of the options from it. They cannot provide the data by entering the value in that column’s cell.
You can say it as a validation tool of Excel using which you can restrict the other users to enter invalid input. You can remove the dropdown list and also modify the options in the dropdown list. If you no longer need a dropdown list in an Excel sheet.
An example of dropdown list
An example for the dropdown list can be gender or month’s name dropdown list. A dropdown list comes in data validation type in MS Excel. By applying this on a column, users are not allowed to enter the value manually. They have to only select one of the values from it.
Remove a specific dropdown list
An Excel sheet can have multiple dropdown lists, but you want to delete one dropdown list instead of all. You can remove a specific dropdown list from an Excel sheet. We have a dropdown list named “Gender” and “Joining Month”. But our target for this example is Gender dropdown list that we will try to remove.
These are the steps to remove a dropdown list from an Excel sheet:
Step 1: Select a cell of the column having a dropdown list and move to the Data tab in the Excel menu bar.
Step 2: In the Data tab, click the Data Validation inside the Data Tools section.
Step 3: You may get this popup with a message like that some of the cells do not contain this data validation setting. So, click Yes on this popup.
Step 4: A data validation panel will open where click the Clear All button inside the Settings tab and click OK.
Step 5: The dropdown data has been cleared. No validation has remained on column C. Now, click OK on this dialogue box to save changes.
Step 6: You will be back on your Excel sheet where you see that the dropdown list has been removed.
Remove all dropdown list
An Excel sheet can have multiple dropdown lists. You can remove all those dropdown lists from an Excel sheet in one go. It is natural that a sheet may not have too many dropdown lists. So, you can remove them one by one. But it will be a good practice to remove all dropdown lists at once.
These are the steps to remove a dropdown list from an Excel sheet:
Step 1: Select the entire sheet using the triangle button between the row and column header to remove all the dropdown lists available in this currently opened sheet.
For your information, we tell you that we have two dropdown lists named Gender and Month in this sheet.
Step 2: Now, move to the Data tab in the Excel menu bar and click the Data Validation.
Step 3: A small popup will open on which click OK to erase the current setting and continue.
Step 4: You will navigate to the data validation panel where do nothing and just click OK.
Step 4: You will be back on your Excel sheet where you see that all the dropdown lists have been removed at once.
Why use dropdown list in Excel?
Excel enables its users to provide input in a sheet by entering the data manually in cells or from an external file by importing data to it and through a dropdown list. The user can add a dropdown list to a column to restrict the user from entering an incorrect value to the cell. Basically, you can say that it is a type of data validation tool in Excel.
A dropdown list is created to take accurate input from the users. You provide options to choose an input value by creating a dropdown list. It can be created for the data whose input values you want to fix. So, after placing a dropdown list, the users are not able to enter an input value outside the options provided in the dropdown list.
“The main benefit of creating a dropdown list is to take accurate input from the user when the input values are fixed.” So, it comes in the data validation category. A dropdown list can be used in such cases like – Gender, Month, Season.
Delete an option from dropdown list
If you have provided a wrong option or want to remove any option from a dropdown list, you do not need to delete the entire dropdown list. You can modify the existing dropdown list and remove and add options to the list.
Here, we have some easy steps using which you can delete an option from an existing dropdown list inside an Excel sheet.
Step 1: Suppose we have a dropdown list named Season having five options (Summer, Winter, Rainy, Spring, and Autumn).
We want to remove the Autumn season from the list here.
Step 2: Select the column or cell having a dropdown list and move to the Data tab and click Data Validation.
Step 3: Remove the option from the Source field, whichever you want to delete using the backspace key on your keyboard.
Step 4: You have seen that Autumn has been removed from the source. Now, click the checkbox Apply these changes to all other cells with the same settings, and click OK.
Step 5: Now, click on of the dropdown cells and see that Autumn is deleted from the dropdown list.
Using the same way, the users can add more options to a dropdown list by following the same steps. You have to provide a new option in the source field instead of removing one by separating it with a comma.
