Google Sheets Data Validation: Edit Rules and Lists

Yes. To edit data validation in Google Sheets, select a cell or range with a rule, choose Data > Data validation, and edit the rule in the side panel. You can change its criteria, dropdown choices, applied range, and whether invalid entries are rejected or merely flagged.

How to edit a data validation rule

Data validation controls what people can enter in selected cells. To change an existing rule in a browser, open the spreadsheet and follow these steps:

Select a cell covered by the rule. To review rules for several cells at once, select the relevant range.
Choose Data > Data validation. The data validation panel displays rules that apply to the selection.
Select the rule you want to change, then edit its criteria, range, options, or invalid-data behavior.
Finish by clicking Done or Save if that control appears. Google Sheets saves spreadsheet changes automatically.

If the selected cell has no rule, the panel may offer Add rule rather than an existing rule to edit. Adding a new rule is different from changing one: it may create another rule for the selected cells, so check the panel before adding anything. If you cannot find a rule, try selecting another cell in the range or opening the rule panel with a broader selection.

What you can change

A rule has two important parts: the cells it applies to and the condition those cells must meet. Editing the condition changes what entries are considered valid. Depending on the selected criteria, you might set an allowed date, a number limit, a text condition, a dropdown list, or a custom formula. The panel shows the settings available for the chosen type.

For example, a quantity column might permit whole numbers from 1 through 50. You could revise the upper limit to 100 without changing the cells’ existing values. A status column might use a dropdown with values such as Not started, In progress, and Complete. You can edit those choices to match a revised workflow.

Changing the rule does not generally rewrite the contents of the cells. If a cell already contains a value that no longer meets the revised condition, that value may remain in place. Validation is primarily a control on entries, not a data-cleaning command. Review existing values separately when you change a rule.

Edit a dropdown list

Google Sheets supports dropdowns whose choices are entered directly in the rule and dropdowns whose choices come from a cell range. Select a cell with the dropdown and open Data > Data validation to see which kind it uses.

Choices entered in the rule: Add, remove, or rename the listed options in the rule editor. Keep labels consistent if other sheets, charts, or formulas rely on those exact words.
Choices from a range: Change the source range in the rule, or edit the values in the cells that supply the choices. A source range is useful when a list is maintained in one place and reused.

Suppose a project tracker uses the values Planned, Active, and Done. Changing Active to In progress in the dropdown rule affects future selections, but cells that already contain Active are not necessarily converted. You may need to find and replace the old label or update those cells individually. Before renaming an option, check whether formulas such as COUNTIF, filters, or summary tables look for the old text.

Dropdown appearance can also be changed independently of the permitted values. Depending on the rule editor, you can choose a display style such as a chip, an arrow, or plain text. Changing the style affects how the dropdown appears, not the meaning of the stored choice.

Change which cells the rule covers

The Apply to range field determines where a rule operates. It may contain a single cell, a continuous range such as B2:B100, or multiple ranges. Edit that field when a rule should cover more or fewer cells. Check the range carefully: an accidental change can leave intended cells without validation or apply a restriction to unrelated cells.

To extend a dropdown from B2:B20 to B2:B100, edit the applied range rather than rebuilding the dropdown. If new rows are regularly added, consider whether the sheet’s layout or the rule’s range needs periodic maintenance. A rule does not automatically mean that every future row in the sheet will be covered.

When a selection contains cells with different validation rules, the panel may show more than one rule. Make sure you are editing the intended one, especially in a sheet with separate rules for headers, input cells, and calculated cells. Selecting a representative cell first can make it easier to identify the correct rule.

Choose whether invalid entries are rejected

Validation can either reject an entry that does not meet the rule or show a warning while allowing it. The choice depends on how strict the sheet needs to be. For a controlled status list, rejecting values can prevent inconsistent spellings. For a planning sheet where unusual dates or quantities may be legitimate, a warning may be less disruptive.

Reject input: Use this when entries must conform to the rule, such as selecting one of a fixed set of department names.
Show a warning: Use this when the rule is guidance rather than an absolute restriction. People can enter an out-of-rule value, but Sheets signals that it does not match.

Consider the people who will use the sheet. A strict rule can improve consistency, but it can also block legitimate exceptions. A warning preserves flexibility, but it does not guarantee clean data. If you switch from warnings to rejection, check the existing values and explain the allowed choices to collaborators before they begin entering data.

Existing values, pasted data, and imports

Editing a rule and correcting a column are separate jobs. If the rule changes from allowing any date to allowing dates in a particular period, older dates may remain even though they no longer qualify. Review the affected cells for values outside the new condition and decide whether to correct them, keep them as exceptions, or adjust the rule.

Do not assume that validation is a complete audit of every value already in a sheet. Bulk edits, imports, and copied content can make it especially important to check the resulting data. After pasting a large block or importing a file, scan the affected range and look for values that do not match the intended list or format. A rule helps guide entry, but it should not replace review when accuracy matters.

When a dropdown choice is renamed, existing cells keep their previous text unless you change those cells too. This distinction matters when the values feed reports. For instance, a chart grouped by Complete will not automatically treat a renamed value such as Done as the same category. Update the data and any dependent formulas or summaries as needed.

Common problems and fixes

The rule does not appear: Select a cell that should be covered, then open Data > Data validation. Check that you are in the correct spreadsheet and have permission to edit it.
A dropdown is missing some choices: Inspect the source range or the manually entered options. The source cells may be blank, the range may be too short, or the desired choice may not have been added.
A valid-looking entry is blocked: Check the exact criteria, including number limits, date conditions, and spelling. A text dropdown value must match an allowed choice as defined by the rule.
Old entries still appear after an edit: Changing a rule does not automatically replace existing cell contents. Correct those entries separately if the new standard requires it.
The rule affects the wrong cells: Review Apply to range and correct the references. If multiple rules are present, confirm which one applies to the selected cells.

For a collaborative sheet, permissions can also explain why editing is unavailable. A person with view-only or comment access cannot change validation rules. Ask the file owner for edit access or for the owner to make the change. If someone else is currently editing the same rule, coordinate before making a broad change so the final range and criteria are clear.

Keep validation manageable

Rules are easier to maintain when the sheet has a clear input area and a documented source for each dropdown. Put reusable choices in a labeled list, avoid maintaining several slightly different copies of the same list, and use descriptive column headings. If a rule is important, add a brief note near the input area explaining acceptable values or the reason for a restriction.

Before editing a rule across a large shared sheet, consider making a copy or confirming that the change is intended. Then check a few representative cells: an ordinary valid entry, a boundary value, and an invalid example if appropriate. This helps reveal a mistaken range or condition before many collaborators rely on it. For rules used by reports, check that the allowed values still agree with the labels expected by those reports.

Google Sheets and Excel are different

These steps are for Google Sheets in a web browser. In Excel, data validation is managed through a different interface: on Windows, it is commonly found at Data > Data Validation on the ribbon. Excel and Sheets have similar goals, but their dialogs, available criteria, and collaboration behavior are not identical. Follow the instructions for the spreadsheet application you are actually using rather than expecting the same panel or menu in both.

Frequently Asked Questions

Can I edit data validation for multiple cells at once?
Select the cells or range, open Data > Data validation, and edit the applicable rule. If the selection includes different rules, review them individually and confirm each applied range before changing it.

How do I remove data validation in Google Sheets?
Select the affected cells, open Data > Data validation, choose the rule, and use Remove rule in the panel. Removing a rule removes the entry restriction; it does not erase the values already in the cells.

Why can I still see an old dropdown value after changing the list?
The cell may already contain the old text. Editing the choices changes the rule, not necessarily existing contents. Replace old entries separately if the new label should appear throughout the sheet.

Can I change a dropdown list without changing the cells that use it?
Yes. Edit the dropdown options or its source range while leaving Apply to range unchanged. Check dependent formulas or reports before renaming choices, because they may use the original text.

Why can’t I edit the rule?
You may lack edit permission, or you may have selected a cell that is not covered by the rule. Confirm your access with the file owner and select a cell within the intended range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.