Excel Data Validation: Lists, Rules, and Common Fixes

In Microsoft Excel for Microsoft 365 on Windows, data validation controls what users can enter in selected cells. To create a dropdown, select the cells, choose Data > Data Validation, set Allow to List, and enter a list range or typed choices in Source.

What Excel data validation does

Data validation is a set of rules attached to cells. It can restrict entries to a list, a number range, a date range, a time, a maximum text length, or a result that meets a custom formula. It can also show an instruction when someone selects a cell and display a warning or block an invalid entry.

For example, a department column might allow only Sales, Finance, or Operations. A quantity field might accept whole numbers from 1 to 100. These restrictions make worksheets easier to fill in consistently and reduce typing errors that can interfere with sorting, filtering, and summaries.

Validation is a helpful editing control, not a guarantee that every value in a workbook is correct. Users may paste over validated cells, and existing entries are not automatically repaired when a rule is added or changed. Treat it as one part of a careful spreadsheet workflow.

Create a dropdown list

For a short list, you can type the options directly into the validation dialog. For a longer or regularly maintained list, store the options in worksheet cells and point the rule to that range.

Select the cells that should have the dropdown, such as B2:B100.
On the Windows ribbon, choose Data > Data Validation. In the dialog, open the Settings tab if it is not already selected.
Set Allow to List. Keep In-cell dropdown selected if you want the arrow to appear when a validated cell is selected.
In Source, enter choices separated by commas, such as New,In progress,Complete, or select the cells containing the choices.
Choose OK. Select one of the validated cells and use its dropdown arrow to choose an option.

Typed choices are convenient for a small list that rarely changes. For example, a status field with three fixed options is a good candidate. If the list is long, likely to change, or used in several places, store it on a worksheet and refer to it instead; this makes updates less error-prone.

A list source on the same worksheet can be a cell range, such as =$F$2:$F$4. For a list on another worksheet, create a named range: select the source cells, choose Formulas > Define Name, assign a clear name such as Departments, and use =Departments in the validation Source box. The equals sign tells Excel to use the named range. Keep list entries free of unintended blank cells, since blanks can appear as empty dropdown choices.

Choose the right validation rule

In the Data Validation dialog, the Allow setting determines the kind of rule. The available choices cover common entry needs, while Custom lets you create a rule with a formula.

Whole number: Restrict entries to integers, such as a count from 1 through 100.
Decimal: Allow numbers within a range, such as a measurement from 0 to 10.5.
Date or Time: Set an acceptable date or time interval. These rules are useful for forms, though they do not confirm that an entered date is meaningful for the wider business process.
Text length: Limit the number of characters, for example to keep a short code within a chosen length.
List: Offer a dropdown of permitted values, or allow users to type a value that matches the list, depending on the error-alert settings.
Custom: Use a formula that returns TRUE for acceptable entries and FALSE for unacceptable ones.

For a simple numeric rule, select the target cells, set Allow to Whole number, choose between, and enter 1 and 100 as the minimum and maximum. Excel rejects entries outside that interval if the error alert is configured to stop them. This approach is usually clearer than writing a formula for a straightforward limit.

Use input messages and error alerts

The Input Message tab lets you add a title and short instruction that appears when a user selects a validated cell. For a quantity field, a useful message might say “Enter a whole number from 1 to 100.” This explains the rule before the person makes a mistake. Clear the option to show the input message if it would distract from the worksheet.

The Error Alert tab controls what happens after an invalid entry is attempted. Stop blocks the entry. Warning lets the user choose whether to keep an entry that violates the rule. Information displays a notice but allows the entry. If consistent data is important, use Stop and write a specific message that explains the allowed value. If exceptions are expected, Warning may be more appropriate, but the sheet should make clear that exceptions need review.

Write a custom validation formula

Custom validation is useful when a built-in rule cannot express the condition you need. The formula is evaluated for the active cell in the selected range. References should usually be written relative to the top-left cell of that range, while references to a fixed comparison range can be locked with dollar signs.

