Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to See Data Validation Restrictions in Excel

Cody Schneider••7 min read

Excel's an amazing tool for organizing data, but inheriting a complex spreadsheet can feel like you've been handed a map in a language you don’t speak. If you’re trying to enter data and keep getting blocked by error messages, you've likely run into Data Validation rules. This article will show you exactly how to find and understand every data validation restriction in a workbook, so you can take back control of your spreadsheets.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

What Exactly is Data Validation in Excel?

At its core, Data Validation is a feature designed to control what kind of information can be entered into a specific cell or range of cells. It acts like a bouncer for your spreadsheet, ensuring only the "right" kind of data gets in. This is incredibly useful for maintaining data accuracy, preventing common mistakes, and making spreadsheets easier for others to use.

You’ve probably seen it in action without even realizing it. Common examples include:

  • Dropdown Lists: Limiting choices in a cell to a predefined list, like "East," "West," "North," or "South" for a sales region.
  • Number Ranges: Allowing only whole numbers between 1 and 100 in a "Quantity" column.
  • Date Restrictions: Ensuring a "Project Due Date" is always after the "Start Date."
  • Text Length Limits: Requiring a "Product ID" to be exactly 8 characters long.

By setting up these rules, creators can build robust templates and reports that are far less prone to user error, keeping the data clean and consistent.

Why You Need to Find Existing Data Validation Rules

While Data Validation is powerful, its rules are applied in the background and aren't immediately visible. This means you often need to do some detective work, especially when you’re:

  • Troubleshooting a Spreadsheet: You're trying to input data that you know is correct, but Excel keeps rejecting it. Finding the validation rule will tell you why.
  • Updating an Old File: You’ve taken over a report and need to modify the rules - perhaps update a dropdown list with new products or change a date range.
  • Auditing a Workbook: You need to understand how a complex model works and verify all the constraints that have been applied.
  • Replicating a Template: You want to build a similar spreadsheet and need to copy the exact validation rules.

Knowing how to quickly find these hidden rules is a fundamental skill for anyone who works with Excel regularly.

How to Find All Cells with Data Validation

Let's start with the easiest and most direct method for finding every single cell on a worksheet that has a data validation rule attached to it. Excel has a built-in tool perfect for this task.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Step-by-Step Guide with "Go To Special"

The "Go To Special" feature is your best friend here. It allows you to select cells based on their content, formatting, or - in this case - the rules applied to them.

  1. Open "Find & Select": From the Excel ribbon, click on the Home tab. Towards the far right, you'll see the "Editing" group. Click the Find & Select dropdown menu.
  2. Choose "Go To Special...": In the dropdown menu, select the Go To Special... option. This will open a new dialog box with several selection options.
  3. Select "Data Validation": In the "Go To Special" dialog box, you'll see a list of radio buttons. Click the button next to Data Validation. (You'll see two more options appear below it: "All" and "Same." For our purpose of finding every rule on the sheet, leave "All" selected.)
  4. Click OK: After selecting "Data Validation," click the OK button.

Instantly, Excel will highlight every cell on the active worksheet that has a data validation rule. This is the first and most important step in your investigation. To make these cells even easier to find later, you can apply a fill color (like bright yellow) from the Home tab while they are all still selected.

How to See a Specific Data Validation Rule

Now that you've identified the cells with rules, the next step is to examine what those rules actually are.

  1. Select a Highlighted Cell: Click on any single cell that you found and highlighted in the previous step.
  2. Open the Data Validation Window: Navigate to the Data tab in the Excel ribbon. In the "Data Tools" group, click the Data Validation button. This will open the dialog box where the rule was originally created.
  3. Review the Three Tabs: The Data Validation window has three tabs, each controlling a different part of the rule.

By reviewing these three tabs, you get a complete picture of how the data validation rule functions for that specific cell.

Pro Tip: Use a Macro to List All Rules Across Your Entire Workbook

Manually checking each sheet with the "Go To Special" method works great for small files. But what if you have a workbook with dozens of sheets? Doing this manually is slow and tedious. For a faster and more comprehensive approach, you can use a simple VBA macro to automatically generate a report of every single validation rule in the entire workbook.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

How to Use the VBA Script

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

  1. Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
  2. In the VBA editor menu, go to Insert > Module. A new blank white window will appear.
  3. Copy the code below and paste it into that blank module window.
  4. Press the F5 key on your keyboard to run the macro (or click the green triangle "Run" button in the toolbar).
Sub ListAllDataValidationRules()
    'Deletes old report if it exists and creates a new one
    Dim ws As Worksheet
    Dim cell As Range
    Dim validationReportSheet As Worksheet
    Dim rowCounter As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("Validation Rules").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    Set validationReportSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    validationReportSheet.Name = "Validation Rules"
    rowCounter = 1

    'Setup headers for the report sheet
    With validationReportSheet
        .Cells(rowCounter, 1).Value = "Worksheet Name"
        .Cells(rowCounter, 2).Value = "Cell Address"
        .Cells(rowCounter, 3).Value = "Rule Type"
        .Cells(rowCounter, 4).Value = "Formula"
        .Cells(rowCounter, 5).Value = "Error Message"
        .Range("A1:E1").Font.Bold = True
    End With

    'Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Validation Rules" Then
            On Error Resume Next
            'Find all cells with validation rules on the sheet
            For Each cell In ws.Cells.SpecialCells(xlCellTypeAllValidation)
                rowCounter = rowCounter + 1
                With validationReportSheet
                    .Cells(rowCounter, 1).Value = ws.Name
                    .Cells(rowCounter, 2).Value = cell.Address
                    .Cells(rowCounter, 3).Value = GetValidationType(cell.Validation.Type)
                    .Cells(rowCounter, 4).Value = "'" & cell.Validation.Formula1
                    .Cells(rowCounter, 5).Value = cell.Validation.ErrorMessage
                End With
            Next cell
            On Error GoTo 0
        End If
    Next ws

    validationReportSheet.Columns("A:E").AutoFit
    Application.ScreenUpdating = True
    MsgBox "Report with all data validation rules has been created."
End Sub

Function GetValidationType(valType As Integer) As String
    'Helper function to convert validation type number to descriptive 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 Formula"
        Case Else: GetValidationType = "Unknown"
    End Select
End Function

After a few seconds, a new worksheet named "Validation Rules" will be created. This sheet will contain a tidy list detailing every validation rule: its location (worksheet and cell address), the type of rule, the formula or source list for that rule, and any custom error message associated with it. This creates a powerful audit log you can use for your analysis.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Final Thoughts

Understanding how to unearth Data Validation rules is a small skill that pays big dividends. Using the built-in "Go to Special" function helps you quickly find and troubleshoot any restrictions on a given sheet, while a simple VBA macro can give you a bird's-eye view of your entire workbook in seconds. It pulls back the curtain on how a spreadsheet is meant to be used, saving you time and frustration.

Moving from manual spreadsheet tasks to automated insights is what modern data analysis is all about. While identifying data validation rules helps clean up manual inputs, the next step is automating your reporting process entirely. Instead of struggling with CSVs and wrestling spreadsheets into submission, we built Graphed to connect directly to your data sources - like Google Analytics, Shopify, and Salesforce. You can ask for reports in plain English and get live, interactive dashboards in an instant, freeing you from manual data chores for good.

Related Articles