To add a data validation rule in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, set the rule under Settings, and select OK. A rule can limit entries to items such as a dropdown list, a number range, a date range, or text that meets a custom formula.
What data validation does
Data validation controls what people can enter in selected worksheet cells. It is commonly used in forms, shared trackers, inventories, and budget sheets to make entries more consistent. For example, a status column might accept only Open, In progress, or Complete, while a quantity column might accept whole numbers from 1 to 500.
A validation rule can display an input message when someone selects a cell and an error alert when an entry does not meet the rule. Depending on the alert style, Excel can stop the entry, warn the person, or simply provide information. Validation is a convenient way to prevent routine mistakes, but it is not a security feature or a guarantee that every value in a workbook is valid.
Rules apply to cells, not to the meaning of an entire worksheet. You can select one cell or a range before creating a rule. Applying the same rule to a whole column can be useful, but consider whether headings and future notes might also be inside that selection.
Types of rules Excel provides
In the Data Validation dialog, the Allow list offers several built-in rule types. Choose the one that matches the kind of value the cell should contain:
- Whole number limits entries to integers, with conditions such as between, greater than, or equal to.
- Decimal permits numbers with fractional parts and can enforce a numeric range.
- List creates an optional in-cell dropdown of permitted choices.
- Date and Time limit entries to specified dates or times.
- Text length restricts how many characters a cell can contain.
- Custom evaluates a formula that must return TRUE for an entry to be accepted.
For number, date, time, and text-length rules, Excel also lets you choose a comparison condition and enter the required limit or limits. A rule set to “between” needs both a minimum and a maximum. Decide whether the endpoints should be included; the built-in “between” condition includes them.
How to create a validation rule
- Select the cell or cells where people will enter data. If the same rule should apply to a range, select the entire range first.
- On the ribbon, choose Data > Data Validation. In the dialog, open the Settings tab if it is not already selected.
- Choose a rule under Allow, then set its condition and limits. For a list, specify its source; for a custom rule, enter the formula.
- Optionally configure an input message and an error alert on their respective tabs.
- Choose OK, then try an allowed entry and a disallowed one to check that the rule behaves as intended.
In Excel for Mac, select the cells and use the Data tab’s Data Validation command; the dialog and rule choices are broadly similar. Excel for the web also provides data validation controls, though some editing options and dialog details can differ from the Windows desktop app. If a workbook’s rule is difficult to edit in the browser, open it in the desktop version.
Creating a dropdown list
A list rule is useful when entries should come from a known set of labels. Select the destination cells, open Data > Data Validation, set Allow to List, and enter the list in the Source box. Turn on In-cell dropdown if you want the arrow to appear when a cell is selected.
For a short list, you can type the choices directly into Source, separated by commas, such as North,South,West. Depending on regional settings, Excel may use a different list separator, so a source range is often easier to maintain. For a longer list, put the choices in worksheet cells and use that range as the source. Keep the choices together, without blank rows, and update the source range when new options are added.
A source range on the same worksheet is straightforward. If the choices are on another worksheet, a named range is a reliable way to refer to them: define a name for the source cells, then enter that name as the list source. This also keeps the dropdown’s options in one place instead of embedding them in every validation rule.
A dropdown does not mean a person must always click an arrow; they can type a choice. The rule checks whether the typed value is allowed. For usability, make the labels distinctive and consistent. For example, avoid having both “In progress” and “In Progress” if users are expected to treat them as the same status.
Using a custom formula
Choose Custom when a built-in rule cannot express the condition you need. The formula must evaluate to TRUE for the entry to be accepted. Cell references should normally be written relative to the top-left cell of the selected range, so Excel adjusts them for other cells in that range.
For example, assume the cells to validate are A2:A100 and each entry must be a number from 1 through 100. Select A2:A100, choose Custom, and enter =AND(ISNUMBER(A2),A2>=1,A2<=100). With 42 in A2, the formula returns TRUE; with 120, it returns FALSE. The number 1 and 100 are included, and decimal values such as 42.5 are allowed. If only whole numbers are valid, choose the built-in Whole number rule instead, or add an integer condition to the custom formula.
Be careful with dollar signs in references. In the example, A2 is relative, so when Excel checks A3 it evaluates the corresponding A3 value. An absolute reference such as $A$2 would keep pointing to A2 and could make the rule test the wrong cell throughout the range. Test a few cells in different rows before relying on a custom rule.
Input messages and error alerts
The Input Message tab lets you show a short title and instruction when a validated cell is selected. A useful message explains the expected entry, such as “Enter a whole number from 1 to 500.” It can prevent errors before someone types, especially when a cell’s allowed format is not obvious.
On the Error Alert tab, you can choose a style and write a title and message. Stop blocks an invalid entry. Warning asks whether the person wants to continue, so an exception can be accepted. Information displays a message but allows the entry. Clear the option to show an error alert if you do not want a prompt, but remember that this makes an invalid entry easier to miss.
Use messages that tell the user what to do rather than merely saying that the value is wrong. For instance, “Choose a date between April 1 and June 30” is more actionable than “Invalid date.” Keep the rule and message aligned: if the permitted range changes, update the displayed instructions too.
Limitations and common problems
Data validation is not a complete data-cleaning system. Pasting or filling content into cells can bypass or replace validation behavior, and values that were already present before the rule was added are not automatically corrected. A user may also copy a cell with a different rule over a validated cell. Review important data after imports or bulk pastes.
To check existing entries, select the validated cells and use Data > Data Validation to review the settings. Excel also provides Data > Data Validation options for identifying invalid data, including Circle Invalid Data in the desktop application. Circles help locate entries that do not meet the rule; they do not repair them. After correcting the values, use the command to clear the circles.
If a dropdown arrow is missing, first select the cell and confirm that In-cell dropdown is enabled in the rule. The arrow normally appears only while the cell is selected. Also check that the selected cell actually has the list rule, rather than a neighboring cell that looks similar.
If an entry that should be accepted is rejected, inspect the source range, comparison limits, and error-alert style. Dates can be confusing because Excel stores dates as serial values and displays them according to cell formatting and regional settings. A value that looks like a date may have been entered or imported as text. Likewise, spaces before or after a list item can cause a typed value not to match the source exactly.
To remove a rule, select the affected cells, open Data > Data Validation, and choose Clear All in the dialog, then confirm. Clearing a rule does not delete the cell’s contents. If you need to change a rule, edit its settings instead of clearing it and recreating it, particularly when you have also configured messages.
Choosing validation or another tool
Use data validation when the goal is to limit what can be entered at the point of entry. Use conditional formatting when the goal is to highlight values that look wrong without blocking them. For example, a validation rule can stop a user entering a quantity above a limit, while conditional formatting can color unusually high quantities already present in a report.
For larger datasets or information that needs stronger controls, validation may be only one part of the solution. A formatted Excel table can help keep related records organized, while a review process or a purpose-built data-entry form can provide additional checks. Validation is best understood as a practical guardrail: easy to set up and useful for common mistakes, but not a substitute for checking critical data.
Frequently Asked Questions
How do I add a dropdown in Excel?
Select the destination cells, choose Data > Data Validation, set Allow to List, enter a comma-separated source or a cell range, and make sure In-cell dropdown is selected.
Can Excel validation allow only whole numbers?
Yes. Choose Whole number under Allow, select a condition such as “between,” and enter the minimum and maximum. This is simpler than using a custom formula for an ordinary integer range.
Why can I paste an invalid value into a validated cell?
Some paste and fill operations can bypass the normal entry prompt or overwrite the cell’s rule. Validation is not a lock against every way data can enter a worksheet, so check imported and pasted values separately.
Does adding a rule fix data that is already in the cells?
No. A new rule governs later entries but does not automatically change existing values. Use Excel’s invalid-data checking tools or review the range and correct any entries that fail the rule.
Can I use a list from another worksheet?
Yes. A named range for the choices is a dependable approach: create the name for the source cells, then use that name in the List rule’s Source box. Keep the named range updated when the available choices change.
Related
- Compare Similarity Between Two Excel Sheets
- Excel Merge Cells Command: Shortcuts, Options, and Limits
- Clear Data Validation in Excel Without Deleting Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets GOOGLEFINANCE: Prices, History, and LimitsGoogle Sheets’ GOOGLEFINANCE function retrieves supported market information, including current quote data and historical prices, directly into a…
- Excel Table Multiple Header Rows: What WorksNo. An Excel Table can have only one header row: the first row of the table range supplies…
- Excel Data Validation List: How to Add a Blank OptionYes. To add a selectable blank to an Excel data-validation drop-down, include a genuinely empty cell inside the…
- Excel Workbook Not Opening? Causes and Fixes for WindowsIf an Excel workbook will not open, first try opening Excel in Safe Mode, then use File >…
- How to Double Space Each Line in Microsoft Word EasilyTo double-space text in Word, select the text, then choose Home > Line and Paragraph Spacing > 2.0.…
- How to Insert a Table of Contents in Microsoft WordTo insert an automatic table of contents in Microsoft Word for Microsoft 365 on Windows, apply built-in heading…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.