How to Set Data Validation in Excel for Reliable Entries

How-to basics · Updated November 18, 2026

To set data validation in Excel, select the cells you want to control, then choose Data > Data Validation > Data Validation. Pick a rule, such as a drop-down list, a number range, or a date limit, and configure the settings and error message. Data validation helps keep entries consistent, reduces avoidable input errors, and makes a workbook easier for other people to use. Before you start, decide what entries are acceptable and whether users should see a drop-down, a prompt, or a blocking warning.

What You’ll Need

  • A workbook in Microsoft Excel for Microsoft 365 on Windows. The main steps are similar in recent desktop versions of Excel.
  • The target cells or range where people will enter data.
  • If you are making a drop-down, the approved choices, ideally stored in a dedicated range on a worksheet.
  • If you are using a custom rule, a clear example of what should pass or fail. You do not need to write a formula for the standard whole number, date, or list rules.

Step-by-Step

  1. Select the cells to control. Click a cell or drag across a range before opening the validation settings. For example, select B2:B100 to control entries in the next 99 rows while leaving a header in B1. The rule will apply to the selected cells; selecting an entire column is possible, but can make a workbook harder to manage and may affect rows where you did not intend to collect data. If the range is already an Excel table, you can select its data cells and apply the rule there.
  2. Open the Data Validation dialog. On the Windows ribbon, choose Data > Data Validation > Data Validation. In the dialog, open the Settings tab if it is not already selected. The Allow menu contains the rule types Excel supports, including Whole number, Decimal, List, Date, Time, Text length, and Custom. Use the rule that matches the information you are collecting instead of relying only on an explanatory note.
  3. Choose a rule type and its limits. For a numeric or date rule, select a condition such as between, greater than, or less than or equal to, then enter the permitted value or values. For example, to limit a rating to whole numbers from 1 through 5, choose Whole number, set Data to between, and enter 1 and 5. Choose Decimal if values such as 2.5 should be allowed. For dates, enter actual date values or cell references containing dates; Excel stores dates as serial numbers, so cells formatted as dates are easier to inspect.
  4. Create a drop-down list when users should choose from approved options. Set Allow to List. In Source, either type short choices separated by commas, such as Low,Medium,High, or select a worksheet range that contains the choices. Keep the In-cell dropdown option enabled if you want an arrow to appear when a user selects the cell. A list range is easier to maintain when choices change. If the source list is on another worksheet, define a named range for those cells using Formulas > Define Name, then enter the name in Source with an equals sign before it, for example =StatusChoices. Excel’s validation source box does not accept every kind of worksheet reference directly, so a named range is a dependable way to refer to a list stored elsewhere.
  5. Use a custom formula only when the built-in rules are not enough. Choose Custom in Allow and enter a formula that returns TRUE for permitted entries. The formula is written for the top-left cell of the selected range, and Excel adjusts relative references for the other cells. With the target range A2:A100 and a requirement that each entry be a whole number from 1 to 100, use =AND(A2>=1,A2<=100,MOD(A2,1)=0). A value of 75 passes; 101 and 2.5 do not. The MOD condition matters because the first two comparisons alone would allow decimals. Test a custom rule with both an acceptable value and an unacceptable one before sharing the workbook.
  6. Add an input message if a short instruction will help. Open the Input Message tab and check Show input message when cell is selected. Enter a brief title and a useful explanation, such as “Enter a date” and “Use a date from this quarter.” The message appears when a user selects a cell with validation. It is guidance, not a restriction, so the rule on the Settings tab still needs to be configured correctly.
  7. Choose what happens when an entry is invalid. Open the Error Alert tab and check Show error alert after invalid data is entered. Choose Stop to block the entry, Warning to let the user confirm whether to keep it, or Information to show a message that can be dismissed. Stop is the strongest choice for values that must follow the rule. Write a specific message that tells the user what to enter instead of only saying the value is invalid. If you do not want an alert, clear the checkbox, but remember that invalid entries can then be accepted.
  8. Apply the rule and test it. Click OK, then try entering one valid value and one invalid value in the selected cells. For a list, check that the drop-down appears and that the displayed options are correct. For a number limit, try a value below and above the allowed range. If Excel displays an alert, confirm that its wording is helpful and that the chosen alert style behaves as intended. Save the workbook after testing.

Common Mistakes

A frequent problem is selecting the wrong range before opening Data Validation. Check the selected cells and the Applies to range shown in the dialog before clicking OK. If you apply a rule only to B2, it will not automatically cover every future row in B3:B100. If you expect to add rows, apply the rule to an appropriately sized range or check how it behaves in your Excel table.

Another mistake is assuming validation repairs existing data. It generally controls future entries; it does not automatically clean values that were already in the cells. It can also be bypassed by pasting or filling data in some situations. Treat it as an input safeguard, not a complete data-cleaning or security system. Check existing data separately, and do not rely on validation to protect confidential information or enforce permissions.

For list rules, people often type options into the Source box when a maintained range would be more practical. A long or frequently changing list is harder to update in a typed source. Also check that the In-cell dropdown option is enabled; without it, the rule may still restrict entries but users will not see the selection arrow. Make sure the source cells do not contain unintended blank lines or spelling variations.

