Remove a Data Validation List in Excel: Steps and Fixes

How-to basics · Updated November 20, 2026

To remove a data validation list in Excel for Microsoft 365 on Windows, select the cell or range, choose Data > Data Validation, click Clear All, then click OK. This removes the validation rule without deleting the cells’ existing values. If you only want to hide the dropdown arrow, use the separate In-cell dropdown option instead.

Remove a list from selected cells

Data validation is a rule attached to a cell. For a list rule, Excel can show a dropdown of permitted entries and reject values that do not meet the rule. To remove that rule from particular cells:

  1. Select the cell or range you want to change. Drag across adjacent cells, or hold Ctrl while selecting separate cells on Windows.
  2. Open the Data tab and choose Data Validation in the Data Tools group.
  3. On the Settings tab of the Data Validation dialog, click Clear All.
  4. Click OK to apply the change.

The validation rule, its input message, and its error alert are removed from the selected cells. Their current contents remain in place. For example, if B2 contains the allowed value “North,” removing validation leaves “North” in B2; it does not empty the cell.

To confirm the change, select one of the cells and reopen Data Validation. Excel may show a message that the selection contains more than one kind of validation if the cells had different rules. In that case, clear validation in smaller groups or select the cells that share the same rule.

Remove validation across a worksheet

When a workbook has many dropdowns, finding them one by one is slow. Go To Special can select cells that contain data validation so you can inspect or clear them together.

  1. First, click a cell in the worksheet. If you want to search the entire sheet, click the Select All button at the intersection of the row and column headings.
  2. Choose Home > Find & Select > Go To Special.
  3. Select Data Validation. Choose All to find cells with any validation rule, or Same to find cells whose rule matches the active cell.
  4. Click OK. With the matching cells selected, choose Data > Data Validation, click Clear All, and click OK.

Go To Special works on the current worksheet selection; it does not search every sheet in the workbook at once. Repeat the process on each sheet where you want to remove rules. If you intend to clear every rule, take care not to remove useful controls from forms, entry sheets, or shared templates. A list may be there to prevent inconsistent entries, not merely to display a convenient menu.

Hide the dropdown without removing the rule

Sometimes the goal is to remove the arrow, not the restriction. Select the cell, open Data > Data Validation, and on the Settings tab clear the In-cell dropdown checkbox. Click OK. The list rule remains active, but the arrow no longer appears in the cell. Depending on the rule’s error-alert settings, Excel can still reject an entry that is not in the allowed list.

The arrow is normally visible when a validated cell is selected, rather than permanently displayed in every cell. So an arrow that seems to be missing may not mean the rule is gone. Check the Data Validation dialog to see whether the cell has a list rule and whether In-cell dropdown is enabled. This distinction matters when a user can type a permitted value but no longer has the menu to choose it.

What changes when you clear a validation rule

Clearing validation changes what Excel allows people to enter in the selected cells. It does not automatically remove existing data, formulas, formatting, or the source list elsewhere in the workbook. After the rule is removed, a user can enter a value that would previously have been rejected, subject to other restrictions such as worksheet protection.

Data validation is also different from a cell’s contents. Pressing Delete, or choosing Home > Clear > Clear Contents, removes the selected cell’s value or formula but leaves its validation rule. If you later type into that cell, the old dropdown or entry restriction can return. Conversely, Data Validation’s Clear All button removes the rule while preserving the current value.

Do not confuse Clear All in the Data Validation dialog with Home > Clear > Clear All. The dialog button clears validation settings for the selected cells. The Home ribbon’s Clear All command clears a much wider set of cell features, which can include contents and formatting. Use the dialog when the intention is specifically to remove the list rule and retain the rest of the cell.

Check the source before deleting related lists

A dropdown’s choices may come from values typed directly into the Source box, a range of cells, or a defined name that refers to a range. For example, a department dropdown might use a list on a separate “Lookups” worksheet. Removing validation from the input cells does not delete that source range or named range. That is usually helpful if other cells or worksheets use the same choices.

If the dropdown is not working as expected, inspect its source before removing anything. Select a cell with the list, open Data > Data Validation, and look at the Source field on the Settings tab. A source reference can reveal whether the list is tied to a range or a defined name. If the source range is broken, correcting it may be better than removing the rule. If the list is no longer needed anywhere, check other worksheets and formulas before deleting its source cells or name.

For a dependent dropdown, one selection can determine the choices available in another cell. Clearing the second cell’s validation removes that dependency for the selected cell, but it does not necessarily remove the first dropdown or the source data. When a workbook uses interconnected entry rules, identify which cells depend on each other before making a broad change.

Common problems and fixes

  • The dropdown is still there after pressing Delete. Delete clears cell contents, not validation. Reopen Data Validation and use Clear All.
  • The arrow is gone, but Excel still rejects an entry. The rule may remain with In-cell dropdown turned off. Clear the rule with the dialog’s Clear All button if you want to allow unrestricted entries.
  • The existing value remains after the rule is removed. That is expected. Clear the cell contents separately only if you also intend to delete the value.
  • You cannot open or change Data Validation. The worksheet may be protected. If you are authorized to edit it, choose Review > Unprotect Sheet. A password may be required; ask the workbook owner if you do not have it.
  • Only some cells changed. Check the selected range and repeat the process on the remaining cells. Go To Special can help locate validation on the current sheet.
  • The rule seems to return later. Another user, a copied cell, or a workbook template may be restoring it. Check how the file is updated and whether the edited range is the one used for data entry.

Removing a validation rule does not repair invalid values that were already present, nor does it automatically identify which entries violate the former list. If you need to preserve data quality, review the affected entries before removing the rule or use another check, such as a filter or a separate review column.

Windows, Mac, and Excel for the web

In Excel for Microsoft 365 on Windows, the standard route is Data > Data Validation, then Clear All in the dialog. The Go To Special route is Home > Find & Select > Go To Special > Data Validation. Keyboard access can vary with ribbon settings, so the menu paths are the dependable instructions.

In Excel for Microsoft 365 for Mac, select the cells and open Data > Data Validation, then use Clear All in the dialog if available. Menu placement and dialog appearance can differ from Windows, but the same distinction applies: clearing the validation rule is not the same as deleting cell contents. If you cannot find a command in your Mac build, use Excel’s command search or consult the workbook in the desktop version that supports the needed controls.

Excel for the web has data-validation controls, but available options and dialog layout can differ from the desktop application. Select the relevant cells and check the Data tab for Data Validation and a way to clear the rule. If the control you need is unavailable or the selection is complex, open the workbook in the desktop Excel app and use the steps above. Changes made in the desktop app can then be saved to the workbook as usual.

Choose the right kind of cleanup

Before making changes, decide whether you want to remove the restriction, remove the arrow, empty the cells, or delete the source choices. These are separate actions, and doing the wrong one can leave the problem in place or erase data you meant to keep.

  • Remove the validation rule: Data > Data Validation > Clear All. Use this when entries should no longer be restricted.
  • Hide the list arrow: Clear In-cell dropdown in the rule’s Settings. Use this when the rule should still check entries.
  • Delete existing values: Select the cells and clear their contents. This does not remove validation.
  • Remove the source list: Edit or delete the source range or defined name only after checking whether another rule uses it.

If other people rely on the workbook, explain which behavior changed and consider keeping a copy of the original file. A dropdown may be part of a data-entry process, and removing it can make later sorting, reporting, or matching less reliable if people begin entering different spellings for the same category.

Frequently Asked Questions

Does removing a dropdown delete the selected value?
No. Clearing validation leaves the cell’s current value or formula intact. Clear the contents separately if you also want the cell emptied.

Why does the arrow disappear but the list restriction remain?
The In-cell dropdown option controls whether the arrow is shown. It does not remove the validation rule. Use Clear All in the Data Validation dialog to remove the rule itself.

Can I remove all dropdown lists in a workbook at once?
Go To Special can select validated cells on the current worksheet, but it does not search all worksheets at once. Repeat the selection and clearing steps on each sheet, checking that you do not remove rules the workbook still needs.

Does clearing validation delete the list on another sheet?
No. It removes the rule from the selected cells, not the source values or defined name. Delete source data only after checking whether other dropdowns or formulas depend on it.

Can I remove a list from a protected sheet?
Usually you need permission to unprotect the worksheet before changing its validation. Choose Review > Unprotect Sheet; if Excel requests a password, contact the workbook owner rather than trying to bypass the protection.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.