In Excel, data validation restricts what people can enter in selected cells—for example, a date within a specified period, a whole number in a set range, or an item from a dropdown list. To add a rule in Microsoft Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, set the criteria on the Settings tab, and select OK.
What data validation does
Data validation is a cell-level control for guiding or limiting data entry. A rule can accept only certain kinds of values, set a minimum and maximum, or check a custom condition. You can also display instructions when someone selects a cell and show an alert when an entry does not meet the rule.
It is useful when a worksheet will be filled in by multiple people or when later calculations depend on consistent inputs. A dropdown for department names, for example, can prevent variations such as “Customer Service,” “Customer service,” and “Cust. Service.” A number rule can prevent an age or quantity from being entered as an implausible value.
Validation does not repair existing data or guarantee that a workbook contains only acceptable values. It primarily guides entry through the worksheet interface; copying and pasting can introduce values that do not satisfy a rule. Treat it as a practical data-entry aid, not as a security boundary or a substitute for checking important data.
When to use a validation rule
Choose validation when a cell has a clear set of acceptable entries and preventing mistakes at the point of entry is easier than cleaning them up later. It is especially helpful for forms, tracking sheets, inventory records, and shared templates. The right rule depends on what “valid” means for the data: a date can be in the correct format but still be outside the period your process allows.
- Use a list for a small set of standard choices, such as status values of “Not started,” “In progress,” and “Complete.”
- Use a number or date limit when entries need to stay within a meaningful range, such as a quantity from 1 to 500.
- Use text length when a field has a defined maximum, such as a short code.
- Use a custom formula when the condition depends on another cell or requires a check not covered by the built-in options.
Validation is less useful when users need to enter free-form notes, when acceptable values change frequently without a maintained source list, or when data is routinely imported from other systems. In those cases, a clear instruction, a review process, or a separate data-cleaning step may be more appropriate.
How to create a rule in Excel
- Select the cell or range to control. You can select a whole column, but applying a rule only to the intended input area can make a workbook easier to maintain.
- On the ribbon, choose Data > Data Validation. If the command is unavailable, check whether the sheet is protected or whether the selected cells are being edited.
- On the Settings tab, choose an option under Allow, then configure its conditions. For example, select Whole number and set a minimum and maximum.
- Optionally open the Input Message tab to provide a prompt, and the Error Alert tab to choose how Excel responds to an invalid entry.
- Select OK, then try both an acceptable and an unacceptable value to confirm the rule behaves as intended.
The Settings tab also includes an Ignore blank option. When enabled, blank entries are permitted by the validation rule; it does not force a user to fill in the cell. If a field is required, explain that requirement and consider checking completeness separately. In Excel for Mac, the Data Validation command is also on the Data tab, although dialog layout and ribbon appearance can vary. Excel for the web may present a different interface or fewer options; use the desktop app when a rule or setting is not available in the browser.
Validation types and what they check
The Allow menu includes several built-in rule types. These are often easier to understand and maintain than a custom formula:
- Whole number: accepts integer values and can enforce a comparison such as between, greater than, or less than a limit.
- Decimal: accepts numeric values that may include a fractional part, with comparable limits.
- List: restricts entries to listed choices and can show an in-cell dropdown.
- Date and Time: restricts entries to a date or time condition, such as dates after a specified day.
- Text length: limits the number of characters in an entry.
- Custom: evaluates a formula and accepts the entry when the formula returns TRUE.
These rules check values, not necessarily their meaning. A decimal rule might accept 12.5, but it cannot tell whether that number is a suitable price for a particular product unless the rule includes additional logic. Also, displaying a date in a particular format is not the same as restricting which dates are allowed. Set the cell’s number format separately if you want dates or numbers to appear consistently.
Creating a dropdown list
A dropdown list is one of the most familiar uses of validation. Select the destination cells, open Data > Data Validation, choose List under Allow, and make sure In-cell dropdown is selected. Enter the choices in Source, or point Source to cells that contain the choices. A range is generally easier to update than a long list typed into the dialog.
For a short, fixed list, type the entries separated by commas, such as Open,Pending,Closed. In some regional settings, the list separator may differ, so a typed list that does not split into separate choices may need a different separator. For a maintained list, put each choice in a column and use that range as the source. If the source is on another worksheet, define a named range for it and use the name in the validation source; this keeps the rule easier to manage.
Keep the source choices tidy: remove unintended blank cells, avoid duplicate labels, and use wording users will recognize. When a source list changes, check that the validation source includes the new entries. A dropdown helps people choose an allowed item, but it does not necessarily prevent them from pasting a different value into the cell.
Custom formulas for more specific checks
A custom rule is useful when a built-in condition is not enough. In the Data Validation dialog, choose Custom and enter a formula that evaluates to TRUE for an acceptable value. When applying the rule to a range, write the formula as though it were being evaluated for the top-left cell; relative references then adjust for the other cells in the selection.
With values entered in A2:A100 and the rule applied to A2:A100, this formula permits whole numbers from 1 through 100. The formula uses A2 because it is the first cell in the selected range:
=AND(A2>=1,A2<=100)
With entries in A2:A100 and the rule applied to that same range, this formula permits a value only when it occurs once in the range. A second occurrence of a value is rejected, while a unique value passes:
=COUNTIF($A$2:$A$100,A2)=1
Test custom rules carefully, including their behavior with blank cells and existing entries. A misplaced dollar sign can make every cell check the same reference; omitting one can cause a range reference to shift unexpectedly. If you want to allow blanks in the duplicate check, the formula needs an explicit blank condition rather than relying on the Ignore blank setting alone.
Input messages and error alerts
An input message appears when a user selects a cell with validation, if Show input message when cell is selected is enabled. Use it to explain the expected entry, not just to repeat the field name. For example, “Enter a whole number from 1 to 100” is more useful than “Quantity.” A concise message can reduce errors without making the worksheet crowded.
On the Error Alert tab, choose whether to show an alert after an invalid entry and select a style:
- Stop blocks the invalid entry through normal typing and asks the user to correct it.
- Warning lets the user choose whether to keep an entry that violates the rule.
- Information informs the user but allows the entry to remain.
You can also edit the alert title and message. If Show error alert after invalid data is entered is cleared, the rule may remain in place but Excel will not display that alert. For a controlled input form, Stop is usually the clearest choice; Warning or Information can make sense when exceptions are allowed and users need flexibility.
Changing rules and finding invalid entries
To review or change a rule, select a cell that uses it and open Data > Data Validation. The dialog shows the settings for the selected cell. If a range contains cells with different rules, Excel may not show a single rule that represents every cell, so inspect smaller selections when necessary. The Apply these changes to all other cells with the same settings option can help extend a rule to matching cells.
Excel does not automatically remove or correct values that were already in a cell before validation was added. In the Windows desktop app, use Data > Data Validation > Circle Invalid Data to mark entries that do not match the current rule. Review the marked cells and correct them as needed. The circles are a review aid, not a correction; use the same menu’s Clear Validation Circles command to remove the markings afterward.
To remove a rule, select the relevant cells, open Data > Data Validation, and choose Clear All in the dialog. This removes the validation settings, not the cell contents. If a rule appears to have disappeared unexpectedly, check whether a pasted cell or range replaced the original validation settings.
Common problems and practical limits
A dropdown may be missing because In-cell dropdown was not selected, the source range is empty, or the source reference is wrong. A list may show only some options if its source range does not include all the entries. Check the source cells and the rule itself before rebuilding the list.
The most important limitation is that validation is not foolproof. Pasting, filling cells, importing data, or replacing a cell’s contents can bypass the intended entry experience or overwrite a rule. For a shared workbook, protect the worksheet where appropriate, limit who can edit the source list, and check the final data with filters, conditional formatting, or a review step. Sheet protection and validation serve different purposes: protection controls editing, while validation guides permitted entries.
Also consider usability. A very strict rule can block legitimate exceptions, and a cryptic error message can frustrate users. If unusual values are valid in some cases, document the exception process or choose a warning that makes the choice explicit. For important records, periodically review data rather than assuming that a validation rule guarantees accuracy.
Frequently Asked Questions
How do I add data validation in Excel?
Select the cells, choose Data > Data Validation, select a rule type under Allow, set its conditions, and choose OK. Add an input message or error alert if users need guidance.
How do I create a dropdown list in Excel?
Choose Data > Data Validation, select List under Allow, and enter comma-separated choices or select a source range. Make sure In-cell dropdown is enabled if you want the arrow to appear in the cell.
Can Excel validation prevent users from pasting invalid data?
No. Validation is intended to guide normal data entry, but paste operations and other ways of replacing cell contents can bypass a rule or overwrite it. Check data that comes from pasted or imported sources.
How do I remove data validation without deleting the data?
Select the cells, open Data > Data Validation, and choose Clear All. This removes the validation rule while leaving the existing cell values in place.
Why is my Excel dropdown list not showing all the options?
Check that the list source includes every choice, contains no unintended gaps, and is referenced correctly in the Source box. If you typed choices directly, check whether your regional list separator differs from a comma.
Related
- Data Link Excel to Civil 3D: Steps and Limits
- Creating a Four-Quadrant Scatter Plot in Excel: Steps
- CSV Import Wizard in Excel: How to Import Files
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Array Length: Count Elements, Rows, and ColumnsTo find the number of elements in a rectangular Excel array, multiply its row count by its column…
- Excel Circular Reference Warning: Causes and FixesAn Excel circular reference warning means a formula refers to its own cell, either directly or through a…
- Excel Freeze Panes Shortcut: Keys and How It WorksIn Excel for Microsoft 365 on Windows, select the cell below and to the right of the rows…
- Find Duplicates in Google Sheets: Methods That WorkTo find duplicates in Google Sheets, select the cells, then choose Format > Conditional formatting and use a…
- How to Embed a Teams Video in PowerPoint PresentationsTo put a Teams recording in a PowerPoint slide, open the recording in OneDrive or SharePoint and use…
- How to Add a Header to Every Page in Microsoft WordTo add a header to every page in Word, choose Insert > Header, select a layout, and type…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.