Google Sheets Data Validation Custom Formula Guide

In Google Sheets, choose the cells, open Data > Data validation, select Custom formula is under Criteria, and enter a formula that returns TRUE for allowed entries. For example, to permit numbers from 1 to 100 in column B, use =AND(ISNUMBER(B2),B2>=1,B2<=100) when the first cell in the selected range is B2; then choose whether invalid entries are rejected or merely flagged.

What custom-formula validation does

Data validation is a rule attached to a cell or range. It can require a particular kind of entry, restrict values to a list, or use a custom formula to test a condition. Custom formulas are useful when the built-in criteria do not express the rule you need—for example, when a value must be unique, must appear in another range, or must agree with a related cell.

The key is the formula’s result: TRUE means the entry satisfies the rule, while FALSE means it does not. The formula is evaluated for each cell in the applied range, with references adjusted according to their relative or absolute status. A custom formula does not automatically change a value, clean up old entries, or make a sheet secure; it helps control what editors enter going forward.

Set up a custom formula rule

In Google Sheets in a web browser, select the cells to validate and use Data > Data validation. In the rule panel, check that Apply to range shows the intended cells, open Criteria, and choose Custom formula is. Enter the formula, then decide what should happen when a cell fails the test. You can add help text to explain the requirement to people entering data.

Select the target range, such as B2:B100. The first cell in this range matters because the formula should usually refer to B2.
Open Data > Data validation and choose Add rule if you are creating a new rule.
Under Criteria, select Custom formula is and enter a formula beginning with an equals sign.
Choose Reject input to prevent an invalid entry, or Show warning to allow it while displaying a warning.
Select Done, then try both a valid and an invalid value in a test cell.

Menu wording or panel presentation can change as Google updates Sheets, but the data-validation controls are reached through the Data menu. If your spreadsheet uses a locale that expects semicolons between formula arguments, the formula separator may need to match that locale; the examples here use commas.

How references and ranges work

Write the custom formula as if it is being evaluated for the top-left cell of the applied range. If the range is B2:B100, a reference to B2 is relative: it becomes B3 for the next row, then B4, and so on. This is usually what you want when each row is checked independently. If you instead apply a rule to B2:B100 but refer to B1, the rule tests a different cell than many editors expect.

Dollar signs lock a reference. In $F$2:$F$8, both the column and row stay fixed when the rule is evaluated for another row. By contrast, B2 can move with the cell being checked. A common design is therefore a relative reference for the entry and an absolute reference for a supporting list or comparison range.

Do not use a whole-column reference casually in a complex rule. A limited range such as $A$2:$A$1000 can make the rule easier to understand and may reduce unnecessary calculation. Make sure the selected Apply to range and the row numbers in the formula agree.

Practical custom-formula examples

Restrict a number to a range. Assume the rule applies to B2:B100 and B2 is the first cell being checked. The formula returns TRUE for 42 because 42 is a number and falls between 1 and 100; it returns FALSE for 0 or text.

=AND(ISNUMBER(B2),B2>=1,B2<=100)

This rule does not allow blanks because ISNUMBER(B2) is false for an empty cell. If blank entries should be allowed until a value is supplied, include an explicit blank condition. Assume the same B2:B100 range; for an empty B2 the result is TRUE, and for B2 equal to 42 it is also TRUE.

=OR(B2="",AND(ISNUMBER(B2),B2>=1,B2<=100))

Prevent duplicate IDs. Assume IDs are entered in A2:A100 and the rule is applied to that range. For A2 containing ID-104, if ID-104 appears once in A2:A100, COUNTIF returns 1 and the formula returns TRUE. If it appears twice, the formula returns FALSE. The blank check permits empty cells.

=OR(A2="",COUNTIF($A$2:$A$100,A2)=1)

Because COUNTIF is not case-sensitive, this treats identifiers such as ab12 and AB12 as the same value for duplicate checking. Also, validation is not a cleanup tool: existing duplicate values may remain after you add the rule, so review existing data separately.

Require a value from another range. Assume entries go in C2:C100, and the approved words are in F2:F8. If C2 contains Approved and that word appears in F2:F8, the formula returns TRUE; if C2 contains an unlisted word, it returns FALSE. An empty entry is allowed by this version.

=OR(C2="",COUNTIF($F$2:$F$8,C2)>0)

