Excel Data Validation: Steps, Rules, and Common Fixes

To add data validation in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Tools > Data Validation, set the rule on the Settings tab, then choose any input message or error alert and click OK. You can use validation to restrict entries to a list, a number or date range, a text length, or values that meet a custom formula.

What data validation does

Data validation is a set of rules for cells. It can guide someone toward an acceptable entry or stop certain values from being typed into a cell. A dropdown list is one familiar example, but the feature also checks numbers, dates, times, text length, and formulas.

Validation is useful in shared workbooks and repeated data-entry tasks: an order form can limit a status field to approved choices, while an inventory sheet can require a positive quantity. Consistent entries make sorting, filtering, formulas, and reports more dependable. Validation does not, however, make a workbook a secure database or guarantee that every value in it is valid. Pasting or filling cells can bypass a rule, and a rule does not automatically correct values that were already there.

Steps to create a validation rule

Select the cell or range where people will enter data. Select the whole intended range before creating the rule so you do not have to repeat the setup for each cell.
On the ribbon, choose Data > Data Tools > Data Validation. In the dialog, open the Settings tab.
Choose a value under Allow, such as Whole number, Decimal, List, Date, Time, Text length, or Custom. The remaining controls change to match that choice.
Set the condition. For example, choose between and enter minimum and maximum values, or enter a list source.
Optionally configure the Input Message and Error Alert tabs, then select OK.

To revise a rule later, select a cell that has it and reopen Data Validation. If a selection contains cells with different rules, Excel may not present one simple rule to edit; inspect the cells individually or select a consistent range. On Excel for Mac, select the cells and use Data > Data Validation; dialog details and ribbon placement can differ slightly. Excel for the web has its own ribbon layout, so the command may be positioned differently.

Choose the right validation type

The Allow setting determines what kind of check Excel performs. Pick a rule that fits the meaning of the field, rather than merely the values currently in the sheet.

Whole number or Decimal: Set a minimum, maximum, or other comparison. Use Whole number for an item count and Decimal for a measured quantity such as weight.
List: Offer a defined set of choices, such as Pending, Approved, and Rejected. Users can select from an in-cell dropdown when that option is enabled.
Date or Time: Restrict entries to a date or time condition. A date rule can, for example, require a date within a reporting period.
Text length: Limit the number of characters, useful for a short code or a field with a known maximum length. It does not check whether the characters have the right meaning.
Custom: Use a formula that evaluates to TRUE for an allowed entry and FALSE for a disallowed one. This is the most flexible option, but the formula must be designed for the selected cells.

The Ignore blank option affects how empty cells are treated by a rule. Consider whether a field is optional before leaving blanks allowed. A validation rule is not the same as a required-field control: a blank can still be present in a worksheet, so check completeness separately when it matters.

Create a dependable dropdown list

For a short, fixed list, select the target cells, open Data Validation, choose List, and enter the choices in Source separated by commas. Turn on In-cell dropdown so the arrow appears when a validated cell is selected. Inline lists are quick to set up, but they are awkward to maintain when choices change and can be confusing if a choice itself contains a comma.

For a list that may grow, keep the options in a worksheet range and use a defined name as the source. For example, put status choices in a dedicated area, select those cells, and choose Formulas > Define Name to give the range a name such as StatusList. Then set the list validation Source to =StatusList. A defined name is a practical way to refer to a list on another sheet; directly typing an ordinary reference to a different worksheet into the Source box may not work as expected.

Keep the source values free of accidental blanks and duplicates, and place them somewhere users are unlikely to overwrite. If choices need regular updates, edit the source range and make sure the defined name covers the added items. A dropdown controls the choices offered during normal entry; it does not prove that a value pasted into the cell came from that list.

Use custom formulas for specific checks

Choose Custom when the built-in conditions are not expressive enough. Excel evaluates the formula for the first cell in the selected range and adjusts relative references for other cells. Therefore, select the correct starting cell and write the formula with that cell in mind. A custom formula must return TRUE for an acceptable value.

