Excel Data Validation: Add Criteria and Rules

How-to basics · Updated November 19, 2026

To add criteria in Excel Data Validation, select the cells, go to Data > Data Validation > Data Validation, choose a rule under Allow on the Settings tab, and set its conditions. For example, choose Whole number and set a minimum and maximum to restrict entries to a range; choose List to offer a dropdown of approved choices.

What data validation criteria do

Data validation puts conditions on what someone can enter in selected cells. A rule can limit entries to certain numbers, dates, times, text lengths, list items, or values that satisfy a formula. When a user types an entry that conflicts with the rule, Excel can show a message and either block the entry or let the user proceed, depending on the error-alert setting.

Criteria are useful for making spreadsheets more consistent. If a Status column should contain only Open, In progress, or Closed, a list rule can prevent variations such as “In Progress” and “In progress ” from accumulating. A numeric rule can keep a quantity within a permitted range, while a date rule can restrict appointments to a particular period.

Validation is an input aid, not a guarantee that every cell in a workbook contains valid data. Pasting values, filling cells, or importing content can introduce entries that do not follow a rule. It also does not protect sensitive information or replace worksheet protection. Treat it as one part of a well-designed workbook.

How to add a criterion in Excel for Microsoft 365

In Excel for Microsoft 365 on Windows, add a rule through the Data Validation dialog:

  1. Select the cell or range where entries should be controlled. Select a whole column only if you intend the rule to apply to all of it.
  2. Open Data > Data Validation > Data Validation. The command is in the Data Tools area of the Data tab.
  3. On the Settings tab, choose a validation category from the Allow list.
  4. Choose a condition in the Data list, such as between, greater than, or equal to, then enter the required limit or values.
  5. Optionally configure the Input Message and Error Alert tabs, then select OK.

The dialog’s fields change according to the selected Allow category. For a whole-number rule, for example, Excel asks for a comparison condition and one or two numbers. For a date rule, the limits should be valid dates. You can type a date directly or use a cell reference, depending on how you want the rule to behave as the workbook changes.

To change a rule later, select a cell that has it and reopen Data Validation. If you select multiple cells with different rules, Excel may not be able to show one shared set of settings. Check a representative cell, or apply the intended rule to the full target range again.

Choose the right Allow category

The best category depends on the kind of entry you need to control. Excel’s Allow choices cover several common situations:

  • Whole number: accepts integer values, such as a count of items. You can set a minimum, maximum, or other comparison.
  • Decimal: accepts numeric values that may include a fractional part, such as a measurement or percentage.
  • List: provides a dropdown of allowed entries, or checks a typed entry against the list.
  • Date and Time: limit entries to dates or times that meet the selected condition.
  • Text length: restricts the number of characters entered in a cell.
  • Custom: evaluates a formula to decide whether an entry is acceptable.

For numeric, date, time, and text-length criteria, choose a condition from the Data menu. Between and not between require two limits; conditions such as greater than or equal to need one. Select the comparison that matches the actual rule, rather than entering an approximate limit that could allow unwanted values.

For instance, if a form accepts an age from 18 through 99, select Whole number, choose between, and enter 18 and 99. If decimal measurements from 0 through 1 are acceptable, use Decimal instead. Choosing Whole number in the second case would reject legitimate entries such as 0.75.

Create a dropdown list criterion

A list rule is often the clearest way to standardize categories. Select the target cells, open the Data Validation dialog, choose List under Allow, and supply the choices in Source. If you want the dropdown arrow to appear in the cells, leave In-cell dropdown selected. The entries can be typed into Source as a comma-separated list, or the Source can refer to cells containing the approved options.

For example, a small fixed list might contain Draft, Review, and Approved. For a longer list, keep the choices in worksheet cells and point the rule to that range. A cell range is easier to maintain: editing a choice in the source list updates the options presented by the validation rule. If the list is on another worksheet, use a defined name for the source rather than assuming a direct cross-sheet reference will work in the Source box.

Consider where to keep the source values and how the list will grow. A fixed reference to A2:A4 will not automatically include a new choice entered in A5. You can update the validation source when the list changes, or set up a defined name that refers to a suitably sized or expanding range. Keep the source values free of accidental blanks and duplicates if those would confuse users.

The dropdown is a convenience, not the only permitted way to enter a value. A user can type into a validated cell, so the error alert matters. If consistent selection is important, enable the alert and choose Stop to reject entries that are not in the list.

Use a custom formula for more specific rules

Choose Custom when a built-in category cannot express the condition you need. A custom validation formula must return TRUE for an allowed entry and FALSE for an entry that should be rejected. Excel evaluates the formula relative to the active cell in the selected range, so relative and absolute references affect how the rule applies across cells.

