Excel Data Validation Examples for Better Data Entry

In Excel for Microsoft 365 on Windows, useful data validation examples include a drop-down list for categories, a number rule for a permitted range, a date rule for a reporting period, and a custom formula for a condition Excel’s built-in options do not cover. Select the cells, choose Data > Data Validation, and set the rule on the Settings tab; use the other tabs to guide users and explain rejected entries.

What Data Validation Does

Data validation controls what users can enter in selected worksheet cells. It can restrict entries to whole numbers, decimals, dates, times, text lengths, items on a list, or values that satisfy a custom formula. A rule can also display an input message when someone selects a cell and an error alert when an entry does not meet the rule.

Validation is most useful at the point where people enter information: a sign-up sheet, inventory log, survey, expense tracker, or shared reporting workbook. For example, a department field is easier to keep consistent when users choose “Sales” from a list than when they type the name themselves. Consistent entries make sorting, filtering, summaries, and later analysis more dependable.

It is a guardrail, not a guarantee that every value in a workbook is correct. People can paste or fill data into validated cells in ways that bypass the usual prompt, and imported data may have errors. Validation also checks whether an entry fits a rule, not whether the entry is factually true. A valid date, for example, may still be the wrong date for a particular transaction.

Common Built-In Validation Examples

Choose the validation type that matches the data and the risk of a mistake. Excel’s built-in choices cover many routine situations:

Whole number: Limit a quantity to an integer, such as 1 through 50 seats. This prevents entries such as 2.5 when fractional seats do not make sense.
Decimal: Allow a measurement within a range, such as a temperature from -20 to 45. For a percentage entered as a true percentage, values from 0% to 100% are stored as 0 to 1, so set the bounds accordingly and format the cells as percentages.
List: Offer a drop-down for a fixed set of choices, such as “Open,” “In progress,” and “Closed.” A list is especially helpful when spelling or capitalization differences would split the same category into multiple values.
Date: Require dates within a period, such as the current quarter or a project’s scheduled start and end dates. Set the start and end bounds to match the reporting purpose.
Time: Restrict a time entry to a shift, such as 08:00 through 17:00. Check that users enter actual time values rather than text that only looks like a time.
Text length: Limit an identifier or short code to a specific number of characters. This checks length, not meaning: a six-character entry is not necessarily a valid six-character product code.

For a list whose choices may change, put the items in a separate range and select that range as the list source instead of typing a long, comma-separated set of values into the dialog. A source range is easier to maintain and can be reviewed by the workbook owner. Keep the source list somewhere users can find it, or on a protected support sheet if ordinary users should not change the options.

Practical Rules for Everyday Sheets

Here are examples that show how to translate a real data-entry need into a rule:

Order quantity: Choose Whole number, set the condition to between, and use 1 and 500 as the minimum and maximum if those are the actual business limits. A rule that allows zero would make sense only if zero is a meaningful entry, such as a recorded out-of-stock result.
Request status: Choose List and provide the approved status names. An input message can say “Choose the current stage,” while a Stop alert can prevent a new value that is not on the list.
Expense date: Choose Date and allow dates between the period’s first and last day. This reduces accidental entries from a different reporting period, though the date still needs to be checked against the underlying receipt or record.
Postal or reference code: Choose Text length when the main requirement is a fixed number of characters. If leading zeroes matter, format the destination cells as Text before entry; otherwise Excel may interpret a code such as 00421 as a number and remove the leading zeroes.
Rating: Choose Whole number between 1 and 5 for a five-point rating. Label the scale near the cells so users know whether 1 means the lowest or highest rating.

These settings should reflect how the worksheet is actually used. A maximum that is too restrictive can block legitimate entries, while a range that is so broad it never catches a likely mistake offers little protection. If users need to record exceptions, provide a clear way to flag them rather than silently expanding the allowed range until it stops being useful.

Custom Formula Examples

Choose Allow: Custom when the built-in types cannot express the condition. The formula must evaluate to TRUE for an allowed entry and FALSE for a rejected one. When applying a formula to multiple cells, write it relative to the top-left cell in the selected range; Excel adjusts relative references for the other cells.

For a score in B2:B100 that must be from 0 through 100, select B2:B100 and use this custom formula. With B2 containing 75, the rule evaluates to TRUE; with B2 containing 105, it evaluates to FALSE:

=AND(B2>=0,B2<=100)

For dates in A2:A100 that must fall on a weekday, select A2:A100 and use the following rule. With A2 containing November 2, 2026, a Monday, the result is TRUE; a Sunday date returns FALSE. This checks the weekday, not whether the date is a public holiday:

