Data Validation in Excel: Steps for Reliable Entries

To set up data validation in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, define the permitted entries on the Settings tab, and configure an input message or error alert if useful. Then test the rule with both acceptable and unacceptable values; a validation rule guides data entry, but it does not prove that the data is true or prevent every way of changing a cell.

What data validation does

Data validation is a set of rules that limits or guides what people can enter in selected worksheet cells. Depending on the rule, Excel can accept only whole numbers within a range, dates within a period, text of a certain length, or values selected from a dropdown list. A custom rule can use a formula to check a condition that the built-in choices do not cover.

These rules support data quality by reducing invalid formats, inconsistent labels, and out-of-range values. For example, a list of department names can prevent entries such as “Customer Service,” “Customer service,” and “Cust. Service” from being treated as three categories. Validation is not the same as verification: a rule can confirm that an entry looks like a date, but cannot establish that the date is historically correct.

Data validation steps in Excel

Prepare the cells and any source values. Decide which cells need a rule, and check whether they already contain data. If you need a dropdown, put its permitted choices in a clear, maintained range. Remove merged cells or unusual layouts that could make selecting the intended range difficult.
Select the target cells. For a rule covering a column of future entries, select a suitable range, such as D2:D500, rather than only the first cell. Leave a header row out of the selection. You can also select nonadjacent cells, but consistent ranges are usually easier to maintain.
Open the validation settings. Choose Data > Data Validation on the ribbon. On the Settings tab, use the Allow menu to choose a rule type, then set its conditions. The available choices include Whole number, Decimal, List, Date, Time, Text length, and Custom.
Set the rule’s details. For a number or date, specify the comparison, such as between, greater than, or less than, and enter the limit values. For a list, supply the choices or a source range. For a custom rule, enter a formula that returns TRUE for an acceptable entry and FALSE for one that should be rejected.
Choose how the rule responds. On the Input Message tab, you can show a short instruction when a user selects a cell. On the Error Alert tab, enable the alert and choose Stop, Warning, or Information. Stop rejects an invalid typed entry; Warning and Information allow the user to proceed after a prompt.
Test and apply. Confirm the settings, try a valid value, and then try a value that should fail. Check the dropdown, messages, boundary values, and blank-cell behavior. If the rule is for a large range, test a cell near the bottom as well as the first one.

In Excel for Mac, the Data Validation command is also available from the Data tab, although ribbon layout and keyboard shortcuts can differ. Excel for the web has its own ribbon layout and may not expose every desktop workflow in the same way; when a setting is unavailable, open the workbook in the Microsoft 365 desktop app.

Choose the right validation type

Choose the narrowest rule that matches the real business requirement. A rule that is too strict can block legitimate entries; one that is too broad may give a false sense of control. These examples show common uses:

Whole number or Decimal: Restrict a quantity to nonnegative integers, or allow a measured value with decimal places.
Date or Time: Limit booking dates to a defined period or require a time within working hours.
List: Standardize a status, region, product type, or other category with a dropdown.
Text length: Limit an identifier to a particular number of characters. This checks length, not whether the characters follow a more complex pattern.
Custom: Combine conditions, check another cell, or test a rule such as uniqueness.

Consider whether blanks should be allowed. The Ignore blank option is available for many validation settings, but its interaction with a custom formula depends on that formula. If a field is mandatory, make the rule reject blanks and also explain the requirement to the user. A blank that is allowed by validation is not the same as a completed field.

Create a dropdown list

Dropdowns are useful when people should choose from a known set rather than invent their own labels. For a short, stable list, select the cells, open Data > Data Validation, choose List under Allow, and enter the items in Source separated by the list separator shown by Excel. Turn on In-cell dropdown so the selection arrow appears. A range is usually better for a longer list because it is easier to review and update.

For example, place the status values Open, In progress, and Closed in H2:H4. Select the entry cells, choose List, and set Source to =$H$2:$H$4. Enable the in-cell dropdown, then check that each option appears and that an unlisted value triggers the intended alert. When a list is stored on another worksheet, define a named range for those cells and use the name as the list source; this avoids relying on a direct cross-sheet reference in the validation box.

