Excel Data Validation: Lists, Rules, and Setup

How-to basics · Updated November 18, 2026

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

What data validation does

Data validation is a cell-level rule for limiting or guiding input. It can make a cell accept only a whole number within a range, a date within specified limits, text of a certain length, an item from a list, or a value that satisfies a formula. A rule can also show a short instruction when someone selects a cell and an alert when they try to enter something the rule rejects.

It is useful when a workbook will be filled in by multiple people or used repeatedly for records such as survey responses, inventory, schedules, and expense categories. Consistent entries make later sorting, filtering, formulas, and summaries more dependable. For example, a dropdown containing “Open,” “In progress,” and “Complete” avoids variations such as “In-Progress” or “Done” that can split a report into unintended categories.

Validation is a data-entry aid, not a guarantee that every cell in a workbook is correct. It is important to understand what it checks and what can get around it before relying on it for a process.

Validation types and when to use them

On the Settings tab of the Data Validation dialog, the Allow menu offers several common rule types. The best choice depends on the data being entered:

  • Whole number: Accepts integer values, such as a quantity from 1 through 500.
  • Decimal: Allows numeric values with decimal places, useful for measurements or amounts within a permitted range.
  • List: Provides a dropdown of approved choices, such as department names or order statuses.
  • Date or Time: Restricts entries to a date or time range, such as dates in a reporting period.
  • Text length: Limits the number of characters, for example to keep a short code within a defined length.
  • Custom: Uses a formula that returns TRUE for an allowed entry and FALSE for a rejected one. This can express rules that the other types cannot.

Each type can use a condition such as between, equal to, greater than, or less than, where applicable. The “Ignore blank” option determines whether blank cells are allowed under the rule; it does not force a user to complete a cell. If completion is essential, consider a custom rule or a separate completeness check, and test the behavior you need.

Create a dropdown list

A list validation is often the most visible use of the feature. First decide whether the choices should be typed into the rule or maintained in worksheet cells. A cell range is easier to update and review when the list has several items or changes over time.

  1. Select the cells that should have the dropdown.
  2. Choose Data > Data Validation > Data Validation.
  3. On Settings, choose List from Allow.
  4. Enter the choices in Source, separated by commas, or select the cells containing the choices. Keep In-cell dropdown selected.
  5. Choose OK, then test the arrow and try both an allowed and an unlisted value.

For a short, stable set of options, typing values such as Open,In progress,Complete into Source is convenient. For a longer list, keep the options in a dedicated range and point Source to that range. A range on the same worksheet is straightforward. If the list is on another worksheet, Excel’s validation Source field generally cannot use a direct reference to that other sheet; define a name for the list first, using Formulas > Define Name or Formulas > Name Manager, then enter the name in Source, preceded by an equals sign.

For changing choices, a named range can make maintenance clearer, and a table-backed or spill-based list can be configured to expand as items are added. Those setups need to be designed and tested so the defined name points to the intended items, not headings or blank cells. Avoid putting the source list in the cells users are expected to edit: the list and the entry area should have distinct roles.

Input messages and error alerts

The Input Message tab lets you add a title and a short note that appears when a validated cell is selected. Use it to explain the expected format or units, such as “Enter a date from the current quarter” or “Choose the team responsible for this request.” A clear note can prevent mistakes before an alert is needed.

On the Error Alert tab, you can choose an alert style and write a message. Stop rejects an invalid value entered normally. Warning and Information display a message but allow the user to proceed, so they are better for guidance than strict enforcement. You can also turn off the error alert while retaining the rule, but users may then enter values that do not meet it. Keep messages specific: say what the acceptable value is, rather than simply saying the entry is invalid.

Use custom formulas for more specific rules

Choose Custom in the Allow menu when the rule depends on another cell, requires a combination of conditions, or needs a check such as uniqueness. The formula is evaluated for the cells to which the validation rule is applied. Write its references as if the formula were being evaluated in the top-left cell of the selected range; relative references then adjust down or across the range. Use dollar signs where a row or column should remain fixed.

For example, suppose B2:B100 contains requested quantities and C2:C100 contains each row’s available stock. Select B2:B100 and choose Allow: Custom. The formula below allows a numeric request no greater than the stock in the same row:

With requested quantities in B2:B100 and available stock in C2:C100, use =AND(ISNUMBER(B2),B2<=$C2). The formula checks that B2 is numeric and no greater than C2; the relative row references adjust for each cell in the selected range. The fixed column in $C2 keeps the stock check in column C if the rule is later applied across columns.

