Remove Data Validation in Excel: Keep Data, Clear Restrictions

Excel’s Data Validation feature is widely used to control what users can type into a cell. It’s often applied to restrict values to a specific list (drop-down menus), limit inputs to numbers or dates, or prevent duplicate entries. While powerful, these rules can sometimes get in the way—maybe the restrictions are no longer needed, or you want to allow free data entry.

In such cases, you may need to remove data validation in Excel. Clearing validation rules won’t erase the cell contents—it only removes the restrictions. That means your existing data stays intact, but you’ll be free to type anything in those cells.

If you want to remove data validation restrictions in Excel, there are both manual and automated methods you can use. This tutorial covers Ribbon commands, shortcuts, and automation with Python or VBA.

Quick Comparison: Methods to Remove Data Validation in Excel

When working with Excel, there are several ways to remove data validation depending on your needs—whether you want a quick manual removal, batch processing, or automated handling across multiple files. The table below provides a quick comparison of each method, showing their scope, difficulty, and which scenario they are best suited for.

1. Remove Data Validation in Excel Using the Ribbon

The most common way to delete data validation is through Excel’s Ribbon interface. This method is beginner-friendly and works in all modern versions of Excel.

Steps:

1. Select the cell or range that contains the validation rule.
2. Go to the Data tab on the Ribbon.
3. Click Data Validation (in the Data Tools group).

1. In the pop-up window, click Clear All.
2. Press OK to confirm.

This action will remove data validation from the selected cells. The cell values themselves won’t change.

Tip: If you only see “Circle Invalid Data,” click the drop-down arrow under Data Validation to access the full menu.

2. Clear Data Validation in Excel from an Entire Column or Sheet

If you need to remove validation rules across a large area, you don’t have to clear them cell by cell.

• To remove validation from a whole column, click the column header (e.g., B to select B:B).

• To remove validation from the entire sheet, press Ctrl + A to select all cells.

• Then repeat the steps: Data → Data Validation → Clear All → OK.

This is particularly useful when you’ve inherited a spreadsheet full of drop-down lists or restrictions and want to clear everything in one go.

Efficiency tip: Removing validation at the column or sheet level is much faster than targeting individual cells.

3. Keyboard Shortcut to Remove Data Validation

If you prefer working primarily with the keyboard rather than the mouse, there's a handy shortcut in Excel that lets you quickly remove data validation from selected cells or ranges.

• Select the cell or range where you want to remove data validation.
• Press Alt + D + L (short presses, not holding) to open the Data Validation dialog box.
• Then click Clear All → OK.

This shortcut works in many Excel versions, though availability can vary.

4. Remove Excel Data Validation with Python (Automation)

While the manual methods above work well for individual cells or sheets, they can become time-consuming when dealing with multiple workbooks or large ranges. In such cases, automation is a more efficient solution.

To automate this task, we can use Spire.XLS for Python, a powerful library that allows programmatic manipulation of Excel files, including removing data validation rules while keeping existing data intact.

Before you start, install the package via pip:

pip install spire.xls

Example: Remove Excel Data Validation Rules with Python

With Spire.XLS for Python, you can remove data validation rules using Python code from a specific range or the entire worksheet, keeping your existing data safe. This approach is ideal for processing multiple files or large datasets quickly and reliably.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file that contains data validation rules
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
worksheet = workbook.Worksheets[0]

# --- Method 1: Remove data validation from a specific range ---
# Rectangle.FromLTRB(startColumn, startRow, endColumn, endRow)
# Note: coordinates are zero-based (0 = first column/row)
# Example below: columns A–C (0-2), rows 1–12 (0–11)
range_rect = Rectangle.FromLTRB(0, 0, 2, 11)

# --- Method 2: Remove all data validation rules from the worksheet ---
# Use the sheet's LastRow and LastColumn to cover the full used range
#all_rect = Rectangle.FromLTRB(0, 0, worksheet.LastColumn, worksheet.LastRow)

rects = [range_rect]
worksheet.DVTable.Remove(rects)

# Save the workbook to a new file
workbook.SaveToFile("output/RemoveDataValidation.xlsx", ExcelVersion.Version2016)

Explanation

• Rectangle(startColumn, startRow, endColumn, endRow) creates a rectangle object that defines the range to remove validation rules from.
• DVTable.Remove(Rectangle) removes data validation rules from the specified range.
• The cell values remain unchanged—only the restrictions are deleted.
• You can either target specific ranges or remove validation rules from the entire worksheet in one step.

Here’s how the sheet looks after running the Python script—data validation is cleared, but values remain.

This approach is ideal if you frequently process spreadsheets with restrictive validation rules.

If you want to learn how to add data validation in Excel using Python, check out our step-by-step guide to set up rules and drop-down lists.

5. Alternative: Remove Data Validation with VBA

If you prefer Excel’s built-in scripting language, a simple VBA macro can remove data validation:

Sub RemoveAllValidation()
    Cells.Validation.Delete
End Sub

This deletes all validation rules from the active worksheet.

You can also target a specific range instead of the entire sheet:

Sub RemoveValidationFromRange()
    Range("B2:D10").Validation.Delete
End Sub

VBA is convenient for Excel power users, but if you need to handle multiple files or integrate with other workflows, Python is generally more flexible.

Difference Between Clearing Data Validation and Clearing Contents

A common confusion in Excel is the difference between clearing validation rules and clearing cell contents:

• Clear Data Validation → Removes only the restrictions (rules). The existing values stay intact and editable.
• Clear Contents → Removes both the values and any applied validation.

When your goal is to remove data validation without losing the data, always use the Clear All option in the Data Validation dialog—not the Clear Contents command from the Ribbon or right-click menu.

This distinction ensures your data remains available while lifting input restrictions. You can also simplify spreadsheet cleanup by learning how to delete entire rows or columns in Excel when you need to remove unnecessary data quickly.

Conclusion

You now know several ways to remove data validation in Excel:

• Manual methods: Use the Ribbon, apply it to columns or the whole sheet, or rely on the Alt + D + L shortcut.
• Automated methods: Use Python (via Spire.XLS for Python) for scalable solutions, or VBA macros for quick fixes inside Excel.

Whether you’re clearing a single drop-down list or removing restrictions from an entire workbook, these methods ensure you can work freely with your data. And remember—removing validation never deletes existing values, so you can proceed confidently.

If you need to remove data validation restrictions in Excel repeatedly, automation with Python or VBA will save significant time and effort.

FAQ for Removing Data Validation in Excel

Q1: Will removing data validation delete my cell values?

No. It only removes the rules, not the existing content.

Q2: Can I remove data validation from multiple sheets at once?

Manually, no—you’ll need to repeat the process for each sheet. With Python or VBA, you can automate across sheets.

Q3: Is there a shortcut to clear data validation in Excel?

Yes, press Alt + D + L to open the Data Validation dialog, then choose Clear All.

Q4: What’s the difference between Clear Contents and Clear Validation?

Clear Contents erases both values and validation. Clear Validation removes only the restrictions, keeping values intact.

Q5: How do I delete data validation restrictions across an entire workbook?

You’ll need automation. A Python script or VBA macro can loop through all worksheets and remove validation rules while keeping the data untouched.

See Also

• Find and Replace Data in Excel with Python
• Sort Excel Data in Python to Organize Your Sheet
• Batch Import Data into Excel with Python
• Hide or Unhide Rows and Columns in Excel with Python