Data Validation Rules in Excel: Limit What Users Can Enter

Data validation rules in Excel limit what people can type into a cell: only whole numbers from 1 to 50, only dates this year, only items from a list, or only codes that are exactly six characters long. You set them once, and Excel rejects wrong entries with a message. This guide covers the built-in rule types, custom formula rules with AND, ISNUMBER and LEN, and the limits you should know before you rely on them.

How to add a data validation rule#

A data validation rule is set from the Data tab in four steps:

1. Select the cells to control, for example B2:B200.
2. Go to Data > Data Tools > Data Validation. On Windows, the keyboard path is Alt, A, V, V.
3. On the Settings tab, choose what to allow in the Allow box, then set the limits.
4. Click OK.

Now type 75 into a cell that only allows 1 to 50, and Excel shows an error and refuses the entry. The rule stays with the cell when you sort or fill it down.

The Ignore blank box is ticked by default. It means an empty cell is always allowed. Untick it only if blanks should count as errors, and remember that it doesn't force anyone to fill the cell in.

The built-in rule types#

The Allow box offers eight options. Each one has its own limits, such as between, greater than or equal to.

The List type creates a drop-down list. The guide to creating a drop-down list in Excel covers it step by step, and how to make a drop-down list that grows with your data shows how to keep it up to date. Limits can also point at cells. For example, a Date rule with a start date of =TODAY() blocks past dates.

How to write a custom validation rule#

A custom rule is a formula that returns TRUE when the entry is allowed and FALSE when it isn't. TRUE and FALSE are logical values, and comparisons like B2>=1 produce them. If you're new to this, practise TRUE and FALSE comparisons first.

Write the formula for the top-left cell of your selection. If you selected B2:B200, write it for B2. Excel moves the reference down for each cell, just as it does when you fill a formula down. Use $ signs for anything that must stay fixed, like a list range.

Here are custom rules that come up often:

AND returns TRUE only when every test inside it is TRUE. OR returns TRUE when at least one test is TRUE. The AND function guide explains both, and you can practise AND, OR and NOT in a live spreadsheet. LEN counts characters, including spaces; see the LEN function guide.

Why use ISNUMBER in a custom rule?#

ISNUMBER makes sure the entry is a real number, not text. Without it, a rule such as =B2>=1 can accept text: in Excel, any text is treated as greater than any number in a comparison, so "ten" passes B2>=1. Adding ISNUMBER closes that gap:

=AND(ISNUMBER(B2),B2>=1,B2<=50)

The ISNUMBER function guide and the article on ISBLANK, ISNUMBER and ISTEXT cover the other IS functions. To use them confidently, practise the IS functions.

Test the rule in a cell first#

A custom rule gives no feedback while you write it, so test it in a normal cell first. Type the same formula next to your data and fill it down. Every row shows TRUE or FALSE, and you can see which entries would pass.

The test formula in D2 is =AND(LEN(A2)=6,ISNUMBER(B2),B2>=1,B2<=50). Row 3 fails because the ID has 7 characters. Row 4 fails because 75 is over 50. Row 5 fails because "ten" is text. Once the results look right, copy the formula into the Custom box and delete the test column.

To count how many rows break the rule in one cell, use SUMPRODUCT: =SUMPRODUCT((LEN(A2:A5)<>6)*1) counts IDs that are not 6 characters long (here, 1).

Input messages and error alerts#

An input message tells people the rule before they type, and an error alert tells them what went wrong. Both are tabs in the Data Validation box.

• Input Message: a short note that appears when the cell is selected, such as "Enter a quantity from 1 to 50."
• Error Alert: the message shown when the rule is broken. Choose one of three styles.

Use Stop for rules that must hold, like IDs that feed a lookup. Use Warning for soft limits, like an unusually large order. Write the message in plain words: say what is allowed, not just "Invalid entry".

What data validation can't do#

Data validation is a guide for people typing, not a lock. Know its limits:

• Pasting replaces the rule. If someone copies a cell and pastes it over a validated cell, the pasted cell's rules (or none) replace yours. Excel doesn't check the pasted value.
• Old data isn't checked. Adding a rule doesn't flag values already in the cells. Use Data > Data Validation (arrow) > Circle Invalid Data to circle them in red.
• Formulas aren't checked. Validation only applies to values typed into the cell, not to results calculated by a formula.
• Lists can point to other sheets. You can use a range on another sheet as the source, as the guide to a drop-down list from another sheet shows.

For real control, combine validation with sheet protection. The guide to protecting a sheet and locking cells shows how to leave only the input cells editable.

Practise it#

Every custom rule is a TRUE or FALSE formula, so the formula skill is what you need to practise. Practise AND, OR and NOT in a live spreadsheet, then practise the IS functions: you type each test and ExcelReady checks it instantly. Start with the row-3 test in the exercise on this page.