Data Validation in Excel: Lists, Rules, and Fixes

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, open the Data tab, select the cells to control, then choose Data > Data Tools > Data Validation. Use the Settings tab to allow a list, number, date, time, text length, or custom formula; Excel can also show instructions and reject entries that do not meet the rule.

What the Data Validation tab does

Data validation is a set of rules attached to worksheet cells. It can limit what someone can enter, provide a dropdown list, or display a message when a cell is selected. For example, a status column might allow only “Not started,” “In progress,” and “Complete,” while a quantity column might accept only whole numbers from 1 to 500.

The feature is useful for forms, trackers, order sheets, surveys, and shared workbooks where consistent entries matter. A consistent set of values is easier to filter, summarize, and analyze than a column containing variants such as “Done,” “done,” and “Complete.” Validation helps prevent those inconsistencies at the point of entry, although it is not a security control and does not guarantee that every value already in a workbook is valid.

Where to find it and what is in the dialog

On Windows, select one or more cells and use Data > Data Tools > Data Validation. Depending on the ribbon layout, the command may appear as a button labeled Data Validation or inside a menu in the Data Tools group. The dialog has three main tabs:

  • Settings defines the allowed entries and the criteria, such as a number range or list.
  • Input Message displays a short prompt when a user selects a cell.
  • Error Alert controls the response to an entry that violates the rule.

In the dialog, the Allow menu offers options such as Any value, Whole number, Decimal, List, Date, Time, Text length, and Custom. Choosing a type reveals fields for its condition. For instance, Whole number can be set to between two limits, while Date can be limited to dates within a specified period. The In-cell dropdown option appears when List is selected.

On Mac, the equivalent command is on the Data tab under Data Validation; the precise ribbon arrangement can vary with the Excel version and window size. Excel for the web also has data-validation controls on the Data tab, though some dialog details and editing options can differ from the Windows desktop app. If instructions depend on a particular setting, check the desktop dialog in the version you are using.

Create a dropdown list

A dropdown is a practical way to standardize entries such as departments, delivery methods, or task statuses. For a short list, you can type the choices directly into the Source field. For a list that may change, it is usually easier to keep the choices in worksheet cells and point validation to that range.

  1. Select the cells that should have the dropdown, such as D2:D100.
  2. Choose Data > Data Tools > Data Validation.
  3. On Settings, choose List from Allow and ensure In-cell dropdown is selected.
  4. For a short, fixed list, enter items in Source separated by commas, such as New,In progress,Complete. For a range-based list, select or enter the source range, such as =$F$2:$F$4.
  5. Choose OK, then select one of the validated cells to use its dropdown arrow.

In the example, put New, In progress, and Complete in F2:F4, then use D2:D100 as the validated range. The absolute references in =$F$2:$F$4 keep the source range fixed. If list choices contain punctuation or are maintained by other users, a cell range is generally clearer than a comma-separated string. Separators in typed lists can also depend on regional settings.

For a list stored on a different worksheet, Excel’s Data Validation Source box may not accept a direct reference to that other sheet. A common solution is to define a workbook name for the source cells using Formulas > Define Name, then enter that name as the list source. Use a simple name without spaces, and keep the source values together so the list is easy to maintain.

Choose a rule that fits the entry

Pick the validation type based on what a valid value means, rather than using a rule simply because it is available. The most common choices are:

  • Whole number or Decimal: restrict quantities, scores, rates, or other numeric entries. Set a minimum, maximum, or other comparison.
  • Date or Time: define an allowed window, such as dates on or after a project start date.
  • Text length: limit characters, which can help with short codes or fixed-length identifiers. It checks length, not whether the text has a particular meaning.
  • List: offer a controlled set of choices and reduce spelling differences.
  • Custom: use a formula that returns TRUE for an acceptable entry and FALSE for one that should be blocked.

For example, a whole-number rule from 1 to 500 fits an item-count field better than a Decimal rule. A list fits a status field better than asking users to type a status from memory. Keep the rule understandable: a complicated restriction can frustrate users, especially if the workbook does not explain what a valid entry looks like.

Use a custom formula for specific checks

Custom validation is useful when a built-in criterion cannot express the rule. With the selected range A2:A100 and the active cell A2 at the top of that range, this formula allows only numbers from 1 through 100: =AND(A2>=1,A2<=100). Choose Custom in the Allow menu and enter the formula in the formula field. The relative reference A2 adjusts for each row in the selected range.

