Cleaning data takes time. Stopping dirty data from getting in takes much less. Excel’s data validation lets you define what can be typed into each cell: only numbers within certain limits, only dates in a given period, only values from a list or text of a specific length. If someone tries to enter something else, Excel warns them or simply stops them. It is the best way to keep shared templates, forms and daily logs clean. In this guide you will see how to use it.
Where to find data validation
Select the cells you want to control and go to Data > Data Validation. The window has three tabs:
- Settings: the allowed data type and its conditions.
- Input Message: a help text shown when the cell is selected.
- Error Alert: what happens when someone enters invalid data.
Drop-down lists
This is the most popular use. It is meant for columns with a closed set of values: departments, order statuses, countries, categories.
- Type the list of allowed values on a helper sheet, for example in
Lists!A2:A8. - Select the target cells.
- Under Allow, choose List.
- Under Source, type
=Lists!$A$2:$A$8.
Each cell now shows an arrow with the options. No more “Pending”, “pending” and “Pend.” in the same column.
Tip: turn the helper list into a table (Ctrl+T) and use a defined name as the source. That way, when you add values to the table, the drop-down updates automatically.
Numbers within a range
For quantities, prices or percentages:
- Allow: Whole number or Decimal.
- Data: between.
- Minimum and maximum: for example, between 0 and 100 for a discount percentage, or greater than 0 for a price.
Valid dates
To prevent impossible dates or dates outside the period:
- Allow: Date.
- Data: between.
- Start date:
=DATE(2026,1,1). - End date:
=TODAY(), to block future dates.
This rule also forces people to type real dates rather than text that looks like a date, which prevents many later problems with filters and PivotTables.
Text length
Useful for codes with a fixed number of characters, such as a five-digit zip code or a tax ID:
- Allow: Text length.
- Data: equal to 5.
Combine it with text formatting in the cell to keep leading zeros.
Custom rules with formulas
The Custom option allows any condition you can express with a formula that returns TRUE or FALSE:
- Prevent duplicates in column A:
=COUNTIF($A:$A,A2)=1. - Require an @ in an email:
=ISNUMBER(SEARCH("@",B2)). - No spaces:
=B2=SUBSTITUTE(B2," ",""). - Uppercase only:
=EXACT(C2,UPPER(C2)).
Input messages and error alerts
A good input message explains what is expected: “Enter the price before tax, in dollars, with two decimals”. It saves questions and emails.
For the error alert you can choose the style:
- Stop: prevents the entry. Use it for strict rules.
- Warning: warns but lets the user continue after confirming.
- Information: just informs.
Write clear messages: instead of “Invalid value”, say “The discount must be between 0% and 50%”.
Limitations to be aware of
- Pasting data bypasses validation. If someone copies and pastes values from another sheet, Excel does not check the rules. You can check with Data > Data Validation > Circle Invalid Data.
- It does not apply to existing data. Rules only control what is typed from now on. Use the option above to find older values that break the rules.
- It is not a security measure. Anyone with editing rights can remove validation. To prevent that, protect the sheet.
Validation + sheet protection
A robust template combines both:
- Unlock the cells where users should type.
- Apply data validation to those cells.
- Protect the sheet so formulas, headers and rules cannot be changed.
The result is a form where only correct data can be entered in the right places.
Frequently asked questions
How do I copy validation to other cells?
Copy a cell with the validation, select the target and use Paste Special > Validation.
What do I do with data that was already wrong?
Before applying validation, clean the column: trim spaces, standardize values and remove duplicates with our online duplicate remover. Then use “Circle Invalid Data” to review whatever is left.
Does data validation work in Google Sheets and LibreOffice?
Yes. Both programs have data validation with drop-down lists, number ranges and custom rules. If you share an Excel template, the basic rules are kept when it is opened in them.
Can I create dependent drop-down lists?
Yes. For example, the “City” drop-down can show only the cities of the country chosen in the previous column. You do it with defined names and the INDIRECT function as the list source.
How do I remove validation from a range?
Select the cells, open Data > Data Validation and click Clear All. Values that were already entered do not change.
Conclusion
Data validation is the cheapest way to keep your sheets clean: drop-down lists for closed values, limits for numbers and dates, length rules for codes and formulas for custom rules. Combine it with clear messages and sheet protection, and your templates will receive correct data from day one. If you need to clean what already exists, our free tools help you do it in minutes.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
