What data validation does and when you need it
Data validation is a feature in Excel that stops you from typing the wrong kind of information into a cell. You set a rule — like "only numbers between 1 and 100" or "only dates after today" — and Excel rejects anything that doesn't match. If someone tries to enter text where you need a number, or a date that's outside your range, Excel shows an error message and won't let the entry through.
You need this when other people will use your spreadsheet, or when you're building something you'll use for months and want to catch mistakes early. A budget tracker that only accepts positive numbers, a sign-up sheet that rejects duplicate names, a project timeline that won't let you enter a important date before the start date — these are all jobs for validation. Without it, bad data slips in silently and breaks your formulas or reports later.
Key Takeaways
- Data validation rules live in the Data menu under Validation (or Data Tools > Validity on Mac), and you set them after selecting the cells where the rule applies.
- The most common rules are whole numbers in a range, text length limits, dates within a window, and lists of allowed values that appear as a dropdown menu.
- You can write a custom formula rule if the built-in options don't cover what you need, like "reject any entry that matches a value in column B."
- Error messages are optional but worth adding — a clear message like "Enter a number between 1 and 10" stops confusion better than Excel's default.
- Validation only stops new entries; it does not change data already in the cells, so explore rules before you fill the sheet or clean up old data first.
Selecting cells and opening the validation dialog
Start by clicking the cell or range where you want the rule to explore. If you need the same rule across multiple separate areas, hold Ctrl (or Cmd on Mac) and click each one. The cells don't have to be next to each other — validation works on any selection you make.
Once you've selected your cells, go to the Data menu at the top. In Excel on Windows, click Data, then look for Validation (sometimes labeled Data Validation). On Mac, it's under Data > Validity. A dialog box will open with tabs across the top: Allow, Data, Formula, Input Message, and Error Alert. The Allow tab is where you pick what kind of rule you want.
Setting up the most common validation rules
In the Allow dropdown, you'll see options like Whole Number, Decimal, Date, Time, Text Length, List, and Custom. Pick the one that matches what you're validating.
For whole numbers in a range: Select Allow > Whole Number. Then pick "between" from the Data dropdown, and enter your minimum and maximum. If you want only positive numbers, set minimum to 1 and maximum to 999999. If you want to reject zero, set minimum to 1. If you want to allow negative numbers, set minimum to a negative value.
For a dropdown list of allowed values: Select Allow > List. In the Source field, type your options separated by commas (like "Yes,No,Maybe") or point to a range of cells that contains them. When you click OK, those cells will show a small dropdown arrow, and users can only pick from your list. This is the easiest way to prevent typos in categories.
For dates: Select Allow > Date. Pick "between" and enter a start date and end date. This is useful for project timelines where you want to prevent someone from entering a important date before the project starts, or for sign-up sheets where you only want entries from a specific week.
For text length: Select Allow > Text Length. Pick "between" and set minimum and maximum character counts. This stops someone from pasting a paragraph into a field meant for a short code.
Writing a custom formula when built-in rules don't fit
If none of the standard options cover what you need, use a custom formula. Select Allow > Custom, and in the Formula field, write an expression that returns TRUE for entries you want to accept and FALSE for entries you want to reject.
For example, if you want to reject any entry that already appears in column B, use =COUNTIF($B:$B,A1)=0. This counts how many times the new entry appears in column B and rejects it if the count is not zero. If you want to reject entries that are less than the value in cell C1, use =A1>=C$1. The formula sees the cell being validated as the first cell in your selection, so adjust column and row references accordingly.
Custom formulas take practice, but they're powerful. Test your formula on a few cells first before explore it to a large range, because a broken formula will reject everything.
Adding error messages so users know what went wrong
By default, Excel shows a generic error when someone breaks a validation rule. You can replace it with a message that actually explains what's wrong. Click the Error Alert tab in the validation dialog.
The Style dropdown offers three choices: Stop (blocks the entry entirely), Warning (shows a message but lets the user override it), and Information (just notifies the user). For most cases, use Stop. In the Title field, write a short heading like "Invalid Entry". In the Error Message field, write what went wrong and what to do instead — for example, "Enter a number between 1 and 10" or "This name is already on the list."
A clear error message cuts down on confusion and questions. Without one, users see "This value doesn't match the data validation rules" and have no idea what they did wrong.
Checking and updating validation rules after you've set them
Once you've applied validation, test it by trying to enter invalid data into one of the protected cells. The error message should appear. If it doesn't, or if the message is wrong, go back to the cell, open the Data menu, and select Validation again to edit the rule.
If you need to remove validation from a range, select the cells, open Data > Validation, and click the Clear All button. This removes the rule but leaves the data in the cells untouched. If you want to explore the same rule to additional cells later, select those cells and repeat the process — you don't have to start from scratch.
Validation rules don't prevent someone from copying and pasting data into your sheet, and they don't catch mistakes in formulas. They only check new entries typed directly into cells. If you're sharing the sheet with others, mention in a note or comment that certain columns have validation rules so they know what to expect.
Frequently Asked Questions
Can I make a dropdown list that pulls from another sheet?
Yes. In the Allow > List field, instead of typing values, reference the range on the other sheet. Use the format SheetName!A1:A10 (on Windows) or SheetName.A1:A10 (on Mac). The dropdown will show whatever values are in that range, and if you update the range later, the dropdown updates automatically.
What happens if I copy a cell with validation to a new location?
The validation rule copies along with the cell. If you copy a cell with a dropdown list to a new row, the new cell gets the same dropdown. If the rule uses cell references (like "must be less than C1"), those references adjust relative to the new location, just like formulas do.
Can validation stop someone from deleting data from a cell?
No. Validation only checks what gets typed in. To prevent deletion, you need to protect the sheet, which is a separate feature under Review > Protect Sheet. You can combine both — protect the sheet and add validation — for stronger control.
Why does my validation rule reject entries that should be allowed?
Check that your Allow setting matches your data type. If you set Allow > Whole Number but someone tries to enter 5.5, it will be rejected. Also check the Data dropdown — if you picked "equal to" instead of "between," the rule will only accept one specific value. Test with a straightforward entry first to narrow down the problem.
Can I use validation to flag entries that are unusual but not wrong?
Yes, by setting the Error Alert style to Warning instead of Stop. This shows a message but lets the user click OK and enter the data anyway. Use this when you want to catch potential mistakes without blocking legitimate entries.