Data Validation in Spreadsheets
Part of the Data Analyst Roadmap
In short
When ten people type into one shared sheet, you'll get 'Bangalore', 'Bengaluru', 'blr' and 'B'lore'. Data validation stops the mess at the door instead of cleaning it later.
Restricting what can be entered into a cell (dropdown lists, number ranges, custom rules) to prevent bad data from entering a shared spreadsheet in the first place.
Think of it like this
The metro token gate
A metro gate only opens for a valid token. A coin, a bus ticket or a button won't work, no matter how hard you push. Data validation is that gate on a cell: only the values you allowed get in, and everything else bounces with a message.
Watch it happen
A cell with a dropdown rule
types "blr"
1/4Someone tries to type a short form into the City column.
The big ideas
What you need to know
Dropdown lists
Restrict a column to fixed choices like Paid, Pending, Refunded. The most useful rule by far.
Number and date ranges
Allow only whole numbers 1 to 100, or dates after the project start.
Custom formulas
A rule like =COUNTIF($A:$A, A2)=1 blocks duplicate IDs as they are typed.
Helpful error messages
Write what's allowed: 'Pick a city from the list'. Not just 'Invalid'.
Save yourself the pain
Common mistakes
Oops
Typing the dropdown options straight into the rule.
Do this instead
Keep the list on a separate Lists sheet and point the rule at that range, so updating it is easy.
Oops
Assuming validation also checks old data already in the cells.
Do this instead
Rules check new entries. Use 'Circle Invalid Data' in Excel to find existing bad values.
Your mission
Bulletproof an expense sheet
Quick check
What's the main benefit of a dropdown rule on a shared sheet?
Go deeper
- 1Add a dropdown list data validation rule to a sample input sheet.
- 2Set up a custom validation rule (e.g. only allow numbers within a range).
- 3Learn why this matters most for spreadsheets multiple people manually update.
More topics at this stop
Excel Fundamentals
Formulas & Functions (VLOOKUP, INDEX-MATCH, XLOOKUP)
PivotTables & PivotCharts
Conditional Formatting
Data Cleaning in Excel
Excel Charts & Dashboards
Power Query
Google Sheets Essentials
Excel Shortcuts & Productivity
Stuck on this topic? Ask an Insider
Get 1:1 guidance from people who've walked this exact path — on the InsideEdge app.
