Data Validation in Excel: Rules, Lists, and Setup Tips

How-to basics · Updated November 18, 2026

Data validation in Microsoft Excel controls what users can enter in selected cells. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Tools > Data Validation, then set a rule such as a drop-down list, a number range, a date limit, or a custom formula.

What data validation does

Data validation is a cell-level rule for guiding and checking input. It can restrict entries to a permitted set or range, show a message when someone selects a cell, and display an alert when an entry does not meet the rule. For example, a status column might accept only Open, In progress, or Done, while a quantity column might accept whole numbers from 1 to 50.

The feature is useful in forms, trackers, shared workbooks, and data-entry sheets because it can reduce inconsistent values. If people type a department name in different ways, for instance, a list can standardize entries as Finance, Operations, or Sales. Consistent values make filtering, sorting, summaries, and later analysis more dependable.

Validation is not the same as formatting. Number formatting changes how an accepted value appears; it does not necessarily limit what can be entered. Conditional formatting highlights values according to a rule but does not ordinarily stop entry. Data validation is the feature to use when you want Excel to check an entry at the point of input.

How to add a validation rule

  1. Select the cell or range to control. To apply one rule to a column of future entries, select the intended input cells, such as C2:C200, rather than the header.
  2. Choose Data > Data Tools > Data Validation. In the dialog box, use the Settings tab to choose the validation criteria and configure its limits or source.
  3. Optionally use Input Message to provide guidance when a user selects a cell, and Error Alert to choose what happens when an entry fails the rule.
  4. Select OK, then try both an allowed and a disallowed entry to confirm the rule behaves as intended.

In Excel for Mac, the command is also available from the Data tab as Data Validation, though the dialog layout and ribbon presentation can differ. Excel for the web also provides data-validation controls, but the available options and editing experience may differ from desktop Excel; use the Windows desktop app if a particular rule or dialog option is unavailable in the web version.

To change or remove a rule, select the affected cells and reopen Data Validation. Edit the criteria or choose Clear All in the dialog to remove the validation settings. Clearing a rule does not clear the cell contents.

Types of rules you can choose

The Allow menu in the Settings tab provides several common criteria. Which option is appropriate depends on what the cell is meant to contain:

  • Whole number: Limit entries to integers, optionally between minimum and maximum values. This suits item counts or quantities that should not contain fractions.
  • Decimal: Permit numeric values with decimal places, such as a measured weight or percentage. Choose a comparison such as between, greater than, or less than.
  • List: Restrict entries to values in a source list and, if selected, show an in-cell drop-down arrow.
  • Date or Time: Set acceptable date or time boundaries, such as dates within a reporting period or times during a shift.
  • Text length: Limit the number of characters in a cell. This can help keep short codes or identifiers within a defined length.
  • Custom: Use a formula that evaluates to TRUE for acceptable entries and FALSE for entries to reject.

Most criteria let you choose a condition such as equal to, less than, greater than, or between. For example, a score field may allow decimals between 0 and 100, while an order-count field may require whole numbers between 1 and 500. Consider whether zero, negative values, blanks, or boundary values should be allowed before setting the limits.

Creating a drop-down list

A drop-down list is one of the most common uses of data validation. Put the approved choices in worksheet cells, select the cells where users will make a choice, open Data Validation, choose List under Allow, and set Source to the range containing the choices. For example, if A2:A4 contains Open, In progress, and Done, the source can be =$A$2:$A$4. Select In-cell dropdown if you want the arrow to appear in the validated cells.

A range is usually easier to maintain than typing choices directly into Source. If you type choices into the box, Excel separates them using the list separator set for the computer’s regional settings; in many installations that is a comma, but it can differ. A worksheet range avoids separator confusion and makes it easier to add, remove, or correct options.

For lists used in several places, consider defining a named range. Select the source cells, use Formulas > Define Name, give the range a simple name such as StatusOptions, and use =StatusOptions as the list source. If the list must grow over time, plan for that growth: a fixed range will not automatically include items typed below its last cell. A named range or a carefully designed expanding source can make maintenance more reliable. Keep the source list tidy, without unintended blank cells or duplicate choices.

The drop-down arrow is a convenience, not a requirement for validation. If In-cell dropdown is not selected, Excel can still check entries against the list when users type them, depending on the error-alert settings.

Custom rules and formula-based checks

Choose Custom when the built-in criteria do not express the rule you need. The formula must return TRUE for an acceptable value. Excel evaluates the formula relative to the top-left cell in the selected range, so the starting cell and use of dollar signs matter.

