How to Change a Data Validation Rule in Excel

Changing a data validation rule in Excel is straightforward once you know where to find the right settings. Whether you need to update a dropdown list, adjust acceptable number ranges, or tweak an error message, you can modify existing rules in just a few clicks. This tutorial will walk you through how to find, edit, copy, and remove data validation rules to keep your spreadsheets accurate and user-friendly.

What is Data Validation in Excel?

Data validation is a feature in Excel that helps you control what kind of information is entered into a cell. Think of it as setting up guardrails for your data. Its purpose is to maintain data integrity and prevent common entry errors, which is incredibly useful when you're building reports, trackers, or templates that other people will use.

You can use it to enforce rules like:

• Dropdown Lists: Restricting choices to a predefined list of options, such as campaign names, sales regions, or project statuses (e.g., "Pending," "In Progress," "Complete").
• Number Ranges: Allowing only whole numbers between 1 and 100, or decimal values greater than zero. This is perfect for cells where users enter budgets, quantities, or scores.
• Date & Time Limits: Ensuring dates fall within a specific period, like Q1 of the current year or after a project start date.
• Text Length: Limiting the number of characters in a cell, useful for things like postal codes or product IDs.
• Custom Formulas: Creating complex rules, such as preventing duplicate entries in a column or ensuring an entry matches a specific format.

When someone tries to enter data that violates the rule, Excel shows an error message, stopping incorrect data before it messes up your formulas and reports. As your projects evolve, you'll inevitably need to update these rules - and that's exactly what we'll cover next.

How to Find and Edit an Existing Data Validation Rule

If you inherited a spreadsheet or forgot where you applied certain rules, your first step is to locate all the cells using data validation. Trying to find them by randomly clicking around is a waste of time, especially in a large worksheet. Instead, use Excel's "Go To Special" feature.

Step 1: Find All Cells with Data Validation

This powerful feature instantly highlights every cell on your worksheet that has a validation rule applied.

1. Navigate to the Home tab on the Excel ribbon.
2. On the far right, click Find & Select.
3. From the dropdown menu, choose Go To Special...
4. In the dialog box that appears, select the Data validation option.
5. Click OK.

Excel will automatically highlight every cell with a validation rule. From here, you can investigate the rules applied to these specific cells. If you want to find all cells with the exact same rule, simply select one of the highlighted cells and choose the "All cells with same" option under Data validation from the Find & Select dropdown.

Step 2: Open the Data Validation Dialog Box

Once you've selected a cell (or a group of cells) whose rule you want to edit, you need to open the main Data Validation settings window.

1. While the cell(s) are selected, click on the Data tab in the ribbon.
2. In the Data Tools section, click the Data Validation icon.

This will open the Data Validation dialog box, which has three tabs: Settings, Input Message, and Error Alert. This is where all modifications happen.

Important Tip: If you want to apply the same change to multiple cells that share the same validation rule, select all of them at once before opening the Data Validation dialog box. Any edits you make will apply to all selected cells.

Step 3: Modify the Validation Rule and Criteria

The Settings tab is where you define the core logic of your rule. Here's how to edit the most common types of validation rules.

Changing a Dropdown List

Dropdown lists are one of the most common uses of data validation, and they often need updates. How you edit the list depends on how it was created.

• Editing a Manually Typed List: If the original list was typed directly into the Source field (e.g., "Google,Facebook,LinkedIn"), you can simply edit that text. Just add, remove, or change the items in the box, making sure each item is separated by a comma.
• Changing a List Source Range: A more robust method is to point the list to a range of cells elsewhere in your workbook (often on a separate "Lists" or "Settings" tab). The Source field will look like a formula, such as =Sheet2!$A$2:$A$10. To change the dropdown options, do not edit this formula. Instead, go to the source range (in this case, cells A2:A10 on Sheet2) and change the values in those cells. The dropdown list will update automatically. If you need to expand the list, simply update the range in the formula to include more cells (e.g., change $A$10 to $A$15).

Adjusting Number, Date, or Text Length Rules

Maybe your budget ceiling increased, or your reporting period changed. Modifying criteria for numbers, dates, or text is done on the same Settings tab.

1. From the Allow dropdown, ensure the correct data type is selected (e.g., Whole number, Date, Text length).
2. Use the Data dropdown to select the logical operator (e.g., between, greater than, equal to).
3. Update the values in the fields that appear. For example, to allow numbers between 1 and 500 instead of 1 and 100, you would change the Maximum value to 500.

Editing the Input Message and Error Alert

Good spreadsheets give users guidance. You can add or change the helpful hint that appears when a user clicks the cell (Input Message) or the warning that shows up when they enter invalid data (Error Alert).

• Input Message: Click the Input Message tab. Add or edit the Title and Input message text to provide better instructions. To remove the message entirely, uncheck the "Show input message when cell is selected" box.
• Error Alert: Go to the Error Alert tab. Here, you can change the Style to be a hard 'Stop' (prevents entry), a 'Warning' (allows entry after a warning), or 'Information' (just informs the user). You can also write a more helpful custom error message than Excel's generic one.

Once you've made your changes, click OK to save and apply them.

Advanced Tips for Managing Data Validation Rules

Knowing how to edit a rule is great, but knowing how to manage them efficiently is even better. Here are a few tricks to save you time and headaches.

Copying a Data Validation Rule

If you've set up the perfect rule and want to apply it to other cells, don't recreate it from scratch. Use Paste Special.

1. Select the cell that has the data validation rule you want to copy.
2. Press Ctrl + C (or Cmd + C on Mac) to copy it.
3. Select the destination cell(s) where you want to apply the rule.
4. Right-click on the selection and choose Paste Special....
5. In the dialog box, select the Validation option and click OK.

This copies only the data validation rule, leaving the content and formatting of the destination cells untouched.

Finding and Reviewing Invalid Data

What if someone entered data before a rule was applied, or you just changed a rule and now some existing entries are invalid? Excel can help you find them.

1. Go to the Data tab.
2. Click the small dropdown arrow next to Data Validation.
3. Select Circle Invalid Data.

Excel will draw a red oval around every cell in the sheet containing data that violates its assigned validation rule. This makes it incredibly easy to spot and fix errors. To remove the circles, click the same dropdown and choose Clear Validation Circles.

How to Clear or Remove Data Validation Rules

If a data validation rule is no longer needed, you can remove it completely.

1. Select the cell or cells from which you want to remove the rule.
2. Navigate to the Data tab and click Data Validation.
3. In the Settings tab of the dialog box, click the Clear All button in the bottom-left corner.
4. Click OK.

The restriction will now be gone, and users can enter any value into those cells.

Final Thoughts

Knowing how to find, edit, and manage data validation rules transforms Excel from a simple grid into an organized, error-proof tool for your business. Whether you're updating a project tracker or creating a marketing budget template for your team, having clean, accurate data is the foundation of any reliable report.

Of course, maintaining data integrity in spreadsheets becomes more challenging as your data grows and spreads across different platforms. We built Graphed because manually pulling, cleaning, and validating data shouldn't consume your entire week. Instead of wrestling with rules in Excel, you can connect your apps like Google Analytics, Shopify, and Salesforce, then simply ask in plain English for the dashboard you need. It's the fastest way to get accurate, real-time reports without the manual work.