For example, suppose you select B2:B100 and want every entry to be a whole number from 1 through 100. With B2 as the active cell in that selection, choose Custom and enter =AND(B2>=1,B2<=100). For B2 containing 25, the condition is TRUE and the entry is allowed; for B2 containing 120, it is FALSE and the entry is rejected. The relative reference adjusts for each row in the selected range.

Custom rules can check relationships between cells as well as the value being entered. A rule might require one cell to be filled only when another cell has a particular value, or require entries in a column to be unique. These rules need careful testing: a reference that shifts in the wrong direction can make a rule behave correctly in the first row but incorrectly elsewhere. Try valid and invalid examples in the first, middle, and last rows before distributing the workbook.

Configure input messages and error alerts

The Input Message tab lets you show a short instruction when a user selects a validated cell. It is useful for explaining the expected format, such as “Enter a whole number from 1 to 100.” This message does not enforce the rule; the Settings tab and Error Alert control acceptance.

On the Error Alert tab, you can set a title, message, and style. Stop blocks an invalid entry made through normal cell editing. Warning and Information alerts allow the user to proceed after acknowledging the message, so they are less strict. If the alert is disabled, the criterion may still exist, but users receive no warning when entering an invalid value by typing.

Use concise messages that explain the correction, not just the error. “Choose Draft, Review, or Approved” is more helpful than “Invalid entry.” If users need to understand a boundary, include it in the message. For example, say “Enter a date from 1 January through 31 March” rather than simply “Date not allowed.”

Check existing entries and troubleshoot rules

Adding validation does not automatically clean up or certify values already in the selected cells. After applying a rule to a range with existing content, review those entries. Excel’s Data Validation menu includes Circle Invalid Data, which can mark values that fail the selected validation rules; the command is useful for finding issues, but you still need to decide how to correct them.

If a rule seems not to work, check these common causes:

  • The wrong cells are selected. Confirm the rule applies to the cells where users will enter data, not just a heading or neighboring range.
  • The alert is not set to Stop. Warning and Information alerts let users continue, and a disabled alert does not visibly block a typed entry.
  • Values were pasted or filled. Pasting can bring in values that bypass the normal validation prompt. Review data after bulk edits or imports.
  • A list source is incomplete. A fixed range may omit newer choices, or a source range may contain blanks or unintended values.
  • A custom formula uses the wrong reference. Check the active cell and whether references should be relative or fixed.
  • Numbers or dates are stored in unexpected ways. Text that looks like a number or date may not behave like a numeric or date value in a rule.

To remove a rule, select the affected cells, open the Data Validation dialog, and choose Clear All. This removes validation settings from the selection; it does not clear the cell contents. If the range contains different rules, verify which cells are selected before clearing.

Excel on Mac and Excel for the web

In Excel for Microsoft 365 on Mac, select the cells and use Data > Data Validation; the dialog and available controls may be arranged differently from the Windows version. The underlying idea is the same: choose an Allow category, set its criteria, and configure messages if needed. Keyboard shortcuts and ribbon navigation can differ between Mac and Windows, so the menu path is the more dependable instruction.

Excel for the web also provides data-validation controls, including dropdown-related options, but the interface and some editing workflows can differ from desktop Excel. If a rule is difficult to create or inspect in the browser, open the workbook in the desktop app and use the Data Validation dialog. When sharing a workbook across platforms, check the rule in the version your users will use, especially if it relies on a custom formula or a defined list name.

Frequently Asked Questions

Can I add more than one criterion to a cell?
Yes. A Custom rule can combine conditions in one formula, such as requiring a value to fall within a range and meet another test. Built-in categories generally offer one selected comparison condition at a time; use a custom rule when the combined logic is not available directly.

Can Excel show a dropdown but still allow other entries?
Yes. A list rule can display the dropdown while an alert style such as Warning or Information lets a user continue with a value outside the list. If only listed values should be accepted through ordinary typing, use a Stop alert and test the rule.

Does validation change data that is already in the cells?
No. It controls future entries and does not automatically replace or remove existing values. Use Circle Invalid Data or review the range manually, then correct any entries that do not meet the new rule.

Why can pasted data ignore validation?
Some paste, fill, and import actions do not trigger the same check as typing directly into a validated cell. Validation is not a security boundary. Review data after bulk changes, and consider additional checks when accuracy is critical.

How do I apply the same rule to more cells?
Select the additional range and create or adjust the rule there. You can also copy a validated cell and paste its validation to other cells using Paste Special options, but verify the resulting ranges and formula references afterward. Relative references in custom rules may change as they are applied to new cells.

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.