For a range B2:B100 where each entry must be a number from 0 through 100, select B2:B100 and use this custom validation formula: =AND(B2>=0,B2<=100). The formula checks the value in each row as Excel applies the rule through the range. An entry of 82 passes because it is within the limits; 105 fails. If the selected range begins in a different cell, adjust the reference to match its top-left cell.

Custom formulas can also check relationships between cells. For example, a rule can require an end date to be on or after a start date, or require a value in one column when a related cell is filled. Design these rules carefully and test both valid and invalid cases. A formula that refers to the wrong row can appear to work in one cell but check a neighboring row when applied to a larger range.

Input messages and error alerts

The Input Message tab lets you show a short instruction when a user selects a validated cell. A message such as Enter a whole number from 1 to 50 explains the expected input before an error occurs. It is especially helpful when the allowed values are not obvious from the column heading alone.

On the Error Alert tab, selecting Show error alert after invalid data is entered determines whether Excel displays an alert when someone enters a value that fails the rule. The alert style affects how strongly Excel responds:

  • Stop rejects an invalid value entered normally and requires the user to correct it or cancel.
  • Warning lets the user choose whether to keep an invalid value or go back and correct it.
  • Information informs the user but allows the value to be retained.

Use Stop when the rule is intended to be mandatory. Warning or Information may suit situations where exceptions are legitimate and should be recorded rather than blocked. Clear error text should say what to enter or how to correct the problem, rather than simply saying that the entry is invalid.

Common problems and limitations

Creating a rule does not automatically repair or remove values already in the selected cells. If a range contains old entries, Excel can leave those entries in place even if they do not meet the new criteria. To find existing problems, select the relevant cells, open Data Validation, and use Circle Invalid Data if that option is available. Review the marked entries and correct them; the circles are a review aid, not a replacement for checking the data.

Validation is also not a security boundary. It helps guide ordinary data entry, but it should not be treated as protection against deliberate changes or every way data can enter a workbook. Pasting, importing, editing rules, or using other tools can lead to values that do not follow the intended process. Copying cells can also affect destination formatting and validation. For important records, check the data after importing or pasting, protect workbook structure where appropriate, and use additional review or controls when the consequences of bad data are significant.

Another frequent source of confusion is blanks. The Ignore blank setting affects how blank cells are treated by the rule; it does not require users to fill in every cell. If a field is mandatory, communicate that requirement and check for missing entries separately, for example with a review step or a formula-based check. A list rule also does not automatically make every related field required.

If a drop-down list is missing or shows unexpected options, check that the validated cells have the intended rule, that the source range contains the correct items, and that the source has not moved or been replaced. Also check whether the workbook is being edited in a version of Excel with different validation controls. Test the workbook using the same kind of entry or paste operation that users are likely to use.

Choosing a practical validation design

A useful rule should prevent common mistakes without making normal work unnecessarily difficult. Before setting it up, identify the valid values, decide whether exceptions should be blocked or merely flagged, and determine how the list or limits will be maintained. A fixed list is sensible for a small set of stable categories; a cell-based source is easier to update when the choices change.

For a shared entry sheet, combine validation with clear headings, examples, and sensible cell formatting. Keep input cells visually distinct from calculated cells, and avoid allowing users to type free-form alternatives where a controlled list is important. For columns that receive data from multiple sources, plan an additional review because validation alone may not catch every invalid value.

Finally, test edge cases: the smallest allowed value, the largest allowed value, a value just outside each limit, a blank, and an entry with the wrong type. For a list, test an approved item and an unapproved one. This simple check catches mistaken boundaries, references, and alert settings before the workbook is shared.

Frequently Asked Questions

How do I make a drop-down list in Excel?
Select the destination cells, choose Data > Data Tools > Data Validation, set Allow to List, and enter a source range or the permitted options. Select In-cell dropdown if you want an arrow in the cells.

Can data validation allow only numbers within a range?
Yes. Choose Whole number or Decimal, select a condition such as between, and enter the lower and upper limits. Use Whole number for counts that must not contain fractions.

Does data validation stop invalid values from being pasted?
Not reliably. Validation is intended to guide and check normal entry, not guarantee that every pasted, imported, or otherwise inserted value meets the rule. Review data after those operations.

How can I find invalid values that are already in a range?
Select the range, open Data Validation, and use Circle Invalid Data if available. Excel marks entries that do not meet the current rule so you can review and correct them.

How do I remove data validation without deleting values?
Select the validated cells, open Data Validation, and choose Clear All in the dialog. This removes the validation settings while leaving the existing cell contents in place.

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.