Data Validation & Protection

Why Validate and Protect?

Data Validation: Prevents users from entering invalid data — the wrong department name, a negative salary, a date in the wrong format. Validation rules enforce consistency at the point of entry.
Protection: Prevents accidental (or intentional) changes to formulas, layouts, or sensitive data. Lock the sheet so users can only edit designated input cells.

Data Validation

Setting Up Validation

Select the cell(s) to validate
Data → Data Validation (or Alt + D + L)
In the Settings tab, choose validation type
Optionally add Input Message and Error Alert

Validation Types

Type	Use
Whole Number	Allow only integers; set min/max
Decimal	Allow decimals; set min/max
List	Dropdown menu of allowed values
Date	Allow only dates in a range
Time	Allow only times in a range
Text Length	Limit text to a character count
Custom	Allow only if a formula returns TRUE

The most widely used validation — forces users to pick from a preset list.

Method 1: Typed List

Select cells → Data Validation → Allow: List
Source: Finance,Technology,Marketing,HR,Operations (comma-separated, no spaces after commas)
Cells now show a dropdown with those options

Method 2: Range-Based List (Recommended)

Enter your list in a separate column (e.g., G1:G5 with the department names)
Select cells → Data Validation → Allow: List → Source: =$G$1:$G$5
Update G1:G5 and the dropdown updates automatically

Method 3: Named Range List

Name your list: select G1:G5 → Name Box → type DepartmentList → Enter
Data Validation → Source: =DepartmentList
Works across sheets; easier to maintain

If your list is in an Excel Table (e.g., tblDepartments[Department]):

Source: =INDIRECT("tblDepartments[Department]")
Auto-expands when you add new items to the table

Dependent (Cascading) Dropdowns

Example: Select a Region first, then only cities for that region appear in the second dropdown.

Setup

Named ranges:
"North" = A1:A3 (Delhi, Chandigarh, Jaipur)
"South" = B1:B3 (Chennai, Bangalore, Hyderabad)
"East"  = C1:C3 (Kolkata, Bhubaneswar, Patna)

Cell E1: Region dropdown (North, South, East)
Cell F1: City dropdown
  → Source: =INDIRECT(E1)
  → When E1 = "North", INDIRECT("North") → Delhi, Chandigarh, Jaipur

Numeric Validation Rules

Allow only salaries between 30,000 and 500,000:
→ Whole Number → between → Minimum: 30000 → Maximum: 500000

Allow only positive numbers:
→ Decimal → greater than → 0

Allow only future dates:
→ Date → greater than → =TODAY()

Allow only past dates:
→ Date → less than → =TODAY()

Text Length Validation

Limit to 10 characters (e.g., employee code):
→ Text Length → less than or equal to → 10

Require exactly 10 characters:
→ Text Length → equal to → 10

Custom Validation

Use a formula that returns TRUE (allow) or FALSE (reject):

Allow only unique values in column A:
→ Custom → Formula: =COUNTIF($A$2:$A$100, A2) = 1
→ Rejects if the same value already exists in the column

Allow only weekdays (no weekends):
→ Custom → Formula: =WEEKDAY(A2, 2) <= 5
→ Rejects if the entered date is Saturday or Sunday

Allow only email format (basic check):
→ Custom → Formula: =AND(ISNUMBER(FIND("@",A2)), ISNUMBER(FIND(".",A2)))
→ Rejects if there's no @ or .

Display a helpful message when the cell is selected:

Data Validation → Input Message tab
Title: "Department"
Message: "Select from the list: Finance, Technology, Marketing, HR"
Check "Show input message when cell is selected"

A small tooltip appears when the user clicks the cell.

Error Alert

Control what happens when invalid data is entered:

Data Validation → Error Alert tab
Style:
- Stop — prevents entry; user must enter valid data
- Warning — warns but allows the user to continue
- Information — informs but doesn't block

Style: Stop
Title: "Invalid Department"
Error message: "Please select a valid department from the dropdown list."

Finding Validation Errors in Existing Data

If you apply validation to a range that already has invalid data:

Data → Data Validation → Circle Invalid Data → Red circles appear around cells that violate the validation rules → Clear Validation Circles removes the circles

Sheet Protection

Lock your worksheet so users can only edit designated input cells.

Step 1: Unlock Input Cells

By default, ALL cells are "Locked" (but locking only activates when you protect the sheet). So first, unlock the cells you DO want editable:

Select input cells (e.g., B2:B20 where users enter data)
Ctrl + 1 → Protection tab → uncheck Locked

Step 2: Protect the Sheet

Review → Protect Sheet
Set a password (optional but recommended)
Choose what users can do even when protected:
- Select locked cells
- Select unlocked cells
- Format cells
- Insert/delete rows
- Sort, AutoFilter, etc.
Click OK

Now users can only edit the unlocked cells; everything else is read-only.

Common Protection Settings

Allow: Select locked cells + Select unlocked cells (minimum)
Allow sort/filter if users need to explore the data
Do NOT allow: Insert/delete rows (protects your formula structure)

