How to Remove Dropdown in Excel? 3 Easy Methods
(Note: This guide on how to remove dropdown in Excel is suitable for all Excel versions including Office 365)
While using Excel, manually entering data into each cell can be a bit arduous. Especially when the data you enter is similar, typing them may feel a little redundant.
In such cases, adding a dropdown to the cells in Excel helps you to enter the data easily. This method of adding data as dropdown in Excel cells is called Data Validation.
But sometimes, using the data validation method and dropdown to add values in Excel has some disadvantages. In this article, I will tell you why and how to remove dropdown in Excel using 3 easy ways.
You’ll Learn:
Related Reads:
How to Create Excel Drop Down List With Color?
How to Switch Between Sheets in Excel? 6 Useful Methods
How to Add Dynamic Dropdown lists in Excel — 4 Easy Steps
Why Remove Dropdown in Excel?
A dropdown list consists of a list of values that you can click and select to populate the cell.
The main drawback of using a dropdown list to enter data is that you cannot enter any data other than the values in the dropdown list.
When you enter any data manually, Excel either throws a pop-up saying “this value doesn’t match the data validation restrictions defined for this cell”. In some cases, Excel accepts the data but shows a flag in the corner of the cell.
If you are not satisfied with the dropdown list, you can modify them or even remove them completely. Let us see how.
How to Remove Dropdown in Excel?
There are a couple of use cases that have different ways to remove dropdown from Excel sheets.
Using the Data Validation Method
Data Validation is an effective method to create dropdown lists in Excel. Using the same option, you can clear or remove the dropdowns created before.
There are multiple ways and cases you can use to remove dropdowns in Excel.
Remove a Particular Dropdown List
If you are not satisfied with any data column being a dropdown list, you can choose to remove any specific columns with dropdowns. Look at the above-mentioned example. The columns Test Center, Grade, and Result all have a dropdown list to choose from. Suppose you want to remove the dropdowns in the Test Center column, you can remove them using the Data Validation option.
- First, select all the cells with the dropdown you want to remove. In this case, we want to remove the dropdown from the Test center column.
- Navigate to Data. Under the Data Tools section, click on Data Validation.
- If you have selected any other cells which do not have the Data Validation, Excel throws a popup saying “The selection contains some cells without Data Validation settings. Do you want to extend data validation to these cells?”. Click Yes.
- This opens the Data Validation dialog box. Under the Settings tab, click on the Clear All button.
- Once you click on Clear All, the data validation inputs and settings will be cleared. Now, click OK.
Thus the data validation inputs and the dropdown for the selected cells have been removed.
Note: If you want to remove the data validation from all the cells and columns, select all the cells by clicking on the select all triangle at the top or by pressing Ctrl + A. Now, navigate to Data > Data Validation and select Clear All.
Remove an Option from the Dropdown List
The above Clear All option in the Data Validation dialog box will delete all the dropdowns from the selected cells. However, if you want to remove any particular option from the dropdown, you don’t have to remove the whole dropdown. You can just remove it from the same Data Validation dialog box.
- First, select all the cells which have the particular data validation.
- Then, navigate to Data. Under the Data Tools section, click on Data Validation.
- This opens the Data Validation dialog box.
- In the Source text box, delete the particular option/data. Also, check the checkbox for Apply these changes to all the other cells with the same settings, and click OK.
- This removes the particular data from the dropdown.
Note: If you have linked the data to the dropdown from another sheet, you can easily navigate to the sheet and delete that particular option.
Suggested Reads:
How to Remove Dotted Lines in Excel? 3 Different Cases
How to Remove Comma in Excel? 5 Easy Ways
How to Remove Spaces in Excel? 3 Easy Methods
Using the Clear Button
This is a very effective and quick way to remove dropdown in Excel. When you delete any data in the data validated cell, the data in the cell will be removed but the dropdown remains. When you use the Clear button, it removes all the data in the cell and also all the formatting applied to the cell.
- To remove the data validation, first, select the cells you want to clear.
- Navigate to Home. Under the Editing section, click on the dropdown from Clear and select Clear All.
- This removes the Data Validation from the selected cells and also the data in the cells.
Note: As the Clear button removes all the data and format of the cells, you can use this option only when it’s indispensable. If you want to retain the data and just remove the dropdown, you can use the above-mentioned method.
Using the Copy and Paste Option
This is also another easy and effective method to remove dropdown in Excel. This method works on the principle that copying and pasting a cell with particular formatting changes the format of the cell where it is pasted. This way, you can copy a blank cell and paste it into a cell that has a dropdown list and remove it.
- The most important step is to select an equal number of cells as the number of cells you want to replace. If not, only the number of cells copied will be replaced and the rest of the cells will be left off.
- Now, select the cells you want to remove dropdowns from.
- Either, right-click on the starting cell and select Paste or press Ctrl + V.
Thus the selected cells will have the formatting of the copied cell and Excel removes the data along with the dropdowns.
Also Read:
How to Remove Hyperlinks in Excel? 3 Easy Methods
How to Split Cells Diagonally in Excel? 2 Easy Ways
How to Enable Full Screen in Excel? 3 Simple Ways
Frequently Asked Questions
What is the easiest and fastest way to remove the content and dropdown in Excel?
Using the Clear button is the easiest and fastest way to remove the content and dropdown in Excel. First, select the cells. Navigate to Home, under the Editing section, click on the dropdown from Clear and select Clear All. This will clear the data and the data validation in the particular cells.
Can we remove specific data from the Excel dropdown?
Yes, we can remove certain data from the Excel drop-down and not the whole dropdown. Select the cells. Go to Data and select the Data Validation option. In the Source text box, delete the data and click OK.
How to remove the dropdown in Excel easily?
Select the data and click on Data Validation in the option in the Data ribbon. In the dialog box, click on Clear All and click OK. This removes the dropdown from the cells in Excel.
Closing Thoughts
Removing the data validation dropdowns in Excel helps you to easily enter data into cells, especially when they are not listed in any of the dropdowns.
In this article, we saw how to remove dropdown in Excel using 3 easy ways. You can either choose to remove just the data validations or the data from the cells. Choose the method that suits your purpose best.
If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!