Chapter 8: Data Validation & Protection

Data Validation & Protection.

This chapter is all about making your worksheets foolproof and user-friendly, especially when sharing files with colleagues, family, students, or clients. Data validation prevents silly mistakes (wrong entries), shows helpful hints, and gives clear error messages. Protection locks down the sheet so only specific cells can be edited — perfect for templates, invoices, budgets, or reports.

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you at your computer (in Airoli, Maharashtra — hope the weather is nice today!). We’ll use real-life examples from our ongoing tea shop sales workbook. All steps are based on Excel for Microsoft 365 / Excel 2021+ / 2024 / 2025–2026 versions (they haven’t changed much in recent years).

Let’s make your file safe and smart!

1. Data Validation Rules (Dropdown Lists, Whole Numbers, etc.)

Data Validation → found on Data tab → Data Tools group → Data Validation button (or Alt + A + V + V).

Main purpose: Restrict what users can type in a cell.

A. Create a Dropdown List (most popular use)

Example 1: City dropdown for customer location

1. In a new sheet or your SalesLog table, go to column City (say F2:F100).

2. First, create the list of allowed cities somewhere safe (e.g., in Sheet2, cells A1:A5):

   • A1: Mumbai
   • A2: Navi Mumbai
   • A3: Thane
   • A4: Pune
   • A5: Kalyan

   (Tip: Put this list on a hidden sheet or far right — users won’t mess with it.)

3. Select the cells where you want the dropdown → e.g., F2:F100 in SalesLog.

4. Go to Data tab → Data Validation → Settings tab.

5. Allow: List

6. Source: =Sheet2!$A$1:$A$5 (or if using a table, name the range “CityList” and type =CityList)

7. Check In-cell dropdown (usually already checked).

8. Click OK.

→ Now clicking any cell in F2:F100 shows a nice dropdown arrow → only these cities allowed!

Pro tip: For dynamic list (auto-expands when adding cities):

• Convert your city list to an Excel Table (Ctrl + T) → name it “CityTable”.
• Source: =CityTable[City] (structured reference — very clean!)

Example 2: Yes/No dropdown

• Allow: List
• Source: Yes,No → Simple binary choice.

B. Whole Numbers / Decimals / Dates / etc.

Example – Quantity must be whole number between 1 and 200

1. Select Qty column (C2:C100).
2. Data Validation → Settings:
   • Allow: Whole number
   • Data: between
   • Minimum: 1
   • Maximum: 200
3. Ignore blank → usually leave checked (allows empty cells).

Other useful types:

• Decimal → e.g., Price between 10 and 100
• Date → e.g., Order Date between 01-Jan-2026 and TODAY()+30
• Text length → e.g., Customer Name ≤ 50 characters
• Custom → advanced formula, e.g., =AND(C2>=1, MOD(C2,1)=0) for whole numbers

2. Input Messages and Error Alerts

These make your sheet helpful instead of frustrating.

A. Input Message (shows when cell is selected – like a tooltip)

Example – Guide for Quantity

1. Select Qty cells (C2:C100).
2. Data Validation → Input Message tab.
3. Check Show input message when cell is selected.
4. Title: “Quantity Rules”
5. Input message: “Please enter whole number only. Minimum: 1 Maximum: 200 (e.g., 45 cups of Chai)”
6. OK.

→ Now when user clicks any Qty cell → yellow box pops up with your instructions!

B. Error Alert (shows when invalid data entered)

Example – Custom error for wrong Qty

1. In same Data Validation dialog → Error Alert tab.
2. Check Show error alert after invalid data is entered.
3. Style: Stop (red X – blocks entry) (or Warning – yellow triangle – allows override; Information – green i – just informs)
4. Title: “Invalid Quantity!”
5. Error message: “Sorry, quantity must be a whole number between 1 and 200. Please correct and try again. Contact manager if needed.”
6. OK.

→ Try typing 0 or 250 or “abc” → see your nice red popup!

Tip: Keep messages short & friendly (max 225 characters). Use Stop style for critical fields (like Qty), Warning for others.

3. Protecting Worksheets and Workbooks

Protection prevents accidental (or intentional) changes to formulas, formatting, etc.

A. Protect a Worksheet (most common)

Step-by-step:

1. First, decide which cells should be editable (usually input cells like Qty, City).
2. Select all cells (Ctrl + A).
3. Right-click → Format Cells (or Ctrl + 1) → Protection tab → uncheck Locked → OK. (By default, all cells are Locked — we’re unlocking everything first.)
4. Now select only the cells you want to lock (e.g., formula cells like Total =[@Qty]*[@Price], headers, tax rate).
5. Right-click → Format Cells → Protection → check Locked → OK.
6. Go to Review tab → Protect Sheet.
7. Password (optional – but recommended for shared files): type a simple password (e.g., “tea2026” – remember it!).
8. Check/uncheck permissions (default is good: allow select locked/unlocked cells, use autofilter, sort, etc.).
9. OK → retype password if set.

→ Sheet is now protected! Users can only edit unlocked cells (input areas). Formulas, headers, formatting stay safe.

To unprotect: Review → Unprotect Sheet → enter password.

B. Protect the Workbook (structure – prevents adding/deleting sheets)

• Review tab → Protect Workbook → check Structure → set password (optional). → Stops people from renaming, deleting, hiding sheets, or adding new ones.

4. Unlocking Cells for Editing (on Protected Sheet)

You already did this in step 3 above!

Quick recap – Allow specific cells editable:

1. Unprotect sheet first (if already protected).
2. Select cells to allow editing → Format Cells → Protection → uncheck Locked.
3. Protect sheet again.

Advanced tip – Allow specific users to edit ranges with different passwords (Review → Allow Users to Edit Ranges → New → set range + password → then protect sheet). Useful in offices with multiple people.

Quick Summary Table – Chapter 8

Homework / Practice (Do This Today!)

Use your TeaSales table or SalesLog sheet:

1. In City column → add dropdown from a list (Mumbai, Navi Mumbai, Thane, Pune…)
2. In Qty column → whole number 1–500 + input message “Enter cups sold” + error alert “Must be 1–500 only!”
3. In Price → decimal between 5 and 100.
4. Unlock input columns (Qty, City, etc.) → lock formula columns (Total) and headers.
5. Protect the sheet with password “tea2026”.
6. Test: Try typing “abc” in Qty → see your error message!
7. Try changing a formula → should be blocked.
8. Unprotect → make a small change → protect again.