Keep list choices unambiguous and consistent with the labels used in reports. If the source list changes, verify that the validation source still includes the new items. A dropdown does not automatically make the source list complete or current, and a copied cell may carry a different validation rule than expected.

Use a custom formula when needed

Custom validation is helpful for conditions that depend on more than one test. The formula is written relative to the top-left cell in the selected range, so selecting and referencing the correct starting cell matters. Excel evaluates the condition for each cell in the selection, adjusting relative references as it goes.

For example, suppose B2:B100 contains quantities that must be whole numbers from 0 through 100. Select B2:B100, choose Custom, and use this formula:

With the quantity cells in B2:B100 and B2 as the active, top-left cell, use =AND(B2>=0,B2<=100,MOD(B2,1)=0). A value of 37 passes; 101 fails the upper limit, and 12.5 fails the whole-number test. If blanks should be permitted, enable Ignore blank as appropriate; if each cell is required, add a nonblank condition such as B2<>”” to the formula, using straight quotation marks in Excel.

For a unique-entry check, select A2:A100 and make sure A2 is the active cell. A custom rule can use =COUNTIF($A$2:$A$100,A2)=1. The fixed range checks the full entry area while the current-cell reference adjusts down the selection. Test the rule carefully, especially if blank entries are allowed, since blanks may need separate handling. Custom formulas should be simple enough that another workbook maintainer can understand and audit them.

Test existing data and common failure points

Adding a validation rule does not automatically clean or repair values already present in the cells. Nor should you assume that every paste operation will be blocked: copying and pasting can bring in values, and can also replace a destination cell’s validation settings. If data is imported or pasted regularly, check the resulting values and rules after the operation.

To find existing entries that do not meet the current rule, open the Data Validation menu on the Data tab and choose Circle Invalid Data. Review the marked cells, correct them, or decide whether the rule needs adjustment. The circles identify entries considered invalid under the active rules; they do not explain the business reason an entry is wrong. The command to clear the circles removes the visual markers, not the underlying values.

A valid-looking value is rejected: Check the comparison limits, the selected range, and whether a number or date is stored as text.
The rule works in one row but not another: Review relative and absolute references in a custom formula and verify the active cell when the rule was created.
The dropdown is missing choices: Inspect the source range or named range, including whether it covers newly added items.
Users bypass the warning: Check whether the alert is set to Warning or Information instead of Stop, and remember that pasted or imported data needs separate checks.

Maintain validation as a workbook changes

Validation is most dependable when it is treated as part of the workbook’s design rather than a one-time formatting task. Document what each rule is meant to protect, who owns its source list, and whether blanks are allowed. After inserting rows, extending a table, copying cells, importing data, or changing a list, confirm that the intended range still has the correct rule.

Use clear input messages that tell people both what to enter and, where useful, an example. Error messages should explain the acceptable range or choices in plain language. For a shared workbook, protect formulas and structural cells as appropriate, but do not treat worksheet protection as a substitute for validation or review. If incorrect entries could have serious consequences, use additional checks such as reconciliation, review, or controlled data-import procedures.

Frequently Asked Questions

How do I add a dropdown list in Excel?
Select the destination cells, choose Data > Data Validation, select List under Allow, provide the list items or source range, and turn on In-cell dropdown. Test that the choices and error behavior are correct.

Does data validation stop people from pasting invalid data?
Not reliably. Validation can reject invalid values typed into a cell when a Stop alert is active, but pasting or importing can bypass the entry prompt or replace the rule. Review data and validation settings after bulk changes.

Can I use a formula to validate an entry?
Yes. Choose Custom in the Allow menu and enter a formula that evaluates to TRUE for valid entries. Use references based on the top-left cell in the selected range, and test multiple rows to confirm that references adjust correctly.

How can I find invalid values already in a worksheet?
Use Data > Data Validation > Circle Invalid Data in the desktop ribbon to mark entries that fail the applicable rules. Inspect and correct those cells; applying a rule alone does not fix old values.

How do I change or remove a validation rule?
Select the affected cells and open Data > Data Validation. Edit the settings to change the rule, or use Clear All in the dialog to remove validation from the selected cells. Check a few cells afterward, particularly if the selection includes multiple types of rules.

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.