Excel Data Validation Examples and Setup Tips

Excel data validation examples include dropdown lists for status fields, number limits for quantities, date rules for deadlines, and custom formulas for checks such as preventing duplicate IDs. In Microsoft Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, and set the rule on the Settings tab.

What data validation does

Data validation lets a workbook’s author define what users may enter in selected cells. A rule can allow only a whole number within a range, a date within specified limits, text of a certain length, a value from a list, or an entry that meets a custom formula. The purpose is to prevent avoidable entry errors and make a worksheet easier to use—not to make its contents impossible to change.

Validation applies to the cells selected when the rule is created. It can also display an input message when a user selects a cell and an error alert when an entry fails the rule. Existing cell contents are not automatically corrected when a rule is added, so check old entries separately if the sheet already contains data.

Example: Create a dropdown list

A dropdown is useful when users should choose among standard labels, such as a task status, department, or payment method. It reduces inconsistent entries such as “In progress,” “In Progress,” and “Progress.”

Select the cells that need the dropdown, such as B2:B100.
Choose Data > Data Validation. On the Settings tab, set Allow to List.
Enter the options in Source, separated by commas, for example Open,In progress,Closed, then select OK.

With the cells selected, the dropdown arrow lets a user choose an item. A typed value that is not on the list is rejected if the error alert is set to Stop. For a short list, entering options directly is convenient. For a longer list, keep the choices in worksheet cells and use that range as the source; this makes updates easier and avoids maintaining a long comma-separated string.

For a list stored on another sheet, create a named range for the choice cells and use the name as the validation source. For example, select the choices on a worksheet named Lists, choose Formulas > Define Name, and give the range a simple name such as Departments. In the validation Source box, enter =Departments. This approach is also handy when several input cells or worksheets need the same choices.

Example: Restrict numbers and quantities

Choose Allow: Whole number when fractional values do not make sense, such as a count of units or people. Choose Allow: Decimal for values that may include a fraction, such as a measured weight. The Data Validation dialog offers comparison rules including between, greater than, less than, and equal to.

For example, to allow a quantity from 1 through 500, select the quantity cells, set Allow to Whole number, set Data to between, and enter 1 and 500 as the minimum and maximum. A value of 24 passes; 0, 501, and 2.5 do not. If zero represents a legitimate quantity in the worksheet, set the minimum to 0 instead. Choose limits that match the real business process rather than relying on a generic example.

A percentage rule needs particular care because Excel stores percentages as decimal values. If a cell is formatted as Percentage, entering 15% stores 0.15. Decide whether the validation limits should be entered as 0 and 1, for 0% to 100%, or as another appropriate decimal range. Cell formatting changes how a number appears; it does not by itself restrict what can be entered.

Example: Limit dates, times, and text length

For a date field, set Allow to Date and choose a comparison such as between. A simple example is allowing dates from January 1 through December 31 of a specified year. Excel stores dates as serial numbers, so a valid date can still appear unexpectedly if the cell is formatted as General or as a number; apply a date format when that helps users interpret the value.

Choose Allow: Time to constrain entries to a time interval, such as working hours. A text-length rule is useful for fields such as a short code or an account reference: set Allow to Text length, choose a comparison, and specify the permitted number of characters. This checks the number of characters, not whether those characters follow a particular pattern. For exact patterns or more involved checks, use a custom formula or another controlled input process.

Date rules that use fixed dates do not move forward automatically. If a workbook must always accept dates in the current year, a custom formula can make the boundaries dynamic. With the date entered in A2 and the rule applied to A2:A100, use =AND(A2>=DATE(YEAR(TODAY()),1,1),A2<=DATE(YEAR(TODAY()),12,31)). For a date in the current year, the formula evaluates to TRUE; for a date outside that year, it evaluates to FALSE. Because TODAY updates with the system date, this rule’s allowed period changes over time.

Example: Use a custom formula

Set Allow to Custom when the built-in types do not express the rule you need. A custom validation formula must return TRUE for an allowed entry and FALSE for a disallowed one. Write the formula as if it applies to the first cell in the selected range, then use relative and absolute references deliberately.

To prevent duplicate IDs in A2:A100, select that range and use =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). If A2 contains the ID Q17 and it appears once in the range, the formula returns TRUE; if Q17 appears twice, it returns FALSE. The OR condition allows an empty cell, while the COUNTIF test permits a nonblank value only when it occurs once. The dollar signs keep the full lookup range fixed as the rule is applied to other rows, while A2 adjusts row by row.

To require an end date in column B to be on or after its corresponding start date in column A, apply this formula to B2:B100: =OR(A2="",B2>=A2). If A2 is March 10 and B2 is March 12, the test returns TRUE; if B2 is March 9, it returns FALSE. The first part permits an end date to remain blank while the start date is blank. If the worksheet requires both dates, design the rule accordingly and consider whether users should enter the start date first.