For a duplicate check, assume entries go in A2:A20 and you want each new entry to appear only once. Select A2:A20, choose Custom, and use =COUNTIF($A$2:$A$20,A2)=1. The dollar signs keep the checked range fixed, while A2 changes with each cell. A valid entry appears once in the range, so the formula returns TRUE; a repeated entry makes the count exceed 1 and is rejected. If the range contains existing duplicates, those entries may already be invalid, so review the data before relying on the rule.

Custom formulas need careful references. Excel evaluates the formula in relation to the active cell in the selected range, so selecting a range that starts at A2 while writing a formula based on A1 can shift the check. Test the rule with both an allowed and a disallowed value. A formula that returns TRUE does not necessarily mean the cell displays TRUE; it means Excel considers the entry acceptable under that rule.

Set helpful prompts and error messages

Validation works better when people know what to enter. On the Input Message tab, you can add a title and a brief instruction, such as “Quantity” and “Enter a whole number from 1 to 500.” Excel displays the prompt when a user selects a cell with that rule, if the option to show the input message is enabled.

On the Error Alert tab, enable the alert and choose a style. Stop blocks an invalid entry. Warning and Information display a message but let the user proceed after confirmation. Write an error message that explains the expected input rather than just saying “Invalid.” For example, “Choose a status from the dropdown” is more useful than “Error.”

If you want to allow exceptions, Warning or Information may suit a low-risk workbook; for required categories or tightly controlled entries, Stop is usually more appropriate. The alert affects manual entry, but it is not a substitute for access controls or checks before using important data.

Edit, copy, and remove validation

To change a rule, select a cell that uses it, open the Data Validation dialog, adjust the settings, and choose OK. To inspect which cells have validation, select a cell and open the dialog; the Settings tab shows its rule. The Circle Invalid Data command in the Data Validation menu can mark values that fail current rules, helping you find problems in a worksheet.

To remove validation, select the relevant cells, open the dialog, and choose Clear All, then confirm. Be aware that Clear All clears the settings in that dialog, not the worksheet’s cell contents. If the goal is to remove the rule from only part of a column, select only those cells first.

Copying and pasting can also copy validation settings, depending on the paste operation. If you paste values or data from another source, do not assume the destination rule will prevent every invalid result. After imports, bulk pastes, or edits by other users, use Circle Invalid Data or another review process to check the entries. Validation is most reliable when the worksheet is designed so users enter data in the intended cells and rules are checked periodically.

Common problems and limitations

  • The dropdown arrow is missing: confirm the cell has a List rule and that In-cell dropdown is selected. The arrow may appear only when the cell is selected.
  • Invalid values are still present: a rule does not automatically repair existing entries. Use Circle Invalid Data or inspect the column and correct those values.
  • A pasted value breaks the rule: pasting can bring in data in ways that bypass the normal entry prompt. Validate imported or pasted data afterward.
  • The source list will not accept a reference: check the source syntax and, for a list on another worksheet, use a defined name that refers to the source cells.
  • A custom formula rejects everything: verify the formula starts with the correct cell reference for the top-left cell of the selected range and returns TRUE for a test value that should be allowed.

Another limitation is that a cell can have only one data-validation rule at a time. If requirements change, editing a rule replaces or alters the existing rule rather than layering an independent second rule on top. Custom formulas can combine several checks, but they should be documented and tested so another workbook user can understand them.

Frequently Asked Questions

Where is Data Validation in Excel?
In Excel for Microsoft 365 on Windows, select the cells and choose Data > Data Tools > Data Validation. On Mac and Excel for the web, look on the Data tab; the layout and available controls can vary by version.

How do I create a dropdown list in Excel?
Select the target cells, open Data Validation, choose List, and provide either a comma-separated set of choices or a reference to cells containing the choices. Keep In-cell dropdown selected, then choose OK.

Can I make a dropdown list from another sheet?
Yes. A dependable approach is to define a name for the cells on the other sheet and use that name as the list source. This avoids the direct cross-sheet reference limitation in the Source field.

Why does Excel accept an invalid value?
The cell may not have the rule you expect, the Error Alert may be disabled or set to a permissive style, or the value may have been pasted in. Check the rule and error-alert settings, then review existing entries for invalid data.

Does data validation delete or fix incorrect entries?
No. It controls future entry through the validation rule and can alert users, but it does not automatically correct values already in the cells. Review and fix existing data separately.

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.