Data Validation in Excel: Rules, Lists, and Common Fixes

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, select the cells you want to control, then choose Data > Data Validation. Pick a rule—such as a dropdown list, whole-number limit, date range, or custom formula—and set an error alert if you want Excel to reject invalid entries. Data validation helps keep spreadsheet inputs consistent, but it is a guardrail rather than a security feature.

What data validation does

Data validation is a set of rules you attach to worksheet cells. It can limit what people enter or select, guide them with an input message, and show an alert when an entry does not meet the rule. For example, a response column can accept only values from a list, while a quantity column can accept whole numbers from 1 to 100.

These rules are useful when a spreadsheet is filled in by several people or reused over time. Consistent entries make sorting, filtering, formulas, and summaries more dependable. If one person types “In progress,” another types “In-Progress,” and a third types “Working,” Excel treats those as different text values. A dropdown can prevent that variation.

Validation applies to cells, not to the meaning of an entire workbook. It cannot confirm that a chosen department is factually correct, for instance; it can only check whether the entry satisfies the rule you defined.

How to add a validation rule

  1. Select the cell or range where users will enter data. Select the whole input range if you want the same rule applied to every cell.
  2. Choose Data > Data Validation. In the dialog box, use the Settings tab to choose an option under Allow and configure its conditions.
  3. Optionally add an Input Message to explain what belongs in the selected cells.
  4. On the Error Alert tab, choose whether Excel should stop, warn, or provide information when a user enters a value that breaks the rule.
  5. Select OK, then try a valid and an invalid entry to check that the rule behaves as intended.

The dialog is available from the Data tab in Excel for Microsoft 365 on Windows. Excel for Mac has a Data Validation command on its Data tab, although dialog layout and some interactions can differ. Excel for the web supports common validation tasks, but the desktop app may offer a fuller set of controls; use the desktop version if an option is missing.

Types of rules you can choose

The Allow menu covers several common kinds of input control. The choices include:

  • Whole number: restrict entries to integers, optionally within a range. This suits counts such as units ordered.
  • Decimal: allow numeric values with decimal places, such as a measured weight or percentage.
  • List: offer a dropdown of permitted choices, such as Open, In progress, and Complete.
  • Date or Time: limit entries to a date or time, including values before, after, or between specified bounds.
  • Text length: limit the number of characters, useful for short codes or fixed-length identifiers.
  • Custom: use a formula that returns TRUE for acceptable entries and FALSE for entries to reject.

The Ignore blank setting is also important. If it is selected, an empty cell can be accepted even when a rule is present. If every row must contain a value, consider clearing that option and testing the result, while remembering that validation alone is not a robust way to ensure a complete dataset.

Creating a dropdown list

A list rule is one of the most visible uses of validation. For a short, fixed set of choices, select the input cells, open Data > Data Validation, choose List under Allow, and enter the choices in the Source box separated by commas. Turn on the in-cell dropdown option so users can choose an item from the cell’s menu.

For a longer list or one that may change, keep the allowed values in a worksheet range instead. For example, place the status options in A2:A4 on a sheet named Lists, then refer to that range in the Source box. Because Excel’s validation source field may not accept a direct reference to another worksheet in every setup, a dependable approach is to define a name for the range using Formulas > Define Name, then use that name as the list source. If the options need to grow, an Excel Table can make the underlying list easier to maintain, but check that the validation source continues to include newly added options.

Keep the list values clean: remove accidental spaces, avoid duplicate choices, and use labels people can distinguish. A dropdown is a selection aid, not an automatic lookup; choosing a product name does not fill in its price unless you also add a formula or another mechanism.

Using a custom formula

Custom validation can express a condition that the standard number, date, or list options do not cover. The formula is evaluated for the active cell in the selected range. Relative references matter: when a rule is applied down a column, a reference should usually adjust for each row.

For example, suppose B2:B100 is the quantity-entry range and each value must be a whole number from 1 through 100. Select B2:B100, choose Custom under Allow, and use this formula:

=AND(B2>=1,B2<=100,MOD(B2,1)=0)

With the selected range starting at B2, this accepts 7 and 100, but rejects 0, 101, and 7.5. The MOD test checks that there is no fractional part. If blanks should be allowed, account for that deliberately in the rule or use the Ignore blank setting. Test the rule in more than one row: a formula that uses an absolute reference such as $B$2 would check the same cell repeatedly instead of checking each row’s entry.

Input messages and error alerts