For a numeric range, suppose the cells being validated are A2:A100 and the first selected cell is A2. Use =AND(A2>=1,A2<=100). This permits values from 1 through 100, including both endpoints; an entry of 42 passes, while 0 fails. The relative reference A2 lets Excel evaluate A3 for the next row, then A4, and so on.

To prevent duplicate nonblank identifiers, suppose IDs go in A2:A100 and the validation rule is applied to that same range starting at A2. Use =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). The formula allows a blank, or allows an entered value only when it appears once in the range. If blank IDs should not be allowed, use a separate required-entry check or adapt the condition; validation alone does not make the cell mandatory. Test custom rules with an allowed value, a disallowed value, and a blank if blanks are relevant.

Set helpful messages and error alerts

The Input Message tab lets you show a short instruction when a user selects a validated cell. For a date field, an instruction such as “Enter a date in the current quarter” can explain the expected input before an error occurs. Keep it specific: a useful message tells people what to enter, not just that the cell has a rule.

On the Error Alert tab, choose whether Excel displays a message after an invalid entry. A Stop alert rejects the entry through normal typing and asks the user to correct it. Warning and Information alerts allow the user to proceed after acknowledging the message, so they guide rather than strictly enforce. Give the alert a clear title and describe the correction. If the Error Alert is disabled, the rule may not interrupt someone who types an invalid value.

Check, copy, and maintain rules

Before distributing a workbook, test a valid and invalid entry in representative cells. Confirm that the dropdown appears where expected, the messages are readable, and rules cover the full input range. If the workbook will be reused, document where the source choices live and who should update them.

Excel includes a way to locate entries that violate existing rules: select the relevant worksheet area and use Data > Data Tools > Data Validation dropdown > Circle Invalid Data. The circles help identify values that do not match the rules currently in force. They do not repair the data, and a value may have become invalid because the rule changed after it was entered. Use the same menu option to clear the circles when finished.

When extending a form, be careful to copy the validation rule along with the intended cells. A normal paste can replace destination contents and may bring other formatting or values with it; use Paste Special when you need to transfer only validation. After copying, check the destination cells and test the rule. Changing a source list does not necessarily update a separately typed inline list, which is another reason to keep reusable choices in a maintained range.

Common problems and limits

Invalid entries still appear: Values may have been pasted, filled, or introduced by another process rather than typed normally. Check existing values with Circle Invalid Data and use other controls if data integrity must be enforced beyond ordinary entry.
The dropdown arrow is missing: Confirm the cell has List validation and that In-cell dropdown is selected. Also verify that the active cell is one of the validated cells.
A rule rejects a value that looks valid: Check whether the cell contains text that looks like a number or date, whether the comparison endpoints are correct, and whether the formula references the intended starting cell. Regional settings can affect how dates and list separators are interpreted.
A list does not update: Check that the defined name refers to the full source range. If you used a comma-separated list in the dialog, update the Source itself when choices change.
Old bad values remain: Creating or editing a rule does not automatically clean up existing entries. Identify and correct those values separately.

Validation is best treated as one layer of quality control. For a small workbook, it can prevent many ordinary mistakes. For critical records or data supplied by multiple systems, combine it with review, duplicate checks, appropriate permissions, and checks in the system that ultimately stores or processes the data.

Frequently Asked Questions

How do I add a dropdown in Excel?
Select the target cells, choose Data > Data Tools > Data Validation, set Allow to List, enter a source or refer to a defined name, and make sure In-cell dropdown is selected.

Can data validation prevent someone from pasting invalid data?
Not reliably. Pasting, filling, and other ways of changing cells can bypass normal validation prompts. Check existing entries and use additional controls where stronger enforcement is necessary.

How do I remove data validation?
Select the affected cells, open Data Validation, and choose Clear All in the dialog, then confirm. This removes the validation settings; it does not delete the cell contents.

Can I apply one rule to several cells at once?
Yes. Select the intended range before setting up the rule. For custom formulas, write the formula relative to the first cell in that selection so Excel can adjust it across the range.

Why is Excel not showing an error for an invalid entry?
The Error Alert may be turned off, the alert may be set to Warning or Information, or the value may have been pasted or filled. Recheck the rule and test it by typing an invalid value directly into a validated cell.

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.