How to Remove Data Validation in Excel

Excel's Data Validation is a fantastic feature for keeping your spreadsheets clean and standardized by restricting what users can enter into a cell. But sometimes, those same rules need to be updated or removed entirely. This guide will walk you through several straightforward methods to clear data validation rules from your worksheets, whether for a single cell, a specific range, or the entire sheet.

A Quick Refresher: What is Data Validation?

Before we learn how to remove it, let's briefly touch on what data validation is. It's a tool in Excel that allows you to set up rules for data entry. Think of 'rules' as guardrails that prevent mistakes before they happen. Common examples include:

• Drop-down lists: Forcing a user to select an option like "Pending," "In Progress," or "Complete" from a predefined list.
• Number ranges: Allowing only whole numbers between 1 and 100 in a "Percentage Complete" column.
• Date ranges: Ensuring all entered dates fall within a specific project timeline.
• Text length: Limiting an entry in a "Product ID" column to exactly 8 characters.

While incredibly useful for maintaining data integrity, these rules can become a roadblock if data requirements change or if you need the freedom to enter different values. That's when you need to know how to remove them.

Method 1: The Standard Way for a Single Cell or Range

This is the most common and direct method for clearing data validation from a known area of your spreadsheet. It's quick, easy, and gets the job done in just a few clicks.

Step-by-Step Instructions:

1. Select Your Cells: First, click on the single cell or click and drag to select the range of cells where you want to remove the validation rules. In our example, let's say it's a column with a drop-down list of project statuses.
2. Navigate to the Data Tab: At the top of the Excel ribbon, click on the Data tab.
3. Open the Data Validation Dialog Box: Look for the Data Tools group on the ribbon. Click the Data Validation icon. It might appear as an icon with a green checkmark and a red circle with a slash, or it might be a text button depending on your screen size.
4. Clear All Validation Rules: The Data Validation window will open. In the bottom-left corner of the dialog box, you'll see a button labeled Clear All. Click this button. You will notice that all of the settings in the dialog box (Allow, Data, etc.) are now reset to their defaults (Allow: Any value).
5. Confirm Your Changes: Click OK to close the window and apply the changes. Voila! The data validation rule has been removed from the selected cells. The drop-down arrow will disappear, and you can now enter any value you want.

Method 2: Find and Remove ALL Data Validation on a Worksheet

What if you've inherited a complex spreadsheet and aren't sure where all the validation rules are hiding? Manually hunting them down is a pain. Instead, you can use Excel's "Go To Special" feature to instantly find and select every single cell on the sheet that has a validation rule applied.

Step-by-Step Instructions:

1. Open the "Go To" Dialog Box: You can do this in two ways:
2. Access "Go To Special": In the "Go To" window that appears, click the Special... button in the bottom-left corner.
3. Select Data Validation: In the "Go To Special" window, select the radio button for Data Validation. Leave the sub-option set to All.
4. Confirm and Select: Click OK. Excel will instantly highlight every cell on the current worksheet that contains a data validation rule.
5. Clear the Validation Rules: Now that all the relevant cells are selected, simply follow Steps 2-5 from Method 1. Go to Data > Data Validation > Clear All > OK. This single action will remove all data validation rules from the entire worksheet at once.

Method 3: A Quick (But Imperfect) Copy-Paste Trick

There's another clever way to remove data validation, though it comes with a major caveat. You can "wipe" the validation from a cell by pasting a "clean" cell over it. This method also overwrites the content and formatting of the destination cell, so it's best used when you want to quickly reset a set of cells entirely.

How It Works:

1. Find and copy a "clean" cell: Locate any blank cell on your worksheet that you know has no formatting or data validation rules. Click on it and press Ctrl + C to copy it.
2. Select the target cells: Select the cell or range of cells from which you want to remove the data validation.
3. Paste: Press Ctrl + V to paste.

Because you've pasted the entire cell (content, formatting, and lack of validation), the original cell's validation rules are effectively replaced and erased. This is a fast but blunt instrument. For more precise control without losing data, stick to Methods 1 and 2.

Method 4: Using VBA for Bulk Removal Across Sheets

If you're a power user or regularly need to strip validation from entire workbooks, a small bit of VBA (Visual Basic for Applications) code can automate the entire process. This is the fastest way to clear rules from multiple sheets or a massive file.

Quick PSA: Before running any macros, it’s always a good idea to save a backup copy of your file, just in case.

Step-by-Step Instructions:

1. Open the VBA Editor: From your active Excel window, press Alt + F11 (Windows) or ⌥ + fn + F11 (Mac) to open the VBA editor.
2. Insert a New Module: In the VBA editor menu, go to Insert > Module. This will open a blank white code window.
3. Copy and Paste the Code: Copy the code snippet below and paste it into the module window. This simple macro targets every cell on the active worksheet and deletes any validation rule it finds.

Sub RemoveValidationFromActiveSheet()
    Cells.Validation.Delete
End Sub

Want to be more specific? If you only want to remove validation from the selected cells, you can use this code instead:

Sub RemoveValidationFromSelection()
    Selection.Validation.Delete
End Sub

1. Run the Macro: Close the VBA editor to return to your worksheet. To run your new macro, press Alt + F8 to open the Macro dialog box, select "RemoveValidationFromActiveSheet" (or your other macro), and click Run. The data validation will be instantly removed.

Final Thoughts

Whether you need to remove a drop-down list from one column or clear outdated rules from an entire report, you can easily remove data validation using the standard menu, the "Go To Special" feature, or a simple VBA macro. Each method is simple to execute once you know where to look, giving you full control over your data again.

Managing data entry rules and reporting in Excel can quickly become a manual, time-consuming job. We built Graphed to bypass the headaches of manual data wrangling by automatically syncing with tools like Google Analytics, Shopify, and your CRM. Instead of setting up validation rules for reports, you can just ask a question in plain English and instantly get real-time dashboards and answers, freeing you up to focus on strategy instead of spreadsheets.