How to Easily Remove Drop Down Lists in Excel

Working with Excel spreadsheets often involves using data validation to create handy drop down lists These make data entry quicker and more consistent

But sometimes you may want to remove a drop down list and free up the cell to accept any values again. Deleting drop downs in Excel is easy and only takes a few clicks.

In this step-by-step guide, I’ll show you how to quickly remove drop down lists from cells in Excel for both Windows and Mac. We’ll cover

• What Excel drop down lists are
• Why you might want to delete a drop down list
• How to remove drop downs from single or multiple cells
• Deleting all data validation from a worksheet

By the end of this tutorial, you’ll know the quickest ways to remove annoying drop down lists in Excel. Let’s get started!

What Are Excel Drop Down Lists?

Before learning how to remove them, let’s briefly go over what drop down lists in Excel are.

A drop down list provides a list of pre-set choices that users can choose from when entering data into a cell. Instead of allowing any values to be typed in, the cell shows a small arrow which, when clicked, displays a list to pick from.

These list choices are set through Excel’s data validation feature. Data validation restricts what can be entered into a cell, which helps prevent invalid data from being added.

Drop downs make it easy to standardize data entry. For example, you could create a list of product categories or valid customer IDs. By using a drop down, you can ensure data is entered consistently.

Why Remove Excel Drop Down Lists?

Drop down lists are very handy in some situations. But sometimes, you may want to delete them and go back to allowing any values to be entered.

Here are some common reasons you might need to remove a drop down list in Excel:

• The list choices are no longer valid or needed
• You want to allow any values to be entered rather than restricting inputs
• The drop down is not being used anymore
• You want to edit or update the list choices
• The drop down list is causing errors or issues
• You want to delete all data validation before copying or pasting cells

Deleting drop downs takes just seconds, so it’s easy to remove them when needed.

How to Remove a Drop Down List From a Single Cell

If you only need to remove the drop down list from one cell, it’s extremely quick and easy:

1. Select the cell that contains the drop down list.

2. Click on the Data tab in the ribbon.

3. In the Data Tools group, click Data Validation.

4. This opens the Data Validation dialog box. On the Settings tab, click the Clear All button.

5. Click OK to close the dialog box and delete the drop down list.

The cell will now allow any values to be entered again instead of displaying the drop down list.

And that’s all there is to it! Within a few clicks, you can remove pesky drop down lists from individual cells.

Removing Drop Downs from Multiple Cells

The process is just as easy if you need to delete drop down lists from several cells at once:

1. Select all the cells that contain a drop down list. You can select cells individually or use Ctrl + left click to select multiple cells.

2. On the Data tab, click Data Validation.

3. Click Clear All and then OK.

This will clear the data validation from all selected cells at once, removing their drop down lists instantly.

How to Delete All Data Validation on a Worksheet

You can also delete all data validation rules from an entire worksheet in one go. This is useful if you want to remove every drop down list from the sheet.

Follow these steps to delete all data validation on a worksheet:

1. Select any cell on the worksheet.

2. On the Data tab, click Data Validation.

3. Click the Clear All button, then click OK.

4. When prompted to confirm clearing everything on the sheet, click OK.

This will delete every single drop down list and other data validation rules all at once from the entire worksheet. The sheet will go back to allowing any values to be entered in any cell.

Tips for Removing Drop Downs in Excel

Here are a couple handy tips for quickly deleting pesky drop down lists in Excel:

• To select all cells with data validation on a sheet, go to Home > Editing > Find & Select > Go To Special and choose Data validation. This will select every cell with a drop down for easy deletion.
• If you delete a drop down list by mistake, you can undo it immediately after. Go to theUndo button on the Quick Access Toolbar or press Ctrl + Z.
• To modify a drop down list rather than delete it entirely, edit the source data range or update the list choices in the Data Validation dialog box.
• Make sure to save your workbook after removing drop downs in case you need to revert back.
• Test that unwanted drop downs are gone by trying to enter any value into the cells. The arrow indicator will no longer display.

It’s easy to remove pesky drop down lists in Excel with just a few clicks. By clearing the data validation, you can go back to free data entry in cells.

To quickly recap, you can delete a single drop down, multiple drop downs, or all data validation rules from an entire sheet.

Next time you need to get rid of Excel drop down lists, use one of the simple methods in this guide. And if you decide you want those handy data validation drop downs back, you can always reapply them.

Removing drop down lists gives you flexibility when your data validation needs change. Use these steps to effortlessly clear them from your worksheets.