Finally, setting Ignore blank does not make a cell mandatory. It allows a blank value to pass the validation rule. If every record must have a value, use a separate completeness check or a suitable process before the workbook is submitted. Do not confuse a helpful input message with a rule: users can still enter a value the message advises against unless the validation settings reject it.

Tips

Keep source choices in one clearly labeled place, and use a named range when the list lives on another worksheet. This makes it easier to update options without reopening the validation dialog. If choices change regularly, check the named range after adding items so it includes the new entries. Avoid putting extra notes or blank cells inside the list range unless you want them to appear as selectable choices.

Choose a rule that matches the kind of data. Whole number is preferable to Decimal when fractions are not acceptable; Date is clearer than a general number limit when people are entering dates. For a list of values, use a drop-down instead of asking everyone to type the same category differently. Consistent entries improve sorting, filtering, and later analysis.

Make error alerts practical. A message such as “Enter a whole number from 1 to 5” explains both the expected format and the allowed range. Use Stop when the limit is mandatory, and Warning only when there are legitimate exceptions. Keep instructions concise so they are easy to read while entering data. If a workbook has multiple rules, test each type of cell rather than assuming one successful test confirms all of them.

For a custom rule that should prevent duplicate nonblank entries, apply the rule to A2:A100. With that target range, enter =OR(A2="",COUNTIF($A$2:$A$100,A2)=1) as the custom validation formula. It allows a blank cell or a value that appears only once in the range, so a second copy of an existing entry is rejected when typed normally. The dollar signs keep the checked range fixed while the A2 reference adjusts for each row. Test the rule with a repeated value and an unused value, and remember that pasted data may still need a separate duplicate check.

Troubleshooting

The drop-down arrow is missing. Select a cell with the rule, open Data > Data Validation > Data Validation, and check that Allow is set to List and In-cell dropdown is selected. Verify that the Source points to the intended cells or named range. If the source cells are empty, contain unexpected blanks, or the named range excludes new items, correct the source and test again.

Excel accepts a value that should be rejected. Reopen the settings and confirm that the rule applies to the cell you tested. Check the Error Alert tab: if Show error alert is off, Excel may allow invalid entries. A Warning or Information alert also does not block an entry in the same way as Stop. Remember that data pasted from elsewhere can evade validation, so inspect questionable data rather than assuming the rule caught everything.

A valid value is rejected. Check the actual stored value and its type. A number stored as text may not behave like a number, and a date that looks correct may be text rather than a real Excel date. Confirm that the limits are in the intended order and that the selected condition matches the requirement. For custom formulas, check that the reference points to the top-left cell of the validated range and that the formula returns TRUE for a value that should pass.

Some cells have the rule and others do not. Select a cell that works and one that does not, then inspect each Data Validation dialog. Reapply the correct rule to the full intended range. When copying cells, be aware that the copied cell’s validation can replace the destination’s validation. If you only want to copy a value, use Paste Special > Values where available, then confirm the destination rule remains in place.

You need to find entries that were already invalid. Select the relevant range and use Data > Data Validation > Circle Invalid Data. Excel marks values that do not meet the current rule; correct them and use Clear Validation Circles to remove the circles afterward. This helps identify problems but does not decide how to fix them. Review each marked value against the real requirements before changing it.

On Excel for Mac, use the Data tab and its Data Validation command; dialog layout and some ribbon labels can vary slightly by release. In Excel for the web, validation is available from the Data tab, but advanced dialog options and management workflows may differ from desktop Excel. If a setting is missing or a rule behaves differently, open the workbook in Excel for Microsoft 365 on Windows or Mac and check the rule there.

Frequently Asked Questions

Can I make a drop-down list from another worksheet?

Yes. Put the choices on the other worksheet, define a named range for them using Formulas > Define Name, and use that name as the list Source with an equals sign, such as =StatusChoices. Then test the drop-down from the sheet where people enter data.

Does data validation stop people from pasting invalid values?

Not reliably. Validation is useful for ordinary data entry, but pasting, filling, or importing can bypass it in some cases. Use checks such as Circle Invalid Data or a separate review step when you need to verify a completed range.

Can I require a cell to be filled in?

Ignore blank controls whether blank entries are allowed by the validation rule, but it is not a complete required-field workflow. For a dependable completeness check, review the finished records or add a separate check that flags empty cells before the data is accepted.

How do I edit or remove a validation rule?

Select the affected cells and open Data > Data Validation > Data Validation to change the rule. To remove it, select the cells, open the same dialog, and choose Clear All, then click OK. Check that you selected only the cells whose rules you intend to change.

Can I apply more than one validation rule to a cell?

A cell has one data validation rule at a time. If requirements combine conditions, choose Custom and write a formula that tests them together, or redesign the entry process so each field has a clear, maintainable rule. Test the result using both passing and failing examples.

Will the rule automatically apply to new rows in a table?

It may extend when you add rows to an Excel table, but do not assume every workbook setup will behave identically. Add a test row and inspect its Data Validation settings. If the rule is missing, apply it to the new table cells or a larger planned input range.

Related

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.