Data validation in Excel controls what can be typed into a cell, so your data stays clean and consistent. The most popular use is a drop-down list, where the user picks from set options (like "Paid" or "Pending") instead of typing freely. You set it up from Data ▸ Data Validation. Validation stops wrong entries at the source, which saves hours of fixing messy data later.
Why use data validation
When many people type into the same sheet, you get "Delhi", "delhi", "New Delhi" and "DEL" for the same city. That breaks sorting, filtering and lookups. Data validation forces consistent entries, so everyone picks from the same list or stays within allowed values.
Create a drop-down list
- Select the cell(s) where you want the drop-down.
- Go to Data ▸ Data Validation.
- Under Allow, choose List.
- In Source, type the options separated by commas:
Paid,Pending,Cancelled(or select a range of cells that holds the options). - Click OK.
A small arrow now appears in the cell; click it to pick a value.
Use a range as the source
Instead of typing options, keep them in a column (say a "Lists" sheet) and point Source to that range: =Sheet2!$A$1:$A$5. Now you can edit the options in one place and every drop-down updates.
Other validation rules
Under Allow, you can also restrict entries to:
| Allow type | Use it for |
|---|---|
| Whole number | Quantities, ages (e.g. between 1 and 100) |
| Decimal | Prices within a range |
| Date | Only dates in a period |
| Text length | Codes of an exact length (e.g. 10-digit phone) |
| List | A drop-down of set options |
For example, to allow only whole numbers 1–100: Allow ▸ Whole number ▸ between ▸ 1 and 100.
Add an input message and error alert
- Input Message tab: shows a tip when the cell is selected ("Pick a status").
- Error Alert tab: shows a message and blocks wrong entries ("Please choose from the list").
This guides users and stops mistakes politely.
Worked example (an order tracker)
You track orders with a Status column. You add a drop-down (Paid,Pending,Cancelled) so everyone uses the same words. You also set the Quantity column to allow only whole numbers 1–1000. Now the data is clean enough to sort, filter and total without fixing typos.
Pro tips
- Keep your option list on a separate sheet and reference it, so it's easy to update.
- Turn on the Error Alert so wrong entries are actually blocked, not just discouraged.
- Copy a validated cell to apply the same rule to a whole column.
Common mistakes
- Typing options with stray spaces.
Paid, Pending(with a space) can create " Pending" as an option. Keep the source clean. - Not locking the source range. Use absolute references (
$A$1:$A$5) so the source doesn't shift. - Forgetting the error alert. Without it, users can still paste invalid data over the cell.
Key takeaways
- Data validation controls what can be typed into a cell.
- A List rule creates a drop-down of set options.
- You can also restrict to whole numbers, decimals, dates or text length.
- Add input messages and error alerts to guide users and block mistakes.
Practice task
Make a small order sheet with a Status column. Add a drop-down list with Paid, Pending and Cancelled. Set the Quantity column to allow only whole numbers between 1 and 500, with an error alert for anything else.
Build clean, professional spreadsheets employers trust in the ADCA program at HCI.
Frequently Asked Questions
What is data validation in Excel?
Data validation controls what can be typed into a cell, keeping data clean and consistent. It can create drop-down lists or restrict entries to numbers, dates or a text length.
How do I create a drop-down list in Excel?
Select the cell, go to Data ▸ Data Validation, choose List under Allow, and type your options in Source separated by commas (or select a range). Click OK.
How do I restrict a cell to numbers only?
In Data Validation, set Allow to Whole number or Decimal and choose a range, such as between 1 and 100. Entries outside the range are blocked.
Can I use a list of cells as the drop-down source?
Yes. Keep the options in a column and point the Source to that range, like `=Sheet2!$A$1:$A$5`. Editing the range updates every drop-down.
How do I stop wrong entries in Excel?
Use Data Validation with an Error Alert. It shows a message and blocks values that don't meet your rule, such as text in a numbers-only cell.