Most bad reports start with bad data entry: a date typed as text, a percentage above 100, a misspelt status. Data validation blocks those entries at the source, before they reach your formulas.
Where to find it
Select the cells, then Data, then Data Validation. The Settings tab has the rule, Input Message shows a hint, and Error Alert controls what happens when someone breaks the rule.
Allow only whole numbers in a range
Choose Whole number, between 1 and 100. Anyone typing 150 or 12.5 gets an error. Good for scores, ages and quantities.
Allow only dates in a window
Choose Date, between a start and end date. For example only dates from this year can be entered in a booking sheet.
A custom rule with a formula
Choose Custom and write a formula that must be TRUE. To block duplicate IDs in A2:A500, select the range and use:
=COUNTIF($A$2:$A$500, A2)=1
Add an input message
On the Input Message tab, type a short hint such as "Enter date as DD-MM-YYYY". The hint appears when the cell is selected.
Error alert styles
- Stop: the entry is refused.
- Warning: the user can choose to keep it.
- Information: just a notice.
Check data that already exists
Validation does not check old entries. Use Data Validation, then Circle Invalid Data, to circle cells that break the rule.
Limits to know
Pasting values over a validated cell removes the rule for that cell. Protect the sheet if the file goes to many people.