To validate entries in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation > Data Validation, set an allowed type or list on the Settings tab, then choose how Excel should respond to invalid entries. Data validation helps prevent typos and out-of-range values when people enter or edit data; it does not automatically repair existing entries or stop every way data can be pasted into a cell.
Before you begin, decide what values are acceptable and which cells need the rule. A drop-down list works well for a short set of fixed choices, while number, date, time, and text-length rules are useful when entries must fall within limits. Save the workbook before making broad changes, especially if it contains existing data or shared entry forms.
What You’ll Need
- An Excel workbook and a clear idea of which cells people should fill in.
- The accepted values or limits for those cells. For a list, prepare the options in a column or decide on a short set of choices to type directly.
- Optional: a short instruction for users and an error message that explains what to correct.
Step-by-Step
- Select the cells to control. Open the worksheet and select the target cell or range before creating the rule. For example, select the entry cells in a Status column, not the header. To select nonadjacent ranges, hold Ctrl while selecting each range on Windows. If you apply a rule to a whole column, consider excluding its heading and any cells that have a different purpose. Applying the rule to a range at the start makes it easier to confirm exactly where it will operate.
- Open the validation settings. On the ribbon, choose Data > Data Validation > Data Validation. In the dialog, open the Settings tab. On a Mac, use Data > Data Validation; the dialog has the same core options, although ribbon layout and some controls can vary by Excel release. Excel for the web also supports common validation rules, but if an advanced setting is missing or behaves differently, open the workbook in the desktop app.
- Choose the kind of entry to allow. In the Allow box, select List, Whole number, Decimal, Date, Time, Text length, or Custom. Choose Whole number when decimals should be rejected, and Decimal when values such as 2.5 are allowed. For a date or time rule, set a condition such as between and enter the permitted limits. For Text length, choose a comparison and specify the character count. The Data control changes to match the type you select.
- Set the permitted values or limits. For a number, date, time, or text-length rule, choose a comparison such as between, equal to, greater than, or less than, then fill in the limit boxes. For example, a quantity field might accept whole numbers between 1 and 500. Check that the bounds reflect real requirements and that the cell format helps users understand what to enter; formatting a cell as a date does not itself restrict the entered value. For a list, specify its source in the Source box. You can type a small set of options separated by commas, or select a range of cells containing the choices. A source range on the same sheet is simplest to maintain. If the options are on another sheet, define a name for the source range first, then use that name as the list source.
- Choose how blanks and lists behave. In the Settings tab, review Ignore blank. When selected, blank entries are permitted alongside values that meet the rule; clear it if users must fill in the cell. For a List rule, keep In-cell dropdown selected if you want a visible arrow and selectable choices. Clearing that option hides the arrow, but the list rule can still restrict typed entries if the error alert is set to Stop. Do not assume that a drop-down alone makes a field mandatory; blank handling and any separate completeness checks still matter.
- Add guidance and an error response. Open the Input Message tab if you want a short note to appear when a user selects a cell. Give a practical instruction, such as “Choose a status from the list,” rather than repeating the column heading. On the Error Alert tab, select Show error alert after invalid data is entered if you want Excel to respond. Stop blocks an invalid typed entry until it is corrected; Warning lets the user choose whether to continue; Information displays a notice but is less restrictive. Add a clear title and message so users know what rule they broke and what acceptable entry to use.
- Apply the rule and test it. Click OK, then try an allowed value and an invalid one in a test cell in the selected range. Confirm the expected list arrow, message, or rejection appears. If the rule is meant to allow a decimal, test one; if it is a date range, test dates just inside and outside the limits. Undo the test entry if appropriate, then verify a normal entry still works. Testing both sides of a boundary can catch reversed limits or an incorrect comparison before others use the sheet.
- Review existing entries and extend carefully. A new rule does not automatically change or remove values already in the cells. To locate entries that violate the rule, select the relevant cells and choose Data > Data Validation > Circle Invalid Data. Excel marks invalid cells with circles; use Clear Validation Circles in the same menu after reviewing them. If you later add more rows, copy a correctly validated cell or extend the validated range, then check the new cells rather than assuming the rule followed automatically.
For rules that need more than a simple limit, select Custom in the Allow box. Custom validation uses a logical formula to decide whether an entry is accepted, and the formula must return TRUE for an allowed value. Because references can shift across the selected range, create and test a custom rule on a small sample first, then make sure its cell references match the top-left cell of the range being validated.
Common Mistakes
- Applying a rule to the wrong range: Selecting the header or omitting new rows can leave the sheet partly unprotected. Check the target range in the dialog and test a cell near both ends of it.
- Expecting validation to clean old data: Rules generally guide or reject new entries; they do not automatically rewrite values that were already in the cells. Use Circle Invalid Data and review each flagged value before correcting it.
- Using an error style that is too permissive: Warning and Information alerts let users proceed after acknowledging the message. Choose Stop when entries outside the rule must be blocked during ordinary typing.
- Typing a complicated list directly: A comma-separated list is awkward to update, and commas inside an option can be interpreted as separators. Keep changing or longer lists in worksheet cells and point validation to that source instead.
- Assuming pasted values are checked: Copying and pasting can bypass the normal validation prompt, and a paste can replace the validation rule itself. Treat validation as an entry aid, not as a security boundary; review imported or pasted data separately.
- Confusing display format with an entry rule: A cell formatted as Currency, Date, or Percentage may still accept values that do not fit the intended business rule. Set validation limits as well as formatting cells for readability.
Tips
- Keep list options in a clearly labeled source area and arrange them in the order users expect to see them. When possible, use a named range for a list stored on another sheet, so the source is easier to identify and maintain.
- Make error messages actionable. State the acceptable range or point to the list rather than saying only “Invalid entry.” A concise message saves time for people who do not know how the sheet was built.
- For a reusable form, apply the rule to the full expected entry area before distributing the workbook. Include a few spare rows, but avoid applying rules to unrelated cells just to cover every possible future row.
- Protecting a worksheet can help prevent accidental edits to formulas or layout, but it is separate from validation. Decide which cells users can edit and test the protection settings with the intended workflow.
- Keep a backup before replacing existing rules. In the Data Validation dialog, Clear All removes validation settings from the selected cells, so use it only when you mean to remove the rules and their related messages.
Troubleshooting
- The drop-down arrow is missing: Select a cell that should have the list and reopen Data > Data Validation. Confirm Allow is set to List, In-cell dropdown is selected, and the Source points to valid options. The arrow normally appears when the cell is selected, not at all times. Also check that the worksheet is not in a state that prevents normal editing.
- Excel accepts a value you expected it to reject: Check the Error Alert tab and make sure Show error alert after invalid data is entered is selected. If the style is Warning or Information, the user may be able to continue. Pasted content may also avoid the normal prompt, so inspect pasted or imported records separately.
- A valid-looking value is rejected: Recheck the selected rule type and its limits. A decimal cannot pass a Whole number rule, and a date entered as text may not be interpreted as a date. Confirm the workbook’s regional date conventions and test by entering the value directly into a cell with the rule.
- Some cells in a column behave differently: Select an affected cell and open the validation dialog to see whether it has a rule. Copying from another cell or pasting can overwrite validation. Reapply the correct rule to the intended range, then test more than one cell.
- The source list is not updating: Check that the source range includes the newly added option and that the list rule points to the correct range or defined name. If a source is stored on another worksheet, use a defined name rather than assuming a direct cross-sheet reference will work in the Source box.
- Circles remain after correcting data: The circles are review markers, not cell contents. Choose Data > Data Validation > Clear Validation Circles to remove them after checking that the entries are now acceptable.
Frequently Asked Questions
Can I make a cell accept only choices from a drop-down list?
Yes. Set Allow to List, provide the options as a source, and select In-cell dropdown. Set the Error Alert style to Stop if users should not be able to proceed with a typed value outside the list during normal entry. Remember that pasted data can still require a separate review.
Can Excel data validation make a cell required?
It can reject a blank entry when Ignore blank is cleared, but that setting is not a complete check that every required cell in a worksheet has been filled. A blank can remain untouched, and other workflows such as pasting can behave differently. Use a separate review or completeness check before relying on the data.
Does validation apply to data already in the worksheet?
The rule does not automatically correct existing values. Use Circle Invalid Data to flag entries that conflict with the current rule, then review and fix them. If the rule changes later, repeat that review because an entry that was once valid may no longer meet the new limits.
Can I use a list stored on another worksheet?
Yes. Define a name for the cells containing the options, then use that defined name as the list source in Data Validation. This is usually more manageable than maintaining a comma-separated list in the dialog, particularly when choices change or include punctuation.
Can I copy data validation to other cells?
Yes. Copy a cell with the correct rule and paste it into the destination cells, or select the full destination range before creating the rule. Check the result afterward: a paste may replace existing rules, and a copied rule may not cover cells outside the range you intended.
Can data validation stop every invalid value?
No. It is useful for guiding normal entry and catching many mistakes, but copying, pasting, imports, macros, or other editing methods can bypass or replace rules. For important datasets, combine validation with checks after data entry and limit who can edit the workbook structure.
Related
- Excel Power Query: Compare Two Columns Step by Step
- Importing CSV Into Excel: Steps, Settings, and Fixes
- Import CSV Into Excel Without Losing Leading Zeros
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 Add a Watermark in Word to All PagesTo add a watermark to every page in Microsoft Word, open the document in the desktop app and…
- How to Make a Brochure in Word: Design, Layout, and PrintTo make a brochure in Microsoft Word, choose a brochure template from File > New, or set a…
- Excel Dependent Drop-Down Lists Based on Cell ValuesYes. In Microsoft Excel for Microsoft 365 on Windows, you can make a data validation list depend on…
- Google Sheets Date Format dd/mm/yyyy: Formula and StepsTo show dates as dd/mm/yyyy in Google Sheets, select the date cells and choose Format > Number >…
- Vertical Box and Whisker Plot in Excel: How to Make OneYes. In Excel for Microsoft 365 on Windows, select your data, then choose Insert > Insert Statistic Chart…
- Archive Large Amounts of Emails in OutlookTo archive a large number of emails in Outlook, find the messages you want to keep, select them…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.