How to Remove Data Validation in Excel: A Comprehensive Guide

Published: October 17, 2024 - 6 min read

Frank Ferris
how to remove data validation in excel

Data validation in Excel helps maintain data integrity by restricting what users can enter into cells. However, there are times when you need to remove these restrictions. This guide covers multiple methods to remove data validation across different Excel versions and scenarios.

Method 1: Removing Data Validation Using the Data Tab

This straightforward method works in most Excel versions.

Step-by-Step Instructions

  1. Open your Excel spreadsheet
  2. Select the cells containing data validation
  3. Click the “Data” tab in the ribbon menu
  4. Find the “Data Tools” section and click “Data Validation
Going to Data in the top menu and selecting Data Validation.
  1. In the dialog box that appears, click “Clear All
Clicking "Clear all" in the bottom left-hand corner of the menu.
  1. Click “OK” to confirm and remove the validation
Selecting OK after clearing all.

Troubleshooting Common Issues

“Clear All” is grayed out: This usually happens when the worksheet is protected. To fix:

  1. Go to “Review” > “Unprotect Sheet
Clicking Unprotect Sheet from the menu.
  1. Enter the password if prompted
  2. Try clearing data validation again

Removing validation from multiple cells: Select all relevant cells before opening the Data Validation dialog. Use Ctrl+Click or Shift+Click to select non-adjacent cells.

Protected worksheets: If you can’t unprotect the sheet, contact the spreadsheet owner for the necessary permissions.

Method 2: Using Keyboard Shortcuts to Remove Data Validation

Keyboard shortcuts offer a quicker way to remove data validation, especially for power users.

Quick Removal for Windows Users

  1. Select the cells with data validation
  2. Press Alt + A + V + V (in sequence, not simultaneously)
  3. In the Data Validation dialog, click “Clear All
Clicking "Clear all" in the bottom left-hand corner of the menu.
  1. Press Enter to confirm

Pro tip: Use Ctrl+A to select the entire worksheet if you want to remove all data validation at once.

Shortcuts for Mac Excel Users

The process is slightly different on Mac:

  1. Select cells with validation
  2. Press Fn + Control + F2 to activate the ribbon
  3. Use arrow keys to navigate: right to “Data“, down to “Data Validation
  4. Press Enter, then use arrow keys to select “Clear All
Clicking "Clear all" in the bottom left-hand corner of the menu.
  1. Press Enter again to confirm

If shortcuts don’t work, check your Mac’s keyboard settings or use the menu method described earlier.

Advanced Techniques for Removing Data Validation

Using Go To Special to Find and Remove All Validations

This method helps you quickly locate and remove all data validation in a worksheet:

  1. Press Ctrl+G to open the Go To dialog
Seeing nothing, populate the go-to menu that pops up.
  1. Click “Special
Cooking special in the bottom left-hand corner.
  1. Select “Data validation
Selecting data validation from the list of options.
  1. Click “OK” to select all cells with validation
Clicking Okay after selecting all of the "Go to special" options.
  1. Follow Method 1 or 2 to clear the validation

VBA Macro for Removing Data Validation

For frequent data validation removal, a VBA macro can save time:

  1. Press Alt + F11 to open the Visual Basic Editor
Opening Visual Basic in Excel.
  1. Insert a new module (Insert > Module)
Selecting a module from the list of projects on the VBA.
  1. Paste this code:

Sub RemoveDataValidation()

    Dim ws As Worksheet

    Dim rng As Range

    Set ws = ActiveSheet

    Set rng = ws.UsedRange

    rng.Validation.Delete

End Sub

A screenshot of the code inserted into the blank screen.
  1. Run the macro by pressing F5 or creating a button on your worksheet

Customize the macro to target specific ranges or worksheets as needed.

Removing Specific Types of Data Validation

Deleting Drop-Down Lists

Drop-down lists are a common form of data validation. To remove them:

  1. Select the cells containing drop-downs
  2. Go to Data > Data Validation
Going to Data in the top menu and selecting Data Validation.
  1. Click “Clear All”
Clicking "Clear all" in the bottom left-hand corner of the menu.
  1. Click “OK
Selecting OK after clearing all.

To keep the list data while removing validation:

  1. Copy the list items before clearing validation
  2. Paste the items into a new column for reference

Clearing Number and Date Restrictions

Number and date validations often use custom formulas. To remove:

  1. Select cells with number/date validation
  2. Open Data Validation dialog
  3. Check the “Allow” dropdown – if set to “Custom“, a formula is being used
Selecting "Allow custom" from the dropdown menu.
  1. Click “Clear All” to remove the validation
Selecting 'Clear all' from the menu in the bottom left-hand corner.

After removal, review your data to ensure it still makes sense without the restrictions.

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Understanding Data Validation in Excel

What is Data Validation?

Data validation in Excel allows you to control what users can enter into specific cells. It helps maintain data consistency and accuracy by setting rules for acceptable input.

Common uses include:

  • Creating drop-down lists for predefined options
  • Limiting number ranges (e.g. dates, quantities)
  • Enforcing text length or format requirements

Why Remove Data Validation?

While data validation is useful, there are times when removing it becomes necessary:

  • Updating spreadsheet structure or requirements
  • Allowing more flexible data entry
  • Troubleshooting formula errors caused by validation
  • Preparing data for export or analysis

Knowing how to remove data validation gives you more control over your Excel workflows.

FAQ: Common Questions About Removing Data Validation

Why can’t I remove Data Validation in Excel?

Common reasons include:

  • The worksheet is protected
  • You don’t have edit permissions
  • The cells are part of a table with validation applied to the entire column

Try unprotecting the sheet or contacting the file owner for necessary permissions.

What is the shortcut for removing Data Validation in Excel?

For Windows: Alt + A + V + V, then select “Clear All” For Mac: Fn + Control + F2, navigate to Data Validation, select “Clear All”

How do I remove Data Validation from Google Sheets?

  1. Select cells with validation
  2. Go to Data > Data validation
  3. Click “Remove validation

The process is similar to Excel, but the menu structure differs slightly.

How do I remove Data Validation circles in Excel?

Data validation circles are visual indicators, not the validation itself. To remove:

  1. Go to File > Options
  2. Select “Formulas
  3. Uncheck “Enable background error checking
  4. Click “OK

This removes all error indicators, not just data validation circles.

Can I undo the removal of Data Validation?

Yes, immediately after removal:

  • Press Ctrl + Z (Windows) or Command + Z (Mac)
  • Or click the Undo button in the Quick Access Toolbar

For older versions or if you’ve made other changes, you may need to reapply the validation manually.

Data validation in Excel is a powerful tool, but knowing how to remove it is equally important. By mastering these techniques, you’ll have greater control over your spreadsheets and data management processes. Remember to always consider the impact of removing validation on your data integrity and workflow.

Ready to take your Excel data management to the next level? Get started with Coefficient to unlock advanced data integration and real-time syncing capabilities that complement your Excel skills.

Connect Live Data to Excel Instantly

Automatically sync data from any source into Excel and keep it on a refresh schedule with Coefficient.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Frank Ferris Sr. Manager, Product Specialists
Frank is the spreadsheet ninja you never knew existed. Frank's focus throughout his career has been all about growing businesses quickly through both strategy and effective operations. His advanced skillset and understanding of how to leverage data analytics to automate processes and make better and faster decisions make him the unicorn any team can thrive with.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies