excelavon

Google Sheets Data Validation: How to Add Rules

How-to basics · Updated November 3, 2026

Google Sheets Data Validation: How to Add Rules

To add data validation in Google Sheets, select the cells, choose Data > Data validation, click Add rule, set the criteria, and choose whether invalid entries are rejected or flagged with a warning. Validation helps keep entries consistent by offering drop-downs or limiting what people can type.

The steps below are for Google Sheets in a web browser. You can use validation for items such as task statuses, dates, numbers, and email addresses. It guides people as they enter data, but it does not replace checking or cleaning existing values.

What You’ll Need

  • A Google account with access to the spreadsheet and permission to edit it. If you can only view or comment, ask the owner for editing access.
  • The cell or range you want to control, such as C2:C100.
  • If you are making a drop-down list from a range, a list of allowed values in the spreadsheet. Put the list somewhere easy to maintain, such as a separate tab named Lists.

You do not need to install an add-on or write a formula for common rules. A custom formula is optional for requirements that the built-in criteria do not cover.

Step-by-Step

  1. Open the spreadsheet and choose the target cells

    Open the Google Sheets file in your browser. Select the cells where entries should be controlled. To apply a rule to a column while leaving its heading out, select the cells below the heading—for example, C2:C1000 rather than all of column C. You can select a whole column if the rule should also apply to future rows, but consider whether the heading or notes in that column should be subject to it.

    For multiple non-adjacent ranges, create a rule for each range unless the ranges can be covered cleanly by one continuous selection. A rule applied to the wrong range is one of the most common reasons validation appears not to work.

  2. Open the data validation panel

    With the target cells selected, choose Data > Data validation from the menu bar. In the panel that opens, choose Add rule if no rule is already displayed for the selected cells. If a rule already applies, select it to review or edit it rather than adding a duplicate. The panel shows the range covered by the rule, which is worth checking before you make changes.

    You can also create a drop-down using Insert > Dropdown. For the other criteria and for editing a rule, Data > Data validation is the useful place to start.

  3. Confirm the range to apply the rule to

    Check the Apply to range field. It should contain the cells you selected, such as C2:C1000. You can edit the field if you need to include more rows or correct the selection. Use a clear, bounded range when practical: applying a rule to a whole column may be convenient, but it can also affect headings or unrelated content.

    If the cells are on another tab, include the tab name in the range as needed. For example, a range on a tab named Entries can be written as Entries!C2:C1000. If a tab name contains spaces, enclose it in single quotation marks, as in ‘Monthly Entries’!C2:C1000.

  4. Choose the kind of validation

    Open the Criteria menu and select the type of rule that matches the data. Common choices include Dropdown for options you enter directly, Dropdown (from a range) for options stored in cells, Date for date limits, Number for numeric limits, and text criteria such as valid email address. The available choices can vary as Google updates Sheets, so read the options shown in the panel.

    For a fixed list such as Not started, In progress, and Done, choose Dropdown and add each option as a separate item. For a list that may change, choose Dropdown (from a range) and point it to the cells containing the choices. For example, if your options are on the Lists tab in A2:A5, use that range as the source. Changes to the source list can then be reflected in the drop-down.

    For dates or numbers, choose the condition you need and enter the limit or limits. A date rule might allow dates on or after a particular date; a number rule might set a minimum and maximum. Check that dates in your source cells are actual date values, not text that merely looks like a date.

  5. Set the response to invalid entries

    Expand the advanced options if needed and look for the setting that controls what happens when someone enters a value outside the rule. Choose Reject the input when only approved values should be accepted. Choose Show a warning when you want to flag an unusual entry but let the person keep it. A warning is useful for flexible records; rejection is usually better for controlled fields such as status codes or required categories.

    For a drop-down, decide whether to show a help message and choose a display style such as a chip, arrow, or plain text if those options appear. A short message can explain the expected format, for example, Select one status from the list. The message should clarify the rule rather than repeat the column heading.

  6. Save the rule

    Review the range, criteria, and invalid-entry behavior, then click Done. The selected cells should now show the drop-down indicator or follow the limits you set. Click a cell and try the control to confirm the list or condition is available.

    To add a custom formula rule, choose the custom formula option in Criteria and enter a formula that returns TRUE for values you want to allow. For example, if the rule applies to A2:A100, this formula assumes the first cell in that range is A2 and allows whole-number values from 1 through 100: =AND(A2>=1,A2<=100). Keep A2 as a relative reference so Sheets can evaluate the rule for each row. Check the rule with a valid and an invalid value before relying on it.

  7. Test the rule with realistic entries

    Try an allowed value and then an entry that should fail. For a drop-down, select an option and check that a value outside the list is either rejected or produces the warning you chose. For a date or number rule, test values just inside and just outside the boundary. If the result is unexpected, reopen Data > Data validation and check the applied range and criteria.

    Also check any existing values in the selected cells. Adding a rule does not necessarily correct data that was already there. Review the cells for inconsistent spellings, dates stored as text, or values that fall outside the new limits. Correct those entries separately.

  8. Update or remove the rule when requirements change

    Select a cell covered by the rule and open Data > Data validation to edit it. If you need a longer list, update the source range or add the new list item, depending on how the rule was built. To remove validation, select the relevant cells, open the validation panel, select the rule, and use Remove rule or the corresponding remove control. Check the selection carefully before removing a rule so you do not clear validation from more cells than intended.