Unprotecting

Review → Unprotect Sheet → enter password.

Workbook Protection

Protect Workbook Structure

Prevents users from:

Adding/deleting/renaming/moving sheets
Hiding or unhiding sheets

Review → Protect Workbook → Structure

Protect Individual Sheets with Different Passwords

Sheet "Data Entry": password "enter123" → users can edit input cells
Sheet "Formulas": password "admin456" → no one can edit (except admin)
Sheet "Dashboard": no password → view-only (people can still see it)

Hiding Rows, Columns, and Sheets

Hide Rows/Columns

Right-click row/column header → Hide (or Ctrl + 9 for row, Ctrl + 0 for column) Unhide: Select rows/columns around the hidden ones → right-click → Unhide

Hide a Sheet

Right-click sheet tab → Hide Unhide: Right-click any tab → Unhide → select sheet

Very Hidden Sheets (VBA)

To prevent sheets from appearing in the Unhide menu:

Alt + F11 → VBA editor → select the sheet → Properties window → Visible = xlSheetVeryHidden
Can only be unhidden via VBA or by someone who knows it's there

Practical Examples

Example 1: Employee Data Entry Form

B3: Employee Name
→ Text Length, max 50 characters
→ Input message: "Enter full name, max 50 characters"

B4: Department
→ List dropdown: Finance, Technology, Marketing, HR, Operations
→ Error: Stop — "Please select from the list"

B5: Join Date
→ Date, between 01/01/2000 and today
→ Error: Warning — "Join date seems unusual. Continue?"

B6: Salary
→ Decimal, between 30000 and 1000000
→ Error: Stop — "Salary must be between ₹30,000 and ₹10,00,000"

Protection:
→ Unlock only B3:B6
→ Protect sheet with password
→ All formula cells (totals, lookups) are locked

Example 2: Invoice Template

Sheet structure:
- "Data Entry" tab: unlocked cells for invoice date, client name, line items
- "Invoice" tab: formulas auto-calculate totals from Data Entry — LOCKED
- "Config" tab: tax rate, company name, logo — HIDDEN; very hidden via VBA

Data validation on Data Entry:
- Date: only future dates (>= TODAY())
- Quantity: whole number > 0
- Rate: decimal > 0
- Currency dropdown: INR, USD, EUR

Example 3: Budget Template Shared with Team

Scenario: Finance shares a budget template with department heads.
Each department fills in their own section; they must not touch others.

Setup per department sheet:
1. Unlock only that department's input range (e.g., C4:C15)
2. Protect sheet with one shared password
3. Protect workbook structure (prevent adding sheets)
4. Validation on each cell: Whole Number >= 0

Common Mistakes

1. Validation doesn't block paste

User pastes data from another source → validation rules are bypassed
Fix: After pasting, use Circle Invalid Data to find violations
Or: Use VBA to block paste (Worksheet_Change macro that re-validates)

2. Forgetting to unlock cells before protecting

All cells are locked by default → protect sheet → no one can edit ANYTHING
Fix: Unlock input cells FIRST, then protect the sheet

3. Lost the protection password

No built-in recovery. The only fix is to use a third-party password recovery tool.
Best practice: Store passwords in a password manager, not in the file itself.

=Sheet2!$A$1:$A$10   → #REF! in validation dialog if source sheet is on a different sheet
Fix: Use a Named Range that references the other sheet
Create name "DeptList" = Sheet2!$A$1:$A$10 → use =DeptList as source

Practice Exercises

Create a dropdown validation for a "Status" column with values: New, In Progress, Completed, On Hold.
Set up a salary validation that rejects values below ₹25,000 or above ₹10,00,000 with a Stop error alert.
Build a cascading dropdown: first dropdown selects Region (North/South/East/West), second shows cities for that region.
Protect a template sheet so users can only edit 5 specific input cells. Set a password.
Use Circle Invalid Data to find and highlight all existing cells that violate a custom validation rule (e.g., negative values in a quantity column).

Summary

In this chapter you learned:

Data Validation: Data → Data Validation → allow Whole Number, Decimal, List, Date, Text Length, or Custom (formula)
Dropdown lists: Source = typed values, cell range, or named range; INDIRECT() for cascading dropdowns
Custom validation: Formula returns TRUE = allow; FALSE = reject (unique values, email format, weekdays only)
Input Message: tooltip displayed when user selects the cell
Error Alert: Stop (blocks entry) / Warning (warns) / Information (informs) — set per rule
Circle Invalid Data: Data → Data Validation → Circle Invalid Data — flags existing violations
Sheet protection: unlock input cells first (Ctrl+1 → Protection → uncheck Locked), then Review → Protect Sheet
Workbook protection: Review → Protect Workbook → prevents adding/deleting/renaming sheets
Hide / Very Hide: right-click tab → Hide; VBA → xlSheetVeryHidden for deeper hiding
Validation doesn't block copy-paste; use Circle Invalid Data after imports to find violations

Next up: Named Ranges & Excel Tables — organise your formulas and data with structured references.