An input message appears when a user selects a cell with validation. It can explain the expected format, for example, “Enter a whole number from 1 to 100.” It does not enforce a rule by itself; the Settings tab defines what is permitted.

The Error Alert tab offers three styles. Stop prevents an invalid entry when the user attempts to commit it. Warning lets the user proceed after confirming the choice, while Information shows a message but also permits the entry. Choose Stop when consistency matters and exceptions should not be routine. A warning can be appropriate when unusual values are sometimes valid but should be reviewed.

Give alerts useful wording. “Invalid data” tells a user little; explain the acceptable values and, where relevant, how to correct the entry. A clear message can reduce repeated questions and accidental exceptions.

What validation does not prevent

A common misconception is that a validation rule makes a cell impossible to change to an invalid value. It does not. Users can sometimes bypass or replace validation by pasting data into cells, filling cells from another source, or changing workbook contents through other methods. A rule may also be removed or altered by someone who can edit the sheet.

Validation generally does not repair existing entries when you add a rule. If a column already contains out-of-range values, adding a new rule does not automatically make those values valid. Review existing data separately. Excel’s Data > Data Validation dialog may offer a Circle Invalid Data command for marking entries that violate the current rule; inspect the marked cells and correct them as needed.

For important records, use validation alongside other checks. Protecting a worksheet can reduce accidental changes to cells or formulas, but worksheet protection is not a substitute for access control or a secure database. Keep a backup, limit who can edit the workbook, and review data after imports or collaborative edits.

Common problems and how to fix them

  • The dropdown arrow is missing: Reopen Data Validation and confirm that List is selected and the in-cell dropdown option is enabled. Also confirm that the selected cell actually has the rule.
  • A valid-looking entry is rejected: Check for leading or trailing spaces, a number stored as text, a date interpreted in a different regional format, or a formula reference pointing at the wrong row.
  • Invalid data still appears: The rule may use Warning or Information rather than Stop, or the value may have been pasted into the cell. Check the alert style and review the data after pasting.
  • A rule seems to disappear across a range: Copying and pasting cells can affect validation. Inspect representative cells after copying, and use Paste Special carefully if you need to preserve only selected parts of a cell.
  • The source list does not update: Check whether the source range includes the new item and whether a named range points to the full list. Test the dropdown after changing its source.

When diagnosing a problem, select one affected cell and open Data > Data Validation to inspect its actual settings. Rules can differ across cells even when they look like one continuous column.

Editing, copying, and removing rules

To change a rule, select the affected cells, open Data > Data Validation, edit the settings, and confirm. If the selected cells have different rules, Excel may warn that the selection contains mixed settings. Check the range carefully before applying one replacement rule across it.

To remove validation, select the cells, open the dialog, and choose Clear All, then confirm. This removes the validation settings; it does not necessarily clear the cells’ existing values. If you copy a validated cell to another location, validation may be copied with it, so inspect the destination when the new area is meant to have different input rules.

When data validation is useful

Validation is most helpful at the point where people enter information. Common examples include an order form with a status dropdown, an expense log with a date boundary, an inventory sheet that accepts nonnegative counts, or a survey template that limits answers to defined categories. It is less useful when users need unrestricted notes or when a rule would block legitimate exceptions without a clear review process.

Design rules around the real workflow. Explain units, date formats, and required categories; decide whether blank entries are allowed; and test the sheet with realistic edge cases. If many people submit data, consider collecting it through a structured form or managed system and using Excel for analysis, rather than relying on a workbook rule as the only quality check.

Frequently Asked Questions

How do I add a dropdown in Excel?
Select the target cells and choose Data > Data Validation. Set Allow to List, provide the allowed choices in Source (or use a named range), enable the in-cell dropdown, and select OK.

Can Excel data validation allow only dates in a range?
Yes. Select the cells, open Data > Data Validation, choose Date under Allow, and set the condition and start and end dates. Check date interpretation in the workbook’s regional settings and test the boundary dates.

Why can I paste invalid data into a validated cell?
Validation is designed to guide normal entry, not to secure a workbook against every way data can be changed. Pasting or importing can bypass the expected prompt in some situations, so review data after those operations.

Does adding validation fix values already in the cells?
No. A new rule does not automatically correct existing entries. Use a separate review, such as checking the range against the rule or using Circle Invalid Data where available, then fix any exceptions.

How do I remove data validation without deleting cell contents?
Select the cells, open Data > Data Validation, choose Clear All in the dialog, and confirm. That removes the rule while leaving existing cell values 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.