When testing a custom rule, try both a valid and an invalid sample in the first cell and in a later row. A misplaced dollar sign or an incorrect starting reference can make a rule appear to work in one row but check the wrong cells elsewhere.

Set input messages and error alerts

Validation is clearer when users know what a field expects. In Data Validation, use the Input Message tab to add a short title and instruction, such as “Order quantity” and “Enter a whole number from 1 to 500.” The message can appear when a user selects a validated cell. Keep it specific: instructions that explain the allowed format are more useful than a generic warning.

The Error Alert tab controls the response to an invalid entry. Stop blocks the entry until the user changes it. Warning and Information display a message but allow the user to proceed, so they are less strict. Choose an alert style based on whether the rule is essential or advisory. A warning is not a substitute for a rule that must be enforced.

Apply, edit, and check a rule

To create or change a rule in Excel for Microsoft 365 on Windows, select the target cells and choose Data > Data Validation. Configure the Allow, Data, and value fields on Settings, then review Input Message and Error Alert as needed. If the selected cells already contain different validation rules, Excel may report that the selection has more than one type; inspect the affected cells and apply a consistent rule where appropriate.

To find validated cells, use Home > Find & Select > Data Validation. This can help locate rules in a large workbook before editing. To remove a rule, select its cells, open Data Validation, and choose Clear All in the dialog. Clearing validation does not necessarily clear the cell contents; it removes the restriction and related validation settings.

Excel for Mac has a similar Data Validation feature on the Data tab, although the ribbon layout and dialog presentation can vary by release. Excel for the web also provides data validation controls, but the available options and interface can differ from desktop Excel. If a workbook uses complex custom rules or named ranges, check the rule in the desktop application when the web interface does not expose the setting you need.

Common problems and limitations

A frequent misconception is that validation guarantees every cell will always contain an approved value. It does not. Copying and pasting, filling cells, importing data, or editing the workbook in another tool can introduce values that do not satisfy the rule. Validation is an entry aid, not a security boundary or a complete audit system. For important datasets, check the resulting data as well as setting rules at entry time.

Another common issue is applying a rule after invalid values are already present. The rule does not retroactively remove them. Use a filter, a review column, or an appropriate check to find older exceptions. Also remember that a list source must stay accurate: if its source range omits a new department, that department will not appear in the dropdown. Named ranges make the source easier to reuse, but they still need to include the intended choices.

Blank cells can also cause confusion. Many validation types allow blanks, and the Ignore blank option affects how blanks are treated by the rule. A rule that permits blank values does not require users to fill in the cell. If a field is mandatory, communicate that requirement and add a separate completeness check, such as a review step or a formula-based status indicator.

Choosing the right validation example

Start by identifying the kind of mistake you want to prevent. Use a list when the allowed values are known and finite; a whole-number or decimal limit for numeric boundaries; Date or Time for calendar and clock entries; Text length for a simple character-count limit; and Custom for a condition involving other cells or a more specific test.

Use a dropdown for categories that should be consistent across records.
Use a numeric boundary for quantities, scores, or percentages with clear minimums and maximums.
Use date or time limits when entries must fall within a defined period.
Use a custom formula when a cell depends on another value or must be unique.
Add an input message and an appropriate error alert so the rule is understandable.

Keep rules no more complicated than necessary. A short, well-maintained list is often more dependable than a custom formula that future editors cannot interpret. For shared workbooks, document unusual rules near the relevant input area or in a separate instructions sheet, and test typical, boundary, and invalid examples before distributing the file.

Frequently Asked Questions

How do I make a dropdown in Excel?
Select the cells, choose Data > Data Validation, set Allow to List, and enter the options in Source or point to a range of choices. Use a named range when the source list is on another worksheet.

Can Excel data validation prevent duplicates?
Yes. A Custom rule using COUNTIF can reject a value that already appears in the target range. Apply the formula to the whole range with a relative reference to its first cell, and test it with both a new value and a duplicate.

Why can invalid data still appear in a validated cell?
Validation may be bypassed by pasting, filling, importing, or editing through another application. It also does not fix entries that existed before the rule was added. Review and check the data after entry when accuracy matters.

Does data validation work on blank cells?
Rules often allow blanks, and the Ignore blank setting affects blank-value handling. Validation alone does not make a field required. Add a separate completeness check if users must enter a value.

How do I remove data validation without deleting cell values?
Select the validated cells, open Data > Data Validation, and choose Clear All in the dialog. This removes the validation settings; it does not serve as a command to erase the existing cell contents.

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.