In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Data Validation to control what users can enter. You can create a dropdown list, restrict entries to values such as whole numbers or dates, and display guidance or an error alert when an entry breaks a rule.
What Excel Data Validation Does
Data Validation is a cell-level rule for guiding and checking input. It is useful in forms, trackers, budgets, inventory sheets, and shared workbooks where consistent entries matter. For example, a status column can offer only “Not started,” “In progress,” and “Done,” while a quantity column can accept only whole numbers from 1 to 500.
Validation can make a worksheet easier to use, but it is not a security feature or a guarantee that every value in a workbook is valid. It helps prevent accidental mistakes during normal entry and can explain what a cell expects. Existing values are not automatically corrected when you add a rule, and certain ways of putting data into cells can bypass the rule.
Validation Types and When to Use Them
Open the Data Validation dialog and use the Allow list to choose the kind of rule. The main options cover common entry needs:
- Whole number and Decimal set acceptable numeric values, such as a quantity above zero or a percentage within a chosen range.
- List presents allowed choices in a dropdown, such as a department name or order status.
- Date and Time limit entries to a date or time range, such as dates in the current calendar year.
- Text length limits how many characters a cell can contain, useful for short codes or fixed-length identifiers.
- Custom uses a formula that evaluates to TRUE or FALSE, allowing rules that depend on other cells or combine conditions.
Many types offer conditions such as between, equal to, greater than, or less than. The Ignore blank setting is also available. Leave it selected if empty cells are acceptable; clear it when a cell must contain a value, while remembering that required-field behavior can depend on how data is entered and checked.
Create a Dropdown List
A list is often the most visible and useful kind of validation. To create one in Excel for Microsoft 365 on Windows:
- Select the cell or range where users should choose an item. Selecting a whole input column range at once applies the rule consistently to those cells.
- Choose Data > Data Validation. In the dialog, open the Settings tab if it is not already shown.
- Set Allow to List. Use the range selector beside Source to select the cells containing the permitted choices, or enter short choices separated by commas.
- Make sure In-cell dropdown is selected, then choose OK. Select a validated cell to see its dropdown arrow and choose an item.
A source range is generally easier to maintain than typing a long set of choices into the dialog. Put the options in a clearly labeled area, preferably on a separate worksheet if they would clutter the form. If the source is on another sheet, define a name for that range using Formulas > Define Name, then use the name as the list source with an equals sign in front. This avoids the cross-sheet reference limitations of the Source box. On Mac, the Data Validation command and its core settings are available too, though the dialog’s layout can differ slightly.
Set Numeric, Date, and Length Rules
For a numeric limit, select the cells, open Data Validation, choose Whole number or Decimal, and set the condition and bounds. A quantity field might allow whole numbers between 1 and 500. A decimal rule is more suitable for measurements such as weight or a rate. Choose bounds that match the worksheet’s real purpose; a rule that is too broad may not catch likely mistakes, while one that is too narrow can reject legitimate entries.
Date and time rules work similarly. For example, a date field can allow dates between two endpoints, or dates before or after a specified date. If those endpoints live in worksheet cells, use cell references in the rule rather than typing dates that will quickly become outdated. Text length is useful for constraints such as a four-character department code, but it checks the number of characters, not whether those characters follow a particular pattern. Use a custom rule or another checking method if the format itself matters.
Add an Input Message or Error Alert
Data Validation can explain a rule at the moment someone selects a cell. In the dialog’s Input Message tab, enable the message and provide a short title and instruction, such as “Enter a quantity from 1 to 500.” The message is a prompt, not a permanent note displayed in the cell, so it works best when the instruction is concise and specific.
The Error Alert tab controls what happens when someone types a value that violates the rule. You can set a title and message and choose an alert style:
- Stop rejects the invalid value during ordinary typing and asks the user to correct it or cancel.
- Warning warns the user but allows them to keep the value if they confirm.
- Information displays a notice, but permits the entry to continue.
For a strict list or numeric boundary, Stop is usually the clearest choice. Warning or Information can be appropriate when the rule is guidance rather than an absolute requirement. If the error alert is disabled, Excel will not show that validation message when an invalid value is entered.
Use Custom Rules for More Specific Checks
Choose Custom in the Allow list when the built-in types cannot express the condition. The formula must return TRUE for an allowed entry and FALSE for a rejected one. Its cell references should be written with the first cell in the selected range in mind; Excel adjusts relative references for the other cells in that range.
For example, suppose the selected input range is D2:D100 and each entry must be a whole number from 1 through 100. With D2 as the active cell when setting the rule, a custom formula can be:
=AND(D2>=1,D2<=100,MOD(D2,1)=0)
This returns TRUE for an integer within the inclusive range and FALSE for a value outside it or a decimal. The example assumes the entry is in D2 and the rule is applied to D2:D100; using D2 as a relative reference lets Excel evaluate D3 for the next row, and so on. Custom formulas can also refer to other cells, but test the rule on several rows to confirm that relative and absolute references behave as intended.
Maintain Lists and Apply Rules to More Cells
When a dropdown’s choices change, update the source list and check that the validation source still covers all the choices. A fixed range such as a set of cells from G2 through G8 will not automatically include a new item typed below G8. For lists that grow, use a suitably expanded source range or a named range designed to cover the list. Keeping choices together in one place makes it easier to audit and update them.
To reuse a rule, copy a validated cell and use Paste Special > Validation where that option is available, or copy and paste the cell if its contents and formatting should also be copied. To inspect a cell’s rule, select it and reopen Data > Data Validation. To remove a rule, select the affected cells, open the dialog, and choose Clear All before confirming. That removes validation settings, so check the selection carefully if the worksheet contains other validated fields.
Limits and Common Problems
Validation is not a dependable way to lock down data. Copying and pasting or filling cells can put invalid values into a validated range without the same prompt shown during ordinary typing. It also does not repair mistakes that were present before the rule was added. If accuracy matters, check the finished data separately; Excel’s Data Validation menu includes an option to circle invalid data in applicable desktop versions, which can help identify entries that fail the current rule.
If a dropdown arrow does not appear, first select a cell that actually has a List rule, then check that In-cell dropdown is selected in the rule’s Settings. If a new choice is missing, inspect the source range rather than the list cell itself. If Excel rejects an otherwise valid entry, check whether the selected condition, bounds, decimal-versus-whole-number type, or error-alert settings are too restrictive. Also verify that the rule applies to the intended cells: a copied rule can retain references that point to the wrong row or column.
Rules copied from another workbook may refer to a missing named range or a source list that was not copied. Reopen the dialog and confirm the Source entry and any defined names. When a custom formula behaves unexpectedly, check the first cell of the selected range, the reference anchors, and whether the formula returns TRUE for a value that should be accepted.
How Validation Compares with Other Excel Tools
Validation is for controlling or prompting input; it is different from conditional formatting, which changes a cell’s appearance based on its value. For example, a validation rule can limit an entry to an approved status, while conditional formatting can color completed rows green. Formulas can also flag or calculate based on data, but they do not necessarily stop a user from typing an unwanted value. In practice, a well-designed workbook may use validation to guide entry and a separate review formula or formatting rule to help spot problems later.
Frequently Asked Questions
Where is Data Validation in Excel?
In Excel for Microsoft 365 on Windows, select the target cells and choose Data > Data Validation on the ribbon. The dialog contains settings for the rule, input message, and error alert.
How do I make a dropdown list in Excel?
Select the cells, open Data Validation, set Allow to List, choose or enter the source choices, and make sure In-cell dropdown is enabled. A worksheet range is usually easier to maintain than a typed list.
Can I use a list from another worksheet?
Yes. Define a name for the source cells with Formulas > Define Name, then use that name as the list source in Data Validation. This is a practical way to refer to choices stored on a different worksheet.
Why can invalid data still appear in a validated cell?
Validation is not a security barrier. Pasting or filling can bypass the usual typing prompt, and rules do not fix earlier entries. Review or check the data separately when accuracy is important.
How do I remove Data Validation?
Select the validated cells, choose Data > Data Validation, then select Clear All in the dialog and confirm. Only clear the intended range, because the action removes the rule from every selected cell.
Related
- Conditional Formatting in Excel for the Web: A Guide
- Conditional Formatting in Excel: Rules and Examples
- Excel Conditional Formatting Based on Text: How to Set It Up
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set an Out of Office Reply in Outlook on PhoneTo set an out-of-office reply in Outlook on your phone, open the Outlook app, go to Settings, select…
- Merge Columns in Google Sheets: Methods, Tips, and FixesTo join the text or values in two Google Sheets columns, enter a formula such as =A2&" "&B2…
- Select Non-Empty Cells in Excel: Methods That WorkTo select every non-empty cell in a chosen range—including both typed entries and formulas—in Excel for Microsoft 365…
- Excel Not Equal Sign: How to Use the <> OperatorExcel’s not equal sign is <>. Put it between two values in a formula to check whether they…
- Can You Custom Freeze Panes in Excel? Here’s HowYes. In Excel for Microsoft 365 on Windows, select the cell immediately below the rows and to the…
- Heat Map Examples in PowerPoint: Ideas and How to Make ThemHeat map examples in PowerPoint include a sales-by-region matrix, a weekly activity calendar, a survey response grid, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.