How to See All Data Validation Rules in Excel

Ever opened an Excel spreadsheet from a colleague and felt like you were navigating a minefield? You type in a cell, hit enter, and BAM - an error message pops up, telling you the data is invalid. Data validation is a powerful feature for keeping data clean, but it can be frustrating when you don't know where the rules are or what they do. This guide will show you exactly how to find, review, and manage every data validation rule in your workbook, so you can take back control of your spreadsheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Data Validation in Excel?

Data validation is an Excel feature designed to control what a user can enter into a cell. Instead of letting people type anything they want, you can set up rules to ensure the data is accurate and consistent from the get-go. It's the digital equivalent of putting up guardrails to prevent bad data before it even gets into your sheet.

Common examples include:

• Dropdown Lists: Forcing users to select an option from a predefined list (e.g., "Shipped," "Pending," "Delivered").
• Whole Numbers: Allowing only whole numbers within a specific range (e.g., product quantity must be greater than 0).
• Date Ranges: Restricting entries to a certain period (e.g., project start date must be after today).
• Text Length: Limiting the number of characters in a cell (e.g., a product ID must be exactly 8 characters long).
• Custom Formulas: Building complex rules based on other cells (e.g., the value in this cell cannot be higher than the value in cell D5).

Using data validation is a best practice for anyone building financial models, project trackers, dashboards, or any workbook that will be used by others. It dramatically reduces user error and saves you countless hours of cleaning up messy data later.

When and Why You Need to Find All Data Validation Rules

Just because you didn't build the spreadsheet doesn't mean you're off the hook. Several situations call for a full audit of a worksheet's data validation rules:

• Inheriting a Workbook: When you take over a file from someone else, you need to understand its structure. Finding all validation rules helps you see how the sheet was designed to work.
• Troubleshooting Errors: If you or your team members are repeatedly getting validation errors, seeing all the rules can help you pinpoint the problematic cell or find an overly restrictive rule that needs updating.
• Making Updates: If a business process changes, the validation rules need to change, too. For instance, you might need to add a new option to a dropdown list or adjust an expense limit.
• Auditing Data for Accuracy: Before preparing a final report, it's wise to review the rules to ensure they are still correct and haven’t allowed bad data to slip through.

Method 1: Quickly Find All Validated Cells with "Go To Special"

The fastest way to get a bird's-eye view of all cells with data validation on a single sheet is by using Excel's "Go To Special" feature. This tool tells Excel to highlight every single cell that has a rule applied to it. It won't tell you what the rules are, but it will show you exactly where they are located.

Here’s how to do it step-by-step:

1. On the Home tab of the Ribbon, navigate to the 'Editing' group on the far right.
2. Click on Find & Select, and then choose Go To Special... from the dropdown menu. (The keyboard shortcut is even faster: press F5 and then click the Special... button).
3. The 'Go To Special' dialog box will appear. Select the radio button next to Data validation.
4. Below that, make sure the All option is selected. This tells Excel to find every cell with any type of validation rule.
5. Click OK.

Instantly, Excel will select and highlight every cell in the active worksheet containing a data validation rule. Seeing them all highlighted can be an "aha!" moment, revealing the structure and logic of the spreadsheet.

Pro Tip: Once the cells are highlighted, give them a temporary background color (like bright yellow) using the 'Fill Color' tool on the 'Home' tab. This "paints" the cells, allowing you to click away and still easily see where all the rules are located for your review.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to View the Specifics of Each Rule

Now that you've identified the cells with rules, you need to inspect them to understand the criteria. This is a manual but essential part of the audit process.

1. Click on one of the cells you identified.
2. Go to the Data tab on the Ribbon.
3. In the 'Data Tools' group, click the Data Validation button.

This opens the ‘Data Validation’ dialog box, which has three tabs showing you everything you need to know about the rule in that cell:

• Settings: This tab reveals the core logic. You'll see what kind of data is allowed (the 'Allow' dropdown) and the criteria (e.g., the source for a list, the minimum/maximum bounds for a number, or a custom formula).
• Input Message: This optional feature displays a small message box whenever a user clicks on the cell, guiding them on what to enter. If a message is set up, you'll see it here.
• Error Alert: This tab controls the pop-up message that appears when a user tries to enter invalid data. You can see the message title, the text, and the style (Stop, Warning, or Information).

By clicking through a few of the highlighted cells and checking their settings, you can quickly get a sense of the different types of rules being used in the workbook.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Creating a Comprehensive List with VBA

The "Go To Special" method is great for a quick visual scan, but what if your worksheet has hundreds of rules scattered across thousands of rows? Inspecting them one by one isn't practical. For large or complex sheets, you need an automated solution. That's where a small Visual Basic for Applications (VBA) script comes in handy.

This macro will create a brand new worksheet that lists every single data validation rule, its cell address, its type, and its formula. It’s like creating a detailed blueprint for your spreadsheet’s integrity checks.

Don’t worry if you’ve never used VBA before. Just follow these steps carefully:

1. Open your Excel file and press Alt + F11 to open the Visual Basic Editor (VBE).
2. In the VBE window, click Insert from the menu bar and select Module. A blank white code window will appear.
3. Copy the code below and paste it directly into that module window.

