In Excel for Microsoft 365 on Windows, select the cells to control, choose Data > Data Validation, set Allow to Custom, and enter a formula that returns TRUE for allowed entries and FALSE for disallowed ones. For example, with values in A2:A100 and A2 as the active cell, =AND(ISNUMBER(A2),A2=INT(A2),A2>=1,A2<=100) allows whole numbers from 1 through 100.
What a custom data validation formula does
A custom formula is a rule Excel checks when someone enters or changes a value in a cell. Unlike the built-in Whole number, Decimal, Date, or List options, a custom rule can combine several conditions or refer to other cells. The formula must produce a logical result: TRUE means the entry passes, while FALSE means Excel should reject or warn about it, depending on the error-alert settings.
Use a custom formula when the built-in choices cannot express your rule clearly. Typical uses include checking that an end date is not before a start date, preventing duplicate IDs, allowing only certain combinations of fields, or restricting text by length and pattern. For a simple rule such as “between 1 and 100,” the built-in Whole number setting may be easier to maintain; a formula is most useful when the rule needs more than one condition or a reference to another cell.
How to add the rule in Excel
- Select the cell or range where users will enter data. For a range, make sure the active cell is the range’s top-left cell.
- On the Windows ribbon, choose Data > Data Validation. In the dialog’s Settings tab, choose Custom from the Allow list.
- Enter the formula in the Formula box. Use a reference for the top-left cell of the selected range, not a reference fixed to one particular row.
- Open the Input Message tab if you want a prompt to appear when a user selects a cell. Use the Error Alert tab to choose whether invalid entries are stopped, warned about, or merely flagged.
- Select OK, then try both an allowed and a disallowed value in a test cell.
In Excel for Mac, the command is also available from Data > Data Validation, although the dialog’s layout can differ from Windows. The formula logic and reference principles are the same. If the rule is important, test it in the version and workbook environment where people will actually enter data.
Relative and absolute references matter
Excel adjusts relative references as a validation rule is applied to other cells. If the rule begins with A2 and applies to A2:A100, the next row is evaluated as if the reference were A3, then A4, and so on. This is why the top-left cell of the selected range matters: the formula is interpreted relative to that starting point.
Dollar signs lock a reference. For example, a rule applied down column B might need to compare each row with a fixed threshold in F1. The reference to the entry should move by row, while the threshold should remain fixed. Before using that pattern, confirm the active cell and selected range; a misplaced dollar sign can make every row compare against the wrong cell.
For rules covering a range, think through what the formula should mean in the first cell, then let Excel adjust it. If a rule is intended for D2:D50, a reference to D2 normally checks the value being entered in each row. A reference that is accidentally locked as $D$2 would keep checking D2 instead of the current row.
Practical custom formula examples
Whole numbers in a range: With entries in A2:A100 and A2 as the top-left cell, this formula accepts integers from 1 to 100. An entry of 42 passes; 12.5 and 101 fail.
=AND(ISNUMBER(A2),A2=INT(A2),A2>=1,A2<=100)
Unique names or codes: With names or codes in A2:A100 and A2 as the top-left cell, this rule allows a blank cell or a value that appears only once in the range. If the code entered in A2 also appears in A4, the count is at least two and the entry fails. Including the current cell in the checked range is intentional: one occurrence is valid; a second occurrence is not.
=OR(A2="",COUNTIF($A$2:$A$100,A2)=1)
Dates in a calendar year: With dates in C2:C100 and C2 as the top-left cell, this formula accepts dates from January 1 through December 31, 2026. A valid date such as July 4, 2026 passes; a date in 2027 does not. Excel stores dates as numbers, which is why the test uses numeric date boundaries. The way a date is typed or displayed can depend on regional settings.
=AND(ISNUMBER(C2),C2>=DATE(2026,1,1),C2<=DATE(2026,12,31))
End date on or after start date: Suppose start dates are in D2:D100 and end dates are in E2:E100. Apply the rule to E2:E100, with E2 active. This version permits either date cell to be blank while requiring both entered values to be valid Excel dates and the end date not to precede the start date. If your process requires both dates, remove the blank allowances and make the rule require two numeric dates.
=OR(E2="",D2="",AND(ISNUMBER(D2),ISNUMBER(E2),E2>=D2))
Combining conditions and allowing blanks
Use AND when every condition must be true, as with a whole number that must also fall within a minimum and maximum. Use OR when at least one condition is sufficient, such as accepting a blank or a valid entry. Parentheses make the intended grouping visible and help prevent a rule from accepting more cases than expected.
Decide explicitly whether blank cells are allowed. Excel’s Ignore blank option can affect how empty cells are treated, but a formula that includes a blank condition makes the intent easier to see and communicate. For a text rule, you might accept an empty cell until a user is ready to complete the record, then add a separate completeness check before the workbook is finalized. If the field must never be blank, do not include a blank-accepting condition.
Validation formulas are not limited to the cell being edited. A rule can check related cells on the same row, but it should be designed around the active cell and applied range. Consider a small test range first when a formula combines multiple fields; a rule that is logically correct for one row can still behave unexpectedly if its references shift incorrectly.
Testing and troubleshooting a rule
Test both sides of the rule: enter a value that should pass and one that should fail. Also test a blank if blanks matter, and test a later row in the range to check that relative references adjust properly. A formula can look plausible while silently referring to the wrong row, especially when a range was selected with a different active cell.
- Unexpected rejection: Check whether the formula returns TRUE for the intended example and whether text that looks numeric is actually stored as text.
- Unexpected acceptance: Check AND and OR grouping, locked references, and whether the rule was applied to the full intended range.
- Dates fail: Confirm that the cell contains a real Excel date value rather than text that only looks like a date.
- Formula entry issue: Check the selected Allow option and the formula’s starting reference. Regional settings may use different list separators in formulas, even though examples here use standard comma separators.
The Error Alert setting changes the user experience. A Stop alert blocks an invalid value entered directly and requires a valid replacement or cancellation. Warning lets the user choose whether to keep an invalid entry, while Information informs the user but does not enforce the rule as strictly. A custom error message can explain the requirement, such as “Enter a whole number from 1 to 100.”
Important limits of data validation
Validation is a useful entry safeguard, not a guarantee that every value in a workbook is valid. It does not automatically repair old data that was already present before the rule was added. Review existing values separately, for example by testing the rule against them or using a filter or conditional formatting to find exceptions.
Copying and pasting can also bring data into cells without the same protection users encounter when typing directly. Pasted content, fill operations, imported data, formulas, and macros may create values that do not meet the intended rule. Excel may show an invalid-data circle in some situations; use Data > Data Validation > Circle Invalid Data when that command is available to locate entries that violate the rule. Recheck data after imports or bulk edits.
For critical records, treat validation as one layer of control. Protecting the sheet can reduce accidental changes to formulas or settings, but it does not turn validation into a security boundary. Keep a clean source of truth, review important entries, and use a separate audit or reconciliation step when incorrect values could have serious consequences.
When to use a different validation method
Choose the built-in validation types when they match the requirement: List for a controlled set of choices, Whole number or Decimal for numeric limits, and Date for date boundaries. These settings are easier for another workbook editor to understand at a glance. A custom formula is appropriate when the condition depends on multiple tests or other cells.
If users need a guided form, conditional choices, or a process that must work reliably across imported or pasted data, consider a structured input form or a separate review process. Conditional formatting can highlight questionable values, but it does not prevent entry. Formulas in helper columns can audit existing records, while validation can discourage invalid new entries. The right choice depends on whether the goal is guidance, prevention during typing, or later detection.
Frequently Asked Questions
Does a custom validation formula need to start with an equals sign?
Yes. Enter it as a normal Excel formula beginning with =, and make sure it returns TRUE for values that should be accepted and FALSE for those that should not.
Why does my formula refer to the wrong row?
Excel evaluates a range rule relative to its top-left cell. Select the intended range with the correct starting cell active, and use relative references for the entry on each row. Lock only references that should stay fixed.
Can a custom formula check another cell?
Yes. For example, an end-date rule can compare the entry with the start date in the same row. Verify that references shift correctly for every row in the applied range.
Will data validation remove invalid values already in my sheet?
No. Adding a rule does not clean up existing entries. Check the current data separately, and retest after pasting, importing, or filling cells because those actions can bypass the normal typing prompt.
Can I use a custom formula to make a dropdown list?
A custom formula is a rule that accepts or rejects an entry; it does not itself display a dropdown. Use the List validation type for a dropdown, or design a separate list-based solution if the choices depend on other values.
Related
- Hide Column in Tableau: Steps and Tips
- Group Rows in Smartsheet: Sheets, Reports, and Views
- Numbers Text to Columns: Split Your Data Correctly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IF Function in Excel: Examples and Practical UsesIn Excel, the IF function checks whether a condition is true and returns one result if it is,…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas…
- Concatenate an Excel List: Join Values With DelimitersTo concatenate an Excel list into one cell, use TEXTJOIN: with the values in A2:A6, enter =TEXTJOIN(", ",TRUE,A2:A6).…
- Excel Attendance List: Setup, Formulas, and Practical TipsAn Excel attendance list is a worksheet that records who attended each class, meeting, or event and makes…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
- Text Formula Examples in Excel: Clean, Extract, CombineExcel text formulas let you combine words, extract part of a value, standardize capitalization, and clean imported text.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.