A dropdown sourced from F2:F8 may be clearer for users when the goal is simply to choose from a short list. The formula approach is more adaptable when the allowed value also has to satisfy another condition.

Check that an end date is not before a start date. Assume start dates are in C2:C100, end dates are in D2:D100, and the rule is applied to D2:D100. If C2 is April 10, 2026 and D2 is April 15, 2026, the formula returns TRUE; if D2 is April 9, it returns FALSE. This version permits D2 to remain blank.

=OR(D2="",AND(ISNUMBER(C2),ISNUMBER(D2),D2>=C2))

Dates in Sheets are stored as numbers, so ISNUMBER helps ensure that the comparison is between date values rather than arbitrary text. Format the cells as dates as well; formatting changes how a value looks, while validation determines what values are accepted.

Choose between a custom rule and a dropdown

Custom formulas are not always the simplest choice. If people should select from a known set of categories, use a dropdown or a dropdown sourced from a range. A dropdown makes the permitted choices visible and reduces spelling differences such as Pending versus pending. A custom formula is preferable when acceptance depends on a calculation, a relationship to other cells, or a condition that is awkward to represent as a list.

Use the built-in number, date, text, checkbox, or dropdown criteria when they already express the requirement. Built-in criteria are easier for another editor to inspect and maintain. A custom formula earns its extra complexity when it adds a meaningful rule, such as checking uniqueness across a column or ensuring that one date follows another.

For rules that should only highlight a problem rather than stop an entry, conditional formatting may be a better fit. It changes the appearance of cells according to a condition but does not itself enforce valid input. Data validation controls entry behavior; conditional formatting helps people notice patterns or exceptions. They can also be used together.

Common mistakes and troubleshooting

Wrong starting row: If the range starts at A2, a row-by-row formula should normally refer to A2, not A1 or A3.
Missing dollar signs: Lock a list or comparison range, such as $F$2:$F$8, so it does not shift as the rule moves down the column.
Unexpected blank behavior: Decide whether blanks should pass. Add a condition such as A2=”” only when blanks are intentionally permitted.
Formula errors: Check for misspelled function names, unmatched parentheses, and references to the wrong range. A formula that errors cannot reliably approve the entry.
Old values remain: Adding a validation rule does not necessarily remove or repair values already in the range. Check existing entries and correct them separately.
Rule conflicts: Review the applied range and its existing validation rules if behavior differs between neighboring cells. A rule may cover more or fewer cells than intended.

Test the rule with a clearly acceptable value, a clearly unacceptable value, and a blank if blanks matter. For rules involving other columns, test a row where the related cells are filled and another where one is empty. These checks reveal reference mistakes more quickly than inspecting the formula alone.

Validation is not a security boundary

Data validation is designed to guide spreadsheet editing, not to protect sensitive information or guarantee that every value in a workbook is trustworthy. Someone with editing permission may be able to change or remove the rule, and data can arrive through copying, imports, or other workflows that deserve a separate review. If the data is important, periodically check it with filters, summary formulas, or a dedicated audit process rather than assuming that a validation rule proves every cell is clean.

Use Reject input when an incorrect value should not be accepted through ordinary editing. Show warning is useful when exceptions are legitimate but should be noticed. Add explanatory help text that says what is expected and, where useful, gives an example. A clear message can prevent repeated errors better than a complicated formula with no explanation.

Frequently Asked Questions

Does a custom formula need to return TRUE or FALSE?
Yes. TRUE indicates that the entry passes the rule, and FALSE indicates that it does not. Build the formula around the condition you want to accept, then test both outcomes in the sheet.

Can I use a custom formula to allow only unique values?
Yes. Use COUNTIF over the full target range and compare the count with 1, while accounting for blanks if they should be allowed. The example above checks A2:A100; adjust the range and first-cell reference to match your sheet.

Why does my formula work in one row but not another?
The most common cause is a relative reference or Apply to range that starts on a different row than expected. Confirm that the formula refers to the first cell in the range and that supporting ranges are locked with dollar signs where needed.

Can a custom formula create a dropdown?
No. A custom formula validates a value; it does not display a choice menu. Use dropdown criteria when users should pick from visible options, and use a formula when the permitted value depends on a condition.

Can I use Google Sheets custom formulas in Excel?
The validation concepts are similar, and many formulas use functions available in both programs, but the menus and feature behavior differ. This article’s setup steps are for Google Sheets in a web browser; Excel has its own Data Validation controls and should be configured separately.

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.