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
  1. Where it lives and what it can do
  2. Drop-down lists
  3. Custom rules with formulas
  4. Messages that help
  5. Protect sheets and workbooks
  6. Case study: order entry at a distributor in Kansas City
  7. Summary
What you'll learn
  • 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.
📥 Practice this lesson

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.

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.

7rule types: whole number, decimal, list, date, time, text length and custom
3error alert styles: Stop, Warning and Information
2layers of defense: validate what gets typed and protect what shouldn't be touched
  1. Choose the rule type
  2. Build drop-down lists
  3. Formula-based rules
  4. Helpful messages
  5. 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:

OptionUse it forExample
Whole numberQuantities, agesBetween 1 and 500 units
DecimalPrices, percentagesDiscount between 0 and 0.3
ListA fixed set of valuesRegion: West, Central, East
DateDates within a rangeBetween 1/1/2026 and today
TimeHours and shiftsBetween 8:00 AM and 6:00 PM
Text lengthFixed-length codesA 5-digit ZIP code
CustomAny rule you can write as a formulaNo duplicate invoice numbers

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)

  1. On a helper sheet (say, Lists), type the values in a column and convert it to an Excel table (lesson 4) named tblRegions.
  2. Select the table's column and give it a name in the Name Box, for example Regions.
  3. 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.

In Microsoft 365: an automatic list without duplicates

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.

  1. 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 becomes New_York.
  2. In column A, create a list of the states.
  3. 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?

  • The list shows up in color.
  • Nobody can change the list.
  • New values added to the table show up in the drop-down automatically.

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).

RuleFormula (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?

  • =COUNTIF($B:$B,B2)>1
  • =COUNTIF($B:$B,B2)=1
  • =COUNT(B2)=1

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:

StopWon't let you continue
What happens
The invalid entry is rejected: fix it or cancel.
Safety
●●●●
Best for
Codes, regions, quantities and anything that feeds formulas.
The safest
WarningWarns and asks
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.
Exceptions
InformationJust informs
What happens
Shows the message and accepts the entry with "OK."
Safety
●●●●
Best for
Reminders that shouldn't slow anyone down.
Reminder
Validation isn't bulletproof

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:

  1. Step 1Pick the input cellsSelect the cells where other people should be able to type.
  2. Step 2Unlock themCtrl + 1 (Mac: Cmd + 1) → Protection tab → uncheck Locked.
  3. Step 3 · optionalHide the formulasOn cells with formulas, check Hidden so they don't show in the formula bar.
  4. 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.

Protection isn't security

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?

  • To uncheck "Locked" on the input cells before protecting the sheet.
  • To set a password.
  • To protect the workbook too.

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?

  • Validation would have rejected them.
  • With "Circle Invalid Data."
  • With Ctrl + Z.

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

Jessica, sales analystJanitorial supply distributor · Kansas City, Missouri · about 1,200 orders a month

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.

Rows with errors · before

Region22
Dates15
Duplicates11

Rows with errors · after

Region0
Dates0
Duplicates3

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:

  1. Date: only dates from 1/1/2026 through today (=TODAY() as the end date).
  2. Product: a list from a products table.
  3. Quantity: a whole number between 1 and 1,000, with an input message.
  4. Status: the list "Pending, Shipped, Delivered."
  5. Total: a formula with XLOOKUP or VLOOKUP that multiplies the price by the quantity.
  6. 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 column Products and use =Products as 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.

🎓 Save your progress and get a free certificate

Create your account in 30 seconds, mark your lessons and download your certificate at the end.

Enroll for free