To add data validation in Excel for Microsoft 365 on Windows, select the cells, then choose Data > Data Validation > Data Validation. Choose a rule on the Settings tab, configure its limits or list source, and select OK. Validation can provide a drop-down list or restrict entries to values such as dates, whole numbers, or text that meets a condition.
What data validation does
Data validation lets you set rules for what people can enter in particular cells. It is useful for making a workbook more consistent: for example, a status column can accept only a few approved labels, or a quantity cell can accept only whole numbers within a defined range. You can also show an input message to explain what belongs in a cell and an error alert when an entry breaks the rule.
Validation applies to the selected cells, not automatically to every cell on a worksheet. A rule can be copied to other cells, and you can edit or remove it later. The feature is meant to guide routine entry and catch mistakes, not to secure confidential information or guarantee that all data in a workbook is valid. Pasting data into a cell can sometimes bypass the usual validation prompt, so important data may need a separate review.
Add a validation rule step by step
- Select the cell or range where entries should be controlled. For example, select B2:B100 to validate a set of status entries.
- On the ribbon, choose Data > Data Validation > Data Validation. In some window sizes, the Data Validation control may appear as a single button on the Data tab; open its menu and choose Data Validation.
- On the Settings tab, choose a value under Allow, such as List, Whole number, Decimal, Date, Time, Text length, or Custom.
- Set the conditions that appear for that type. A whole-number rule, for instance, can require a value between a minimum and maximum.
- If you want a drop-down arrow, make sure In-cell dropdown is selected for a List rule.
- Optionally use Input Message and Error Alert to explain the rule. Select OK to apply it.
In Excel for Mac, select the cells and use Data > Data Validation; the dialog and available controls may be arranged differently from the Windows ribbon. Excel for the web also provides data validation from the Data tab, though its interface and available editing options can differ. If a workbook relies on a complex rule, check it in the desktop version where it was created.
Create a drop-down list
A list is a common way to keep labels consistent. Suppose a task tracker uses the choices Not started, In progress, and Complete. Select the status cells, open Data Validation, choose List under Allow, and enter the choices in the Source box separated by commas. Keep In-cell dropdown selected, then choose OK. The selected cells should show an arrow when selected, allowing a person to choose a listed item rather than type it.
For a list that may change, keep the choices in worksheet cells instead of typing them into the Source box. This makes it easier to update the options in one place. With the options in H2:H4, select the cells to validate and use the following source in the List rule:
With the list values in H2:H4, the Source box can contain =$H$2:$H$4. The dollar signs keep the source range fixed when the validation rule is applied to other cells.
A range-based list is easier to maintain, but its source range must be accessible to the workbook and correctly referenced. If you add a new option below the existing source range, it will not necessarily appear in the drop-down unless the source is expanded or designed to include that new cell. For a longer or reusable list, an Excel table or a named range can help manage the source, but check the validation dialog after changing the workbook structure.
Choose the right kind of rule
The Allow menu determines what kind of condition Excel checks. Choose a rule based on the data people should enter, not just on the appearance of the cell. Setting a number format to display dates, for example, does not by itself restrict what can be entered.
- List: Restricts entries to items in a list and can show an in-cell drop-down.
- Whole number: Allows integer values, with conditions such as greater than, less than, or between.
- Decimal: Allows numeric values that can include a fractional part, subject to a condition.
- Date or Time: Limits entries to dates or times that meet the specified condition.
- Text length: Limits the number of characters in an entry, which can be useful for short codes.
- Custom: Uses a worksheet formula that returns TRUE for an allowed entry and FALSE for one that should be rejected.
For example, a quantity field might allow whole numbers between 1 and 500, while a feedback field might permit up to 250 characters. A date rule can limit a form to dates within a reporting period. Validation does not automatically convert unsuitable input into the right type: if a user enters a date as text, Excel may treat it differently from a real date value.
Use a custom formula for a specific condition
Choose Custom when the built-in categories cannot express the rule you need. For example, to allow only values from 1 through 100 in A2:A20, select A2:A20, choose Custom, and enter this formula. The reference A2 is relative, so Excel adjusts it for each row in the selected range.
With the cells to validate in A2:A20, enter =AND(A2>=1,A2<=100) in the Formula box. The rule returns TRUE for a value such as 75 and FALSE for 0 or 101.
Custom validation formulas must be written with the top-left cell of the selected range in mind. If the range starts at A2 but the formula is written as if it starts at A1, the rule can check the wrong row. A formula can also refer to other cells, which is useful for rules that depend on an earlier choice. Keep the logic understandable and test both an allowed and a disallowed entry after applying it.
Configure messages and error alerts
The Input Message tab can display a short prompt when someone selects a validated cell. It is a good place to state a unit or give a concise instruction, such as Enter a whole-number quantity from 1 to 500. The message is guidance; it does not change the rule itself.
The Error Alert tab controls what happens when someone types an entry that violates the rule. Excel offers three styles:
- Stop: Rejects the invalid entry and requires the user to correct it or cancel.
- Warning: Warns about the entry but lets the user choose whether to continue.
- Information: Shows a notice but allows the entry to remain.
Use Stop when the rule must be followed during ordinary typing. Warning or Information can be appropriate when exceptions are sometimes legitimate. You can edit the alert title and message so the person entering data knows what to do next, rather than seeing a vague instruction to try again.
Edit, copy, or remove validation
To change a rule, select a cell that uses it and return to Data > Data Validation > Data Validation. Update the settings, message, or alert, then select OK. If a range contains different rules, inspect a representative cell from each part rather than assuming one selection has a single shared setup.
To apply an existing rule elsewhere, copy a cell with the rule and use Paste Special > Validation on the destination cells. This copies the validation without replacing the destination’s other contents and formatting. Review the result, especially when the original rule uses relative cell references, because references may adjust as the rule is copied.
To remove a rule, select the affected cells, open the Data Validation dialog, and choose Clear All, then select OK. Clearing validation removes the rule; it does not erase the cell contents. If you need to remove rules from a larger area, first confirm the selection so you do not clear rules from unrelated cells.
Common problems and how to fix them
- The drop-down arrow is missing: Check that the cell has a List rule and that In-cell dropdown is selected. Also make sure the cell itself is selected; the arrow may only appear while it is active.
- An invalid value remains in the cell: Validation does not necessarily clean up data entered before the rule was added. It can also be bypassed in some paste or fill operations. Review existing values and test the rule with direct typing.
- The list is incomplete: Check the Source range for omitted cells and confirm that new options were added inside the referenced range. A typed list may also contain a spelling or separator error.
- The rule seems to check the wrong cell: For a custom formula, confirm that its relative reference corresponds to the top-left cell selected when the rule was created. Check whether dollar signs are fixing a row or column unintentionally.
- Some cells behave differently: The range may contain more than one validation rule, or part of it may have been pasted over. Inspect the affected cells and reapply the intended rule to the correct range.
To look for existing entries that violate rules, select the relevant cells and use Data > Data Validation > Circle Invalid Data when that command is available in the desktop interface. Excel marks entries that do not meet their current validation rules. Correct the marked values, then use Clear Validation Circles to remove the circles. This check is especially helpful after importing or pasting information.
Frequently Asked Questions
How do I add a drop-down list in Excel?
Select the cells, choose Data > Data Validation, set Allow to List, enter a comma-separated list in Source or select a range containing the choices, and keep In-cell dropdown selected.
Can I add data validation to an existing list of values?
Yes. Select the cells and apply a rule. Excel does not automatically replace existing entries that fail the new rule, so inspect the old values and use Circle Invalid Data where available to identify problems.
Can I make an Excel drop-down list depend on another cell?
Yes. A custom validation formula can refer to another cell and evaluate whether the proposed entry is permitted. The formula must be designed around the top-left cell of the validated range, so test it with different choices and rows.
Does data validation prevent people from pasting invalid data?
Not reliably in every workflow. Validation is useful for guiding normal entry, but pasted or filled data can evade its prompts. Check imported data separately when correctness matters.
How do I remove data validation without deleting the values?
Select the validated cells, open the Data Validation dialog, choose Clear All, and select OK. This removes the validation settings while leaving the existing cell contents in place.
Related
- How to Enter an Absolute Cell Reference in Excel
- How to Use Absolute Cell References in Excel on Dell
- How to Name the Inventory Range in Excel (Step-by-Step)
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Find Duplicates in Excel: A Practical Step-by-Step GuideTo find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- How to Insert a Horizontal Line Under a Heading in WordTo add a line beneath a heading in Microsoft Word, click in the heading paragraph, then choose Home…
- Embed Video in PowerPoint Using Embed Code: Steps & LimitsYes. In PowerPoint for Microsoft 365 on Windows, go to Insert > Video > Online Videos, paste the…
- Hanging Indent in Word: Shortcuts, Setup, and FixesIn Microsoft Word for Windows, press Ctrl+T to create or increase a hanging indent in the paragraph containing…
- Edit Data Validation Restrictions in ExcelTo edit a data validation restriction in Excel for Microsoft 365 on Windows, select the cell or range,…
- Rows Excel Shortcut: Insert, Delete, and Select RowsIn Excel for Microsoft 365 on Windows, select a whole row and press Ctrl+Shift+Plus to insert a new…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.