In Excel, Data Validation is a feature that controls what users can enter in selected cells; it is not a worksheet function you type into a cell. In Microsoft Excel for Microsoft 365 on Windows, open Data > Data Validation to set rules such as a dropdown list, a permitted number range, or a custom formula.
What Excel Data Validation does
Data Validation helps make spreadsheet entries more consistent. You can guide someone toward an approved choice, limit a cell to dates within a period, or reject a number outside an allowed range. It can also show an instruction when a cell is selected and display an alert when an entry does not meet the rule.
Validation applies to cells, not to an entire workbook’s meaning. For example, a rule can restrict a status cell to Pending, Approved, or Declined, but it cannot establish that an approval is actually authorized. Think of it as an entry control and helpful prompt, not a substitute for review, business logic, or security.
The feature is useful in shared templates, sign-up sheets, inventory logs, survey forms, and other workbooks where inconsistent spellings or impossible values can make sorting and analysis harder. A dropdown that offers one spelling of each department, for instance, avoids having separate entries for Sales, sales, and Sale.
Create a dropdown list
A dropdown is one of the most familiar validation rules. To make one in Excel for Microsoft 365 on Windows:
- Select the cells that should contain the choices.
- Choose Data > Data Validation.
- On the Settings tab, set Allow to List.
- Enter the choices in Source, or select the worksheet cells containing them.
- Leave In-cell dropdown selected, then choose OK.
For a short, stable list, you can type items separated by commas in the Source box, such as North,South,West. Regional settings can affect which separator Excel expects, so selecting a range of cells is often more dependable for longer lists. If the source list is on the same worksheet, select its cells; for a list stored on another worksheet, a defined name for the source range is a practical approach. Create or review names through Formulas > Name Manager.
Keeping choices in cells makes the list easier to maintain: edit the source entries instead of reopening the validation dialog to change a typed list. If the choices may grow, plan for that growth by using a suitably sized source range or a defined name that covers the intended entries. A dropdown displays choices; it does not automatically sort them or prevent someone from changing the source list.
Validation rule types and settings
In the Settings tab, the Allow menu offers several kinds of restrictions. The exact options shown depend on the selected rule, but common choices include:
- Whole number: permits integers, optionally between limits or above or below a value.
- Decimal: allows numeric values that can include a fractional part.
- List: provides a set of approved choices, usually as an in-cell dropdown.
- Date or Time: limits entries to a specified date or time condition.
- Text length: controls how many characters a cell can contain.
- Custom: evaluates a formula that returns TRUE for an allowed entry and FALSE for a disallowed one.
For numeric, date, time, and text-length rules, choose a condition such as between, equal to, greater than, or less than, then supply the required limit or limits. The Ignore blank option can allow an empty cell to remain valid under the rule. It does not mean that Excel will erase a value or require the cell to be filled in. If completing the field is mandatory, use a separate check or clearly explain the requirement to the user.
Use a custom formula for a more specific rule
Custom validation is useful when the built-in choices do not express the condition you need. The formula must evaluate to TRUE for an entry Excel should accept. Cell references are normally written relative to the top-left cell of the selected range, so the same rule can adjust as it applies down the selection.
For example, suppose B2:B50 is the entry range and each value must be from zero through 100. Select B2:B50, open Data > Data Validation, choose Custom, and enter =AND(B2>=0,B2<=100). Because B2 is the top-left cell, Excel applies the check to each row in relation to that row. The rule accepts 75 and rejects 125. If you instead selected a range beginning at B3 but left the formula referring to B2, the check would look at the wrong row.
Custom rules can also check relationships between cells. For a date entry in A2:A100 that must be on or after January 1, 2026, select A2:A100 and use =AND(ISNUMBER(A2),A2>=DATE(2026,1,1)). The ISNUMBER test helps ensure the entry is a real Excel date value rather than text that merely looks like a date. Excel stores dates as serial numbers, which is why the numeric check is relevant.
More complex rules need careful testing. References with dollar signs stay fixed when the rule is applied to other cells, while relative references move with the cell being checked. Test both acceptable and unacceptable values in a few cells before applying a custom rule across a large input area.
Input messages and error alerts
The Input Message tab lets you show a title and short instruction when a user selects a validated cell. Use it to explain the expected unit or format, such as Enter a whole number of items. This is especially helpful when a rule is not obvious from the worksheet headings.
The Error Alert tab controls what happens when someone types a value that fails the rule. A Stop alert blocks the invalid typed entry unless the user corrects it. Warning and Information alerts notify the user but allow them to continue, so choose those only when an exception may be legitimate. You can turn off the alert, but then the rule may provide little visible feedback.
Alerts are not a complete barrier to invalid data. Copying and pasting can introduce values that do not meet a rule, and pasting may replace validation settings on destination cells. For important workbooks, check the data after importing or pasting and consider adding a separate audit column or review step.
Useful examples for everyday spreadsheets
Validation works best when the rule matches a clear requirement and the spreadsheet makes that requirement visible. Practical uses include:
- Order quantity: allow whole numbers greater than zero, so a quantity is not accidentally entered as a decimal or negative number.
- Request date: limit dates to the reporting period, while stating the permitted dates in a nearby note.
- Task status: use a dropdown with consistent labels such as Not started, In progress, and Complete.
- Short identifier: limit text length when an ID has a fixed character count, while remembering that length alone does not verify the ID is genuine.
- Unique entries: a custom rule can check whether a value appears once in a range, but blanks and existing duplicates require thought when designing and testing the rule.
For a simple form, pair validation with clear headings, sample formats, and an obvious distinction between input cells and calculated cells. A user should not have to guess whether a date means the start date, due date, or date of entry. Instructions reduce errors that a technical rule cannot identify.
Common problems and limitations
A dropdown arrow may not appear if In-cell dropdown is unchecked, or if the cell is not selected. A list may also be empty because its source range contains blanks or points to the wrong cells. Confirm the Source entry in the validation dialog, then check that the source values are present and spelled as intended.
If an invalid value appears despite a rule, consider whether it was pasted or brought in by an import. Data Validation is not a guarantee that every value in a workbook complies. It also does not automatically repair existing entries when a rule is added or changed. Review the current data separately, especially before using it in reports or calculations.
Another frequent issue is selecting a range and writing a custom formula for the wrong active cell. Excel interprets relative references from the selection’s top-left cell. Mixed and absolute references can be useful, but they should be deliberate. Finally, changing the rule for one cell does not necessarily update every similar cell; inspect the full intended range in the dialog.
Change, remove, or check a rule
Select a validated cell and choose Data > Data Validation to inspect or edit its settings. To find cells with validation, use Home > Find & Select > Data Validation. Depending on the selection and Excel build, this can select cells with validation in the current worksheet, which helps identify inconsistent areas.
To remove a rule, select the relevant cells, open Data > Data Validation, and choose Clear All in the dialog, then confirm. This removes validation settings from the selected cells; it does not delete their existing contents. If you copy a cell that has validation and paste it elsewhere, the pasted formatting and settings can affect the destination, so use Paste Special options or review the destination rule when necessary.
Excel for Mac has the same core validation concepts; the Data Validation command is on the Data tab, though ribbon layout and dialog presentation can differ by version. Excel for the web supports common validation tasks, including dropdown lists, but some advanced editing options can differ from desktop Excel. If a rule’s settings are missing or difficult to edit in the browser, open the workbook in the desktop app and check the rule there.
How it differs from formulas and sheet protection
A worksheet formula calculates a result; Data Validation controls entries made in selected cells. These tools can complement each other. A formula might calculate a balance from entered transactions, while validation limits transaction categories to a consistent list. Validation does not replace a formula that checks a result or flags an error after data is entered.
Sheet protection serves a different purpose: it can restrict editing of locked cells or workbook structure, depending on how it is configured. It does not make a validation rule a security control, and worksheet protection is not a way to guarantee that data is trustworthy. For a user-facing template, use validation to guide entry, formulas to calculate or audit, and protection only when you need to limit accidental edits.
Frequently Asked Questions
Is Data Validation an Excel function?
No. It is a spreadsheet feature accessed from Data > Data Validation, rather than a formula such as SUM that you enter in a cell. Its Custom option uses a worksheet formula to test whether an entry meets a condition.
How do I make a dropdown list in Excel?
Select the cells, choose Data > Data Validation, set Allow to List, and provide the choices in Source. Select In-cell dropdown if you want the arrow and options to appear in the cell.
Can Excel Data Validation stop users from pasting invalid data?
Not reliably. Validation alerts can reject invalid values typed directly into a cell, but pasted or imported data may bypass the rule or replace it. Check data after pasting and use a separate audit when accuracy matters.
Can I allow only unique values?
Yes, a Custom rule can use a counting formula to detect repeats, but the references must match the selected range and blank-cell behavior should be planned. Test it against existing entries and duplicates; validation will not clean duplicates that were already present.
Does Data Validation work in Excel for Mac or the web?
Common validation features are available in both, though ribbon layouts and some editing details differ. On Mac, look on the Data tab. If an advanced rule is hard to inspect or edit in Excel for the web, use the desktop app.
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
- PowerPoint Page Numbers: Add Them to Your SlidesTo add page numbers in PowerPoint, open Insert > Header & Footer, check Slide number on the Slide…
- PowerPoint Slideshow With Music Loop: Setup and FixesYes. In PowerPoint for Microsoft 365 on Windows, insert an audio file, select it, then choose Playback >…
- How to Get Rid of All Track Changes in Microsoft WordTo get rid of tracked changes in Word, choose whether to keep or discard the proposed edits, then…
- How to Join Two Columns in Google Sheets With a FormulaTo join two columns in Google Sheets, enter a formula such as =A2&" "&B2 in an empty column,…
- How to Add a Background Watermark in Word for Every PageTo add a background watermark in Word for Microsoft 365 on Windows, open the document, choose Design >…
- How to Add a Side Header in Microsoft Word DocumentsTo add a vertical side header in Word, open the document’s header, insert a text box, rotate it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.