The drop-down box is basically used for a powerful function in Excel. When you have multiple categories you cannot explain them one by one, therefore all categories are added in a drop-down box that includes data about each option.
Manually putting data in each cell becomes a tiring task and when your data is almost of the same kind it even becomes worst to type entries one by one. In situations like these, a drop-down list is used that lets you get rid of these arduous things.
It provides a huge benefit to the user by giving quick and easy data entry. However, sometimes you may need to remove this drop-down box because it limits the options and you don’t want to do it. So the question here is how to remove the drop-down box in Excel spreadsheet. Let’s see how it happens:
Why You Need to Remove a Drop-Down Box in Excel?
As already explained, a drop-down box in Excel gives many options to avail, but when it bounds users to use a small set of values, you may have to remove it from the sheet. When a drop-down list is added to the sheet, you cannot add more entries than a fixed figure. Sometimes, your data has values more than you think and a drop-down list restricts it to a limited number of options.
You can remove the list to get a wide range of responses from the users.
The Best Ways to Remove a Drop-down List in Excel:
- Remove a drop-down list with the Data Validation dialog box
- Remove a drop-down list with the Clear Button
- Remove a drop-down list with Copy-Pasting option
How to Remove Drop-down Box in Excel with Data Validation Dialog Box?
Here we have a dataset including Employee names in column A and in column B a drop-down list is added that contains department names.
Suppose, you need to remove all the drop-down boxes present in column B. For this, follow the steps given below:
Choose the cells that contain drop-down lists to be deleted.
Open the Data tab.
Choose the Data Validation option given in the Data Tools menu. You will see the Data Validation dialog box appears in front of you.
From the Data Validation dialog box, you must check the Settings tab.
Press OK.
You will notice the data validation rules are deleted from the cells you have selected.
All done!
How to Remove Drop-down Box in Excel with Copy and Paste Option?
We have another great method that assists in removing drop-down boxes in Excel. In this method, you will notice a special rule is followed copying and pasting a cell that has special formatting can modify the format of the cell where you paste it.
With this rule, you can simply copy an empty cell to paste it into a cell containing a dropdown list to remove it. In this method, you need to select an equal number of cells similar to the number of cells you need to replace.
Choose the cells that contain drop-down boxes to remove.
You may right-click on the first cell or else choose the Paste option. You can even press CTRL + V from the keyboard.
You will notice the selected cells have the formatting of the cells you copied and data is removed with dropdowns.
How to Remove Drop-Down List in Excel with Clear All Button?
With this method, you can remove the data from more than one cell all at once. The data includes a dropdown list as well. Remember that when you apply these steps, it will not only remove drop-down lists but all the data present in the cells will be removed.
Follow the steps given below while using the Clear All button:
- Choose the cells that have drop-down lists.
- Open the Home tab and choose the Clear option given under the Editing menu.
- You will see a list of Clear options. Choose Clear All from the list.
- You will notice all the data including drop-down lists are removed from the selected cells.
All Done!
Summary:
In this post, you have learned how to remove the drop-down box in Excel by following multiple methods. Try all of the above methods and you will come to know which method grabs your attention.