Sub ListAllDataValidationRules()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim cell As Range
    Dim validationCells As Range
    Dim rowNum As Long

    ' Set the worksheet you want to check to the one you have open
    Set ws = ActiveSheet
    
    ' Find all cells with data validation rules applied
    On Error Resume Next ' In case no validation cells exist on the sheet
    Set validationCells = ws.Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0 ' Resume normal error handling

    ' If no cells with rules are found, inform the user and stop
    If validationCells Is Nothing Then
        MsgBox "No data validation rules found on the active sheet.", vbInformation
        Exit Sub
    End If

    ' Add a new worksheet to create the report
    Set newWs = Worksheets.Add(After:=ws)
    newWs.Name = "Validation Rules Report"

    ' Create headers in the new report worksheet
    With newWs
        .Cells(1, "A").Value = "Sheet Name"
        .Cells(1, "B").Value = "Cell Address"
        .Cells(1, "C").Value = "Validation Type"
        .Cells(1, "D").Value = "Formula 1 / Source"
        .Cells(1, "E").Value = "Formula 2"
        .Cells(1, "F").Value = "Error Message"
        .Cells(1, "G").Value = "Input Message"
        .Range("A1:G1").Font.Bold = True
    End With

    rowNum = 2 ' Sets the starting row for the data

    ' Loop through each cell found and write its rule details to the new sheet
    For Each cell In validationCells
        With newWs
            .Cells(rowNum, "A").Value = ws.Name
            .Cells(rowNum, "B").Value = cell.Address
            .Cells(rowNum, "C").Value = GetValidationType(cell.Validation.Type)
            .Cells(rowNum, "D").Value = "'" & cell.Validation.Formula1
            .Cells(rowNum, "E").Value = "'" & cell.Validation.Formula2
            .Cells(rowNum, "F").Value = cell.Validation.ErrorMessage
            .Cells(rowNum, "G").Value = cell.Validation.InputMessage
        End With
        rowNum = rowNum + 1 ' Move to the next row for the next rule
    Next cell
        
    ' Automatically resize the columns for better readability
    newWs.Columns("A:G").AutoFit

    MsgBox "A report of all data validation rules has been created in the 'Validation Rules Report' sheet.", vbInformation
End Sub

Private Function GetValidationType(valType As Long) As String
    ' Helper function to convert the rule type number into readable text
    Select Case valType
        Case 0: GetValidationType = "Any Value"
        Case 1: GetValidationType = "Whole Number"
        Case 2: GetValidationType = "Decimal"
        Case 3: GetValidationType = "List"
        Case 4: GetValidationType = "Date"
        Case 5: GetValidationType = "Time"
        Case 6: GetValidationType = "Text Length"
        Case 7: GetValidationType = "Custom"
        Case Else: GetValidationType = "Unknown"
    End Select
End Function

1. Close the VBE by clicking the 'X' in the top-right corner or by pressing Alt + Q.
2. Back in your Excel sheet, press Alt + F8 to open the Macro dialog box.
3. You should see ListAllDataValidationRules in the list. Select it and click Run.

The macro will execute instantly. A new worksheet named "Validation Rules Report" will appear, containing a neatly organized table of every validation rule from your original sheet. Now you have a complete, documented record you can filter, sort, and analyze.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Next Steps: Modifying or Removing Data Validation Rules

Once you’ve found all the rules, you can decide what to do with them. You may need to update an outdated list, change a date range, or completely remove a rule that's no longer necessary.

Editing an Existing Rule

1. Select the cell (or multiple cells that share the same validation rule).
2. Go to the Data tab and click Data Validation.
3. In the dialog box, simply make your changes. You can change the allowed criteria, update a list source, or modify the error message.
4. If you selected multiple cells and want the change to apply to all of them, a message will ask if you want to extend the changes. Click Yes.

This is perfect for when you need to add a new category to a company-wide dropdown list or change a project spending limit.

Clearing Rules Entirely

Removing a rule is even simpler. If a validation rule is no longer relevant, you can clear it without affecting the data already in the cell.

1. Select the cell or cells where you want to remove the validation.
2. Go back to the Data Validation dialog box (Data > Data Validation).
3. Click the Clear All button in the bottom-left corner and then click OK.

The rule is now gone, and users can enter any value into the cell(s).

Final Thoughts

Knowing how to locate your worksheet's data validation rules transforms you from a passive user into an in-control spreadsheet manager. Using the quick "Go To Special" feature for a visual audit or a powerful VBA script for a detailed report gives you the insight you need to confidently troubleshoot, update, and manage even the most complicated Excel files.

Maintaining clean data in Excel with validation rules is a smart first step, but the real challenges begin when that data needs to be combined and analyzed with information from other platforms like your CRM, ad accounts, and analytics tools. We built Graphed to solve this problem by eliminating the manual work. We connect directly to your data sources - including Google Sheets - to create real-time, shareable dashboards. Instead of wrestling with data, you can just ask questions in plain English and get the charts and insights you need in seconds.