Lesson 8 of 16 · Level 2: Intermediate
Data Validation, Drop-Down Lists and Sheet Protection in Excel
- ⏱ 11 min read
- ❓ 4 review questions
- ✍️ Exercise with solution
- 📥 Downloadable template
In this lesson
- Create drop-down lists, including dependent lists (state → city).
- Block bad entries: out-of-range dates, duplicates, text in number columns.
- Protect your sheets so other people can only type where you allow.
- Pick the right error alert style and find invalid data that's already there.
On a copy of the Sales sheet, add a drop-down list to the Region column (West, Central, East) and a rule that only accepts whole numbers greater than 0 in Units.
- practice-excel.xlsx · Sales sheet (make a copy first)
The formulas from the last few lessons only work if the data is typed correctly. All it takes is someone typing "West " with a trailing space, "west" in lowercase or an impossible date, and a SUMIFS or VLOOKUP quietly returns the wrong answer. Data validation stops the problem at the source: Excel won't accept entries that don't belong.
- Choose the rule type
- Build drop-down lists
- Formula-based rules
- Helpful messages
- Protect the sheet
Where it lives and what it can do
Select the cells and go to Data → Data Validation. On the Settings tab, the Allow box offers:
| Option | Use it for | Example |
|---|---|---|
| Whole number | Quantities, ages | Between 1 and 500 units |
| Decimal | Prices, percentages | Discount between 0 and 0.3 |
| List | A fixed set of values | Region: West, Central, East |
| Date | Dates within a range | Between 1/1/2026 and today |
| Time | Hours and shifts | Between 8:00 AM and 6:00 PM |
| Text length | Fixed-length codes | A 5-digit ZIP code |
| Custom | Any rule you can write as a formula | No duplicate invoice numbers |
Drop-down lists
This is the most common use and the one that prevents the most errors.
A typed list
Under Allow choose List and in Source type the values separated by commas: Pending,In Progress,Delivered. Good for short lists that rarely change.
A list from a range (the professional way)
- On a helper sheet (say, Lists), type the values in a column and convert it to an Excel table (lesson 4) named
tblRegions. - Select the table's column and give it a name in the Name Box, for example
Regions. - In the validation dialog, choose List and type the source:
=Regions.
The payoff: when you add a new region to the table, it appears in every drop-down automatically.
If you have a column with repeated customer names, type =SORT(UNIQUE(Sales[Customer])) in a helper cell. If that formula is in J2, use =$J$2# as the validation source. The # means "the whole result of the formula," even as it grows. You'll study it in depth in lesson 10.
Dependent lists: state → city
You want column B to offer only Texas cities once someone picks "Texas" in column A.
- Make one column per state with its cities, and name each range exactly like the state:
Texas,California,Florida. Names can't contain spaces, so New York becomesNew_York. - In column A, create a list of the states.
- In column B, create a list with the source
=INDIRECT(SUBSTITUTE(A2," ","_")).
INDIRECT turns the text in A2 into a reference: if A2 says "Texas," B2's list uses the range named Texas. SUBSTITUTE swaps the space in "New York" for an underscore so it matches New_York.
1. What's the main advantage of using a named table as the source for a drop-down list?
Tables grow on their own, and the name always points to the whole column.
Custom rules with formulas
Under Allow → Custom you write a formula that must return TRUE for the entry to be accepted. Write it for the first cell of the selected range (relative references adjust on their own, just like in conditional formatting).
| Rule | Formula (range starting at A2) |
|---|---|
| No duplicates in column A | =COUNTIF($A:$A,A2)=1 |
| Positive numbers only | =AND(ISNUMBER(A2),A2>0) |
| Code must start with "US-" | =LEFT(A2,3)="US-" |
| Date can't fall on a weekend | =WEEKDAY(A2,2)<6 |
| Email must contain @ | =ISNUMBER(SEARCH("@",A2)) |
2. You want to prevent duplicate invoice numbers in column B (starting at B2). Which formula do you use?
The rule must be TRUE to accept the entry: the invoice number must appear exactly once in the column.
Messages that help
The Input Message appears when someone selects the cell and explains what's expected, for example: "Enter the delivery date (mm/dd/yyyy)." The Error Alert appears when an entry breaks the rule, and it comes in three styles:
- What happens
- The invalid entry is rejected: fix it or cancel.
- Safety
- ●●●●
- Best for
- Codes, regions, quantities and anything that feeds formulas.
- What happens
- Shows the alert but lets you accept the entry with "Yes."
- Safety
- ●●●●
- Best for
- Unusual but possible values: a bigger discount than normal.
- What happens
- Shows the message and accepts the entry with "OK."
- Safety
- ●●●●
- Best for
- Reminders that shouldn't slow anyone down.
If someone pastes data from somewhere else, validation doesn't check it. To find invalid entries that are already there, use Data → Data Validation (arrow) → Circle Invalid Data. If the data is critical, combine validation with sheet protection.
Protect sheets and workbooks
By default every cell is marked "Locked," but locking only takes effect once you protect the sheet. The right order is:
- Step 1Pick the input cellsSelect the cells where other people should be able to type.
- Step 2Unlock themCtrl + 1 (Mac: Cmd + 1) → Protection tab → uncheck Locked.
- Step 3 · optionalHide the formulasOn cells with formulas, check Hidden so they don't show in the formula bar.
- Step 4Protect the sheetReview → Protect Sheet, with a password if you like, and check what's allowed (select cells, use AutoFilter, sort…).
On top of that, Review → Protect Workbook prevents adding, deleting, moving or hiding sheets. To let different people edit different ranges with their own passwords, use Review → Allow Edit Ranges.
Sheet protection prevents accidental changes, but it isn't encryption: there are tools that remove it. For confidential information (payroll, customer data), use File → Info → Protect Workbook → Encrypt with Password, and store that password somewhere safe: if you lose it, it can't be recovered.
3. You protect the sheet and now nobody can type in any cell, not even the input cells. What did you forget?
Every cell starts out locked. You have to unlock the input cells before you turn protection on.
4. Someone pasted data into a column with validation rules. How do you find the entries that break the rule?
Pasting skips validation. Circle Invalid Data marks every entry that doesn't meet the rule.
Case study: order entry at a distributor in Kansas City
Five sales reps logged orders in a shared workbook. Every month-end close, Jessica lost an afternoon fixing misspelled regions, impossible dates and duplicate invoice numbers before she could even run SUMIFS. She added three rules: a list from a table for the region, a date between the first of the month and =TODAY(), and =COUNTIF($B:$B,B2)=1 for the invoice number, all with the Stop style. Then she protected the sheet, leaving only the input columns unlocked.
From 48 rows with errors a month (4% of 1,200) down to 3, which had been pasted in from another file and which she found in seconds with "Circle Invalid Data."
Tips for working with validation
- Alt + ↓ (Mac: Option + ↓) opens a cell's drop-down without the mouse.
- To copy just the rule to another range: copy the cell and use Paste Special → Validation.
- To find every cell with validation: Home → Find & Select → Data Validation.
- To remove a rule: select the cells, open Data → Data Validation and click Clear All.
Practice exercise: an error-proof order form
Create a sheet with the columns Date, Customer, Product, Quantity, Status and Total:
- Date: only dates from 1/1/2026 through today (
=TODAY()as the end date). - Product: a list from a products table.
- Quantity: a whole number between 1 and 1,000, with an input message.
- Status: the list "Pending, Shipped, Delivered."
- Total: a formula with
XLOOKUPorVLOOKUPthat multiplies the price by the quantity. - Protect the sheet so people can type only in the first five columns.
See solution
- Date: Allow → Date → between, start
1/1/2026, end=TODAY(). - Product: with the products in a table
tblProducts(columns Product and Price), name the Product columnProductsand use=Productsas the source. - Quantity: Whole number → between 1 and 1000; on the Input Message tab type "Whole units between 1 and 1,000."
- Status: List with the source
Pending,Shipped,Delivered. - Total (F2):
=IFERROR(XLOOKUP(C2, tblProducts[Product], tblProducts[Price]) * D2, 0), or with VLOOKUP:=IFERROR(VLOOKUP(C2, tblProducts, 2, FALSE) * D2, 0). - Before protecting, select columns A through E of the data area and uncheck Locked; then Review → Protect Sheet.
- Test it: type a quantity of 0 or a product that doesn't exist. Your error alert should appear.
Summary
- Data → Data Validation limits what can be typed in a cell.
- Build lists from named tables; build dependent lists with
INDIRECT. - Custom rules use formulas such as
=COUNTIF($A:$A,A2)=1. - To protect: unlock the input cells first, then protect the sheet.
- Pasting skips validation: check with "Circle Invalid Data."
Next lesson: PivotTables, Charts and Dashboards in Excel. The Advanced level begins: you'll summarize thousands of rows in seconds and build a small dashboard.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.