=WEEKDAY(A2,2)<=5

For a list of unique entries in A2:A100, select that range and use a rule that allows blanks but rejects a value already present elsewhere in the range. With A2 containing “North” and no other “North” in the range, the result is TRUE; if another cell already contains “North,” it is FALSE. This is useful for new entries, but it does not automatically repair duplicates that already exist:

=OR(A2="",COUNTIF($A$2:$A$100,A2)=1)

Test custom rules with an allowed value, a boundary value, and a value you expect to fail. Relative and absolute references matter: in the unique-entry example, the range is fixed with dollar signs, while A2 changes as the rule is evaluated down the column.

How to Set Up and Test a Rule

Select the cells that should share the rule. For a column with a heading in row 1, that might be B2:B200 rather than the entire column.
On the ribbon, choose Data > Data Validation. In the Settings tab, select an option under Allow, then configure the condition, limits, list, or formula.
For a list, enable the in-cell drop-down if users should choose an item from the cell. If the source is a range, check that it contains the intended choices and does not include unwanted blank cells.
Use Input Message to give a short instruction, such as “Enter a whole number from 1 to 50.” Use Error Alert to choose the response to invalid input.
Try a valid entry, an invalid entry, and relevant boundary values. Check that the warning is understandable and that the rule is applied to every intended cell.

On Excel for Mac, the Data > Data Validation command is also available, though dialog layout can vary by release. Excel for the web has data-validation controls too, but some workbook tasks and interface details differ from the Windows desktop app. If a workbook is shared across platforms, check that the rule and its list source behave as intended for the people who will edit it.

Input Messages, Error Alerts, and Existing Data

An input message appears when a user selects a validated cell, so it is a good place to explain a requirement before an error happens. Keep it specific: “Enter a date from April 1 through June 30” is more useful than “Invalid input.” It is optional, and users can dismiss it, so do not rely on it as the only explanation of a complicated field.

Excel’s error-alert styles are Stop, Warning, and Information. Stop blocks an invalid value entered through normal typing. Warning and Information explain the problem but let the user continue, which can be appropriate when exceptions are legitimate and should be reviewed. Choose the least permissive alert that fits the process, and make the message say what to do next.

Adding a rule does not mean Excel has corrected or verified values already in the cells. Review existing entries separately. The Data Validation menu includes a command to circle invalid data, which can help locate entries that do not satisfy the current rule. Recheck after changing the rule, because an entry can be invalid under the new condition even if it was acceptable under the old one.

Limits and Common Mistakes

A frequent misconception is that validation makes a worksheet tamper-proof. It does not. Copying, pasting, filling, formulas, or data imports can result in entries that do not trigger the same warning as ordinary typing; some paste operations can also replace the destination cell’s validation. For important records, combine validation with review, protected worksheet structure where appropriate, and checks in the system that ultimately stores or uses the data.

Validation Compared with Other Checks

Data validation and conditional formatting solve different problems. Validation limits or guides new entry; conditional formatting changes a cell’s appearance to draw attention to a condition. For example, validation can constrain a score to 0–100, while conditional formatting can highlight scores below a target. Conditional formatting alone does not stop an invalid score from being entered.

For a workbook that receives data from several sources, add a separate quality check as well. Filters, formulas, PivotTables, or a review report can help identify duplicates, missing fields, and values that are unusual in context. A strong process uses validation to prevent simple mistakes and later checks to catch errors that validation cannot see.

Frequently Asked Questions

How do I create a drop-down list in Excel? Select the destination cells, choose Data > Data Validation, select List under Allow, then specify the list items or point to a source range. Test the drop-down and make sure its source contains only the intended choices.

Can Excel data validation allow only whole numbers? Yes. In Data Validation, choose Whole number, select a condition such as between, and enter the permitted limits. A custom rule is not needed for a simple numeric range.

Does data validation stop people from pasting invalid data? Not reliably. Paste, fill, formulas, and imports can bypass the usual entry alert or replace validation settings. Check important data after entry and use additional review or system-level controls when errors have significant consequences.

Can I apply validation to cells that already contain data? Yes, but applying a rule does not automatically fix or remove existing entries that fail it. Use a separate review, such as circling invalid data from the Data Validation menu, and correct any values that need attention.

What is the difference between a Stop alert and a Warning alert? Stop blocks an invalid entry made through normal typing. Warning tells the user the entry does not meet the rule but allows the user to continue; use it when exceptions are possible and should remain an explicit choice.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.