Another example is preventing duplicate IDs. With IDs entered in A2:A100, select that range and use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)=1). It allows a nonblank value only when that value appears once in the range, including the current cell. This rule is useful for reducing accidental duplicates during entry, but it does not repair duplicates that were already present before the rule was added. It can also be bypassed by pasting, so review the finished data when uniqueness matters.

Find, change, and test existing rules

To inspect a rule, select one of its cells and open Data > Data Validation > Data Validation. If several selected cells have different validation settings, the dialog may not show a single rule that applies to all of them; inspect cells individually or select a smaller range. Changing a rule while a range is selected can apply that rule to the selection, so check the selected cells before confirming.

To locate validated cells, use Home > Find & Select > Data Validation. Excel selects cells with validation on the active worksheet, which can help when auditing an unfamiliar workbook. To identify values that already violate a rule, choose Data > Data Validation > Circle Invalid Data. Excel marks invalid entries with circles; after correcting them, use Clear Validation Circles in the same menu area to remove the markings. These checks are especially useful after importing or pasting data.

Test more than the obvious valid case. Try a value at each boundary, one just outside the boundary, a blank, and a value entered in the way users are likely to enter data. For a dropdown, test selecting an item and typing an unlisted item. This reveals whether the rule and alert settings match the intended workflow.

Common problems and important limits

A frequent surprise is that validation is not a full data-cleaning or access-control system. It may not stop invalid content from being copied or pasted into a validated cell, filled into it, or introduced by a formula or external process. A validation rule added after data exists also does not automatically correct or remove existing invalid entries. Use Circle Invalid Data, filters, or separate checks to review populated cells.

Dropdowns can appear empty or incomplete if the Source reference points to the wrong cells, includes blanks, or does not cover newly added options. A typed list can be awkward when choice names contain commas. Put the options in worksheet cells instead when the list is complex or maintained by other people. For a cross-sheet source, verify that the defined name refers to the correct range and has the appropriate scope.

Another common misconception is that a Stop alert makes a workbook tamper-proof. It is designed to guide ordinary entry, not protect data from deliberate changes or every paste operation. Worksheet protection can reduce accidental edits to selected cells or formulas, but it is a separate feature and should not be treated as a security boundary. For important records, combine sensible validation with review, permissions, and checks after data is collected.

Choosing validation or another Excel feature

Use validation when the goal is to guide or restrict entry in particular cells. Use conditional formatting when the goal is to highlight values that need attention; formatting changes appearance but does not reject an entry. Use worksheet protection when the goal is to prevent routine edits to locked cells, not to define which input is valid. For data brought in from files or other systems, Power Query can help transform and standardize it during import, but it is not a substitute for input rules on a worksheet.

These features can complement one another. For example, a dropdown can standardize status entries, conditional formatting can highlight overdue records, and a separate check can flag missing required fields. Choose the simplest setup that matches the actual workflow. A rule that is too restrictive can cause users to work around it, while a rule that is too permissive may not improve consistency.

Excel for Windows, Mac, and the web

The ribbon paths and steps here are for Excel for Microsoft 365 on Windows. Excel for Mac also has Data Validation on the Data tab, although dialog layout and some controls can differ. Excel for the web supports common validation tasks, but availability and presentation of options may vary. If a workbook relies on detailed custom rules, named ranges, or a more elaborate dynamic dropdown, check the workbook in the desktop version used by its editors and test the behavior in the web or Mac version if those users will enter data there.

Frequently Asked Questions

How do I add a dropdown list in Excel?
Select the target cells, choose Data > Data Validation > Data Validation, set Allow to List, enter comma-separated choices or select a source range, and keep In-cell dropdown selected.

Can Excel data validation use a list from another sheet?
Yes. Define a name for the source range, then use that name in the validation Source field. A named range avoids the usual limitation on entering a direct reference to a different worksheet in that field.

Why can I paste an invalid value into a validated cell?
Validation is primarily an input check, and paste or other ways of populating cells can bypass its alert. Review existing values with Data > Data Validation > Circle Invalid Data, and use additional checks for important data.

Does adding validation fix values that are already in the cells?
No. The rule guides or checks later entry; it does not automatically clean existing values. Use Circle Invalid Data or another review method to find entries that do not meet the rule.

Can I make a cell required with data validation?
A rule can reject a blank during ordinary entry, but it cannot guarantee that every record is completed, particularly when cells are left untouched or populated by other means. Use a completeness check as well if missing values matter.

How do I remove data validation?
Select the affected cells, open Data > Data Validation > Data Validation, and choose Clear All in the dialog. Check the selection first, because clearing validation removes the rules from every selected cell.

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.