With cells A2:A100 selected and A2 as the top-left cell, use =AND(A2>=1,A2<=100) to accept values from 1 through 100. The formula returns TRUE for 35, so Excel accepts it, and FALSE for 120, so a Stop alert rejects it. Set Allow to Custom and enter the formula in the Formula box.

Another example is preventing duplicate names. With the entry area A2:A100 selected, use =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). The first part permits an empty cell; the second accepts a value only when it appears once in the range. If A2 contains Anna and no other cell in the range contains Anna, COUNTIF returns 1 and the rule is TRUE. If Anna appears twice, the rule is FALSE for those entries. This type of validation checks entries as they are made, but pasted data can still create duplicates, so review the completed column when accuracy matters.

Edit rules and check existing data

To change a rule, select a cell that uses it and choose Data > Data Validation. If the selection contains cells with different validation rules, Excel may show that the selection has more than one type of rule; inspect or edit those cells separately to avoid changing the wrong group. You can copy a validated cell and use Paste Special > Validation to copy its validation rule without replacing the destination cell’s other contents.

Changing a rule does not necessarily identify every existing value that now breaks it. To check entries, select the relevant range and use Data > Data Validation > Circle Invalid Data. Excel circles values that fail the current rule. After correcting them, use Clear Validation Circles from the same menu to remove the markings. Reviewing old entries is particularly important when narrowing a date range or replacing a previously broad list.

Common problems and limits

The dropdown arrow is missing: Select the cell and reopen Data Validation. Confirm Allow is List and In-cell dropdown is selected. The arrow normally appears when the cell is selected, not as a permanent button.
A list does not update: Check that the Source points to the correct range or named range. If you added options outside the referenced cells, expand the source range or update the name.
Invalid values still appear: The rule may use Warning or Information instead of Stop, or data may have been pasted into the cells. Validation should not be treated as access control or protection against deliberate changes.
The rule rejects an expected value: Check for spaces, inconsistent spelling, text stored in place of a number, or a formula reference that shifts unexpectedly. Use a small test range before applying a complex custom rule to a large worksheet.
Rules differ within one column: Copying cells from elsewhere can bring along different validation settings. Select representative cells and inspect the rule, then apply a consistent rule to the intended range.

Data validation is distinct from worksheet protection. Protection can limit editing of locked cells, but it is not a substitute for checking data, and validation does not prevent someone from changing the worksheet structure or replacing cell contents. For shared or important records, combine sensible validation with access controls, review procedures, and backups appropriate to the workbook.

Windows, Mac, and Excel for the web

These instructions are for Excel for Microsoft 365 on Windows. On Windows, the main route is Data > Data Validation. Excel for Mac also provides Data Validation from the Data tab, although dialog layout and some ribbon details can differ by release. Excel for the web includes data-validation controls, but available editing options can vary; if a complex rule or named-range setup is difficult to manage in a browser, open the workbook in the desktop app.

Validation rules generally travel with the workbook when it is opened in another supported Excel app, but test important dropdowns and custom rules in the environment where people will use the file. This is especially useful when a workbook is shared across desktop, Mac, and browser users.

Frequently Asked Questions

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

Can an Excel dropdown list use another sheet?
Yes. Create a named range for the source cells using Formulas > Define Name, then enter the name with an equals sign in the validation Source box, such as =Departments.

Can I allow values that are not in the dropdown?
Yes, depending on the rule and alert settings. Clear Show error alert after invalid data is entered, or use Warning or Information instead of Stop. That also means users can enter values that do not match the list, so choose this only when exceptions are acceptable.

Does data validation stop copy-and-paste errors?
No. Pasting can bypass normal validation prompts or replace the rule itself. Check pasted data and inspect the destination cells’ rules when the worksheet depends on consistent entries.

How do I remove data validation?
Select the cells, choose Data > Data Validation, and select Clear All in the dialog, then choose OK. This removes the validation rule from the selected cells but does not delete their existing contents.

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.