Common Mistakes

  • Applying the rule to the wrong cells: A rule for C2:C100 will not control entries in D2:D100. Check Apply to range and include the rows where people will enter data.
  • Choosing a warning when you need a firm restriction: Show a warning flags invalid entries but does not block them. Choose Reject the input if exceptions should not be accepted through normal cell entry.
  • Using a list source that excludes new options: If a drop-down points to Lists!A2:A5 and you add an item in A6, that item will not appear unless you expand the source range or otherwise include it.
  • Expecting validation to repair old data: A rule is not a cleanup tool. Check existing entries after applying it and correct any that do not meet the requirement.
  • Typing dates as text: A cell can look like a date while Sheets treats it as text. Use a consistent date format and verify that the value behaves like a date when you sort or compare it.
  • Replacing a rule accidentally: Adding another rule to the same cells can make the setup confusing. Review existing rules for the selected range before adding a new one.

Tips

  • Use a source tab for changing lists. Keep categories in one place and use Dropdown (from a range). This makes it easier to maintain a shared list than editing options in several separate rules.
  • Make the allowed choices obvious. Use short, distinct labels such as Open, Waiting, and Closed. Similar options such as In progress and In Progress can cause inconsistent records if people can type freely.
  • Choose the right level of strictness. Use rejection for values that must be standardized. Use a warning when unusual entries are acceptable but should be reviewed.
  • Include a useful instruction. A help message can say what format to enter, which choices are allowed, or whom to ask about an exception.
  • Keep the header outside the rule. Apply rules from the first data row downward, and extend the range when your sheet grows. If you expect frequent additions, apply the rule to a larger planned range.
  • Protect important structure separately. Data validation guides entries; it does not prevent someone with edit access from changing or deleting the rule. Use Google Sheets’ protected sheets and ranges feature when you need to restrict edits to parts of the file.
  • Remember that Excel has different menus. Google Sheets uses Data > Data validation. Excel has its own Data Validation controls and does not use the same sidebar or steps.

Troubleshooting

The drop-down arrow is missing. Select a cell you expect to be covered, then open Data > Data validation. Confirm that a rule exists and that Apply to range includes that cell. If the rule uses a drop-down, check the display-style setting; a different style may make the control less noticeable. Also check that the cell is not part of a merged range or an unusual layout that makes the control hard to see.

An invalid entry is accepted. Reopen the rule and verify that the invalid-data setting is Reject the input, not Show a warning. Check the criteria too: a rule that allows any number will not enforce a specific list. If data was pasted or brought in from another source, inspect both the value and whether the validation rule is still attached to the cell; paste operations can affect cell contents or formatting and may not behave like ordinary typing.

Valid values are being rejected. Compare the entered value with the allowed item exactly. Extra spaces, different punctuation, or a text value that differs from the source list can matter. For numeric or date rules, make sure the value is interpreted as a number or date and that the boundary is correct. If you use a custom formula, ensure its reference corresponds to the first cell in the Apply to range.

The new list item does not appear. Check the source range shown in the rule. Expand it to include the added item, or move the item into the existing range. If the source is on a different tab, make sure the tab name and cell addresses are correct.

The rule changes when you edit the sheet. Select a cell that should have the rule and inspect the validation panel. The rule may have been removed, applied only to some rows, or changed by copying cells from elsewhere. Reapply it to the intended range and test it. If several people edit the file, consider protecting the range or limiting who can edit the validation setup.

Frequently Asked Questions

Can I add a drop-down list in Google Sheets?
Yes. Select the cells and choose Data > Data validation, then choose Dropdown to enter options or Dropdown (from a range) to use values already in the sheet. You can also start from Insert > Dropdown.

Can data validation allow only dates within a range?
Yes. Choose Date as the criteria, select the appropriate condition, and set the date or endpoints. Test the rule using dates just before, on, and after the boundary.

Can I apply the same rule to a whole column?
Yes. Set the Apply to range field to the column or a suitable portion of it. If the first row is a heading, start below it so the heading is not treated as an entry.

Can I let people enter values that are not in a drop-down?
Yes, if the rule offers Show a warning, choose that instead of Reject the input. The sheet will flag an entry outside the list but allow it. For a strict list, use rejection.

Does adding validation change data already in the cells?
No. Review existing values separately. A new rule helps control future entries but does not automatically standardize or replace values that were already present.

Can I use data validation in Excel the same way?
Excel has a similar feature, but the menus and dialog are different. These instructions are specifically for Google Sheets in a browser; in Excel, use its Data Validation command on the Data tab.

Related

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.