AboutBlogMediaTags

How to Remove Data Validation in Excel (Step-by-Step)

Runcell Team,

How to Remove Data Validation in Excel

To remove data validation in Excel: select the cells → go to Data tab → click Data Validation → click Clear All → click OK. This removes all validation rules, drop-down lists, input messages, and error alerts from the selected cells.

This guide covers how to remove data validation from selected cells, entire sheets, and many workbooks at once — with step-by-step instructions and solutions for common issues.

Updated 2026-10-12: added a section on removing validation from many files with Python. That code was run with openpyxl 3.1.5 and its output is shown. The Excel menu steps were not re-run in Excel for this update.

We’ll cover:

What Is Data Validation in Excel? (Quick Recap)

Data validation in Excel controls what users can type into a cell. It can:

Removing data validation basically tells Excel:

“Stop enforcing rules on these cells. Let anything be entered.”

How to Remove Data Validation from Selected Cells

If you only want to clear validation from certain cells or ranges, follow these steps.

Step-by-step: Remove validation from a range

  1. Select the cells

    • Click a single cell, or
    • Click and drag to select a range, or
    • Hold Ctrl (or Cmd on Mac) and click multiple non-adjacent cells.

  2. Go to the Data tab on the Ribbon.

  3. Click Data Validation.

    • In some versions of Excel, you may need to click the small icon labeled Data Validation in the Data Tools group.

  4. In the Data Validation dialog box, go to the Settings tab.

  5. Click Clear All.

  6. Click OK.

✅ Result: Data validation rules are removed from the selected cells. Any drop-down arrows, input messages, and error alerts tied to those cells are gone.

Note: Removing validation does not change the existing values — it just removes future restrictions.

How to Remove All Data Validation from an Entire Sheet

If your sheet is full of old rules and you want to wipe them all in one go, you can.

Step-by-step: Clear all validation rules on a worksheet

  1. Click the Select All button:

    • This is the small gray triangle at the top-left corner of the grid (where row numbers and column letters meet), or
    • Press Ctrl + A twice (in most cases) to select the entire sheet.

  2. Go to the Data tab.

  3. Click Data Validation.

  4. In the Data Validation dialog box, click Clear All.

  5. Click OK.

✅ Result: Every data validation rule on that worksheet is removed—no more drop-down lists, restrictions, or error messages.

💡 Tip: Do this one sheet at a time if your workbook has multiple sheets.

How to Remove Drop-Down Lists in Excel

Drop-down lists are just one type of data validation. Removing them uses the same process.

Remove a specific drop-down list

  1. Select the cell(s) that show a drop-down arrow.
  2. Go to Data → Data Validation.
  3. In the Settings tab, click Clear All.
  4. Click OK.

The drop-down arrows disappear, and users can type any value.

Remove all drop-down lists on a sheet

  1. Select the entire sheet (Select All button or Ctrl + A twice).
  2. Go to Data → Data Validation.
  3. Click Clear All → OK.

All drop-down lists and other validation rules on that sheet are removed.

How to Find Cells That Have Data Validation

If you’re not sure where validation exists, Excel can help you locate it.

Step-by-step: Find cells with data validation

  1. Go to the Home tab.

  2. Click Find & Select (usually on the right side of the Ribbon).

  3. Choose Go To Special….

  4. In the dialog, select Data Validation.

  5. Choose:

    • All – to select all cells that contain any kind of validation, or
    • Same – to select only cells with the same type of validation as the active cell.

  6. Click OK.

Excel will highlight all cells with validation. From there, you can:

How to Turn Off “Circle Invalid Data”

Sometimes Excel marks invalid entries with red circles when data validation is enabled.

To clear those circles:

  1. Go to the Data tab.
  2. In the Data Tools group, click Data Validation (or its dropdown).
  3. Select Clear Validation Circles.

This does not remove validation rules—it only removes the red circles. To stop Excel from flagging those values entirely, you still need to remove the data validation rules as described above.

Common Issues When Removing Data Validation

1. “Clear All” only affects some cells

If Clear All doesn’t remove rules everywhere:

2. Existing data looks wrong after removing validation

Removing data validation doesn’t change existing cell values—but it might reveal problems:

If you’re cleaning up a file, consider:

3. I only want to remove the input message or error alert

You can keep restrictions but remove the messages:

  1. Select the validated cells.

  2. Go to Data → Data Validation.

  3. Use:

    • The Input Message tab: uncheck “Show input message when cell is selected.”
    • The Error Alert tab: uncheck “Show error alert after invalid data is entered.”

  4. Click OK.

Now the rule still exists, but users won’t see the pop-up messages.

Best Practices Before You Remove Data Validation

Before mass-removing validation rules, especially in shared or critical workbooks:

If your dataset is growing large or coming from many sources, relying only on Excel validation can become fragile. At that point, using dedicated data preparation or data quality tools is usually more reliable.

Remove Data Validation from Many Workbooks with Python

Clear All works one sheet at a time. If you have a folder of exported workbooks to clean, the Python library openpyxl can remove every rule from every sheet of a file. This was run with Python 3.14.2 and openpyxl 3.1.5 on two test workbooks. Each had four rules on two sheets: a drop-down list, a whole-number range and a custom formula on Orders, and a list on Lists:

import openpyxl
 
def strip_validation(path, out_path):
    wb = openpyxl.load_workbook(path)
    removed = {}
    for ws in wb.worksheets:
        removed[ws.title] = len(ws.data_validations.dataValidation)
        ws.data_validations.dataValidation = []
    wb.save(out_path)
    return removed
 
print(strip_validation("orders.xlsx", "orders-clean.xlsx"))
print(strip_validation("orders-x14.xlsx", "orders-x14-clean.xlsx"))

Output:

{'Orders': 3, 'Lists': 1}
{'Orders': 3, 'Lists': 1}

We then opened the saved .xlsx files as zip archives and counted the validation entries in each sheet’s XML: zero in both files. Every cell value was identical before and after, including a row whose values break the old rules (a quantity of 640 against a 1–500 limit). Removing validation does not touch existing data, and it does not flag it either.

The second file is the one to watch. Besides the four rules above, it also contained a fifth: a drop-down on Orders!E2:E100 whose list lives on another sheet. Excel 2010 and later can store this kind of rule in an extension block (x14:dataValidation), and openpyxl does not read that block. Our test file was built by adding that block by hand, not saved from Excel. Loading it printed:

UserWarning: Data Validation extension is not supported and will be removed

The rule was removed from the saved file, but it was never counted. The function still reported 3 rules on Orders. Two consequences:

If the next step is cleaning the data itself, our guide to dropping columns in pandas covers the most common first step.

FAQ: Removing Data Validation in Excel

Does removing data validation change the existing data?

No. It only removes the rules that validate future entries. Whatever is already in the cells stays as-is.

Can I undo removing data validation?

Yes—immediately after you remove it, you can use Undo (Ctrl + Z).

Once you save, close, and reopen the file (or make many more changes), it’s much harder to restore the exact rules unless you:

How do I remove data validation from just one column?

  1. Click the column letter (e.g., C) to select the whole column.
  2. Go to Data → Data Validation.
  3. Click Clear All → OK.

Why are my drop-down arrows still visible?

If drop-down arrows remain:

Wrap-Up

Removing data validation in Excel is straightforward once you know where to look:

With the old rules gone, you can redesign your data entry process or move toward more robust data quality tools that scale better than Excel alone.

© Runcell.RSS