Data Validation

🚦 Data Validation: The Gatekeeper of Your Spreadsheet

Imagine your spreadsheet is a party. Data Validation is the bouncer at the door — it decides who gets in and who doesn’t!

🎯 What You’ll Learn

By the end of this guide, you’ll master:

• ✅ Data validation basics
• ✅ Dropdown lists
• ✅ Input messages (friendly hints)
• ✅ Error alerts (stop the bad data!)
• ✅ Custom validation formulas

Let’s turn you into a data gatekeeper! 🛡️

🧱 Part 1: Data Validation Basics

What Is Data Validation?

Think of a piggy bank with a coin slot. You can put coins in, but not toys or paper. The slot is the “rule” that controls what goes inside.

Data Validation = Rules for your cells.

It controls:

• What type of data can go in (numbers? dates? text?)
• What range of values are allowed (1 to 100? dates in 2024?)
• What happens when someone breaks the rules

Why Do We Need It?

Bad data = Bad decisions. Validation keeps your data clean!

How to Access Data Validation

graph TD
    A["Select a cell or range"] --> B["Go to Data tab"]
    B --> C["Click Data Validation"]
    C --> D["Set your rules!"]

Quick Path: Data → Data Validation

Simple Example: Allow Only Whole Numbers

1. Select cell A1
2. Go to Data → Data Validation
3. Under “Allow,” choose Whole number
4. Set: Between 1 and 100
5. Click OK

Now try typing “banana” — Excel says NO! 🚫

📋 Part 2: Dropdown Lists

The Magic of Dropdowns

Imagine ordering food. Instead of typing your order (and maybe misspelling “spaghetti”), you pick from a menu. That’s what dropdown lists do!

Dropdown = Pick from a list, no typing mistakes!

Creating a Dropdown List

Method 1: Type the options directly

1. Select your cell
2. Data → Data Validation
3. Allow: List
4. Source: Apple, Banana, Cherry
5. Click OK

Now you get a nice dropdown arrow! ⬇️

Method 2: Use a range

If your options are in cells E1:E5:

1. Select your target cell
2. Data → Data Validation
3. Allow: List
4. Source: =$E$1:$E$5
5. Click OK

Real-World Examples

Pro Tip: Dynamic Lists

Want your dropdown to grow automatically? Use a Named Range or Table!

=INDIRECT("MyList")

When you add items to the named range, the dropdown updates too! 🪄

💬 Part 3: Input Messages

Friendly Hints Before They Type

Input messages are like helpful signs at a theme park. They guide people BEFORE they do something wrong.

Input Message = A tooltip that appears when you click a cell

Setting Up Input Messages

1. Select your cell
2. Data → Data Validation
3. Go to the Input Message tab
4. Check “Show input message when cell is selected”
5. Add a Title and Message

Example

When someone clicks that cell, they see your helpful hint!

graph TD
    A["User clicks cell"] --> B["Tooltip appears"]
    B --> C["📅 Enter Date<br>Please use format: YYYY-MM-DD"]

Best Practices for Input Messages

• Keep them short (2-3 lines max)
• Use friendly language
• Give an example of correct input
• Add emojis for visual appeal! 🎯

⚠️ Part 4: Error Alerts

When Rules Are Broken

Error alerts are like a referee blowing the whistle. They step in AFTER someone tries to enter bad data.

Error Alert = What happens when invalid data is entered

Three Types of Error Alerts

Setting Up Error Alerts

1. Select your cell with validation
2. Data → Data Validation
3. Go to the Error Alert tab
4. Choose your Style (Stop/Warning/Info)
5. Add a Title and Error message

Example: Stop Alert

When to Use Each Type

graph TD
    A["What level of control?"] --> B{Critical data?}
    B -->|Yes| C["Use STOP"]
    B -->|No| D{Need warning?}
    D -->|Yes| E["Use WARNING"]
    D -->|No| F["Use INFORMATION"]

• Stop: Financial data, IDs, critical fields
• Warning: Dates, quantities (might have exceptions)
• Information: Notes, comments, optional fields

🧙‍♂️ Part 5: Custom Validation Formulas

Unleash the Power!

Custom formulas are like teaching the bouncer new skills. You can create ANY rule you can imagine!

Custom Formula = Write your own validation logic

How Custom Formulas Work

1. Select your cell
2. Data → Data Validation
3. Allow: Custom
4. Enter a formula that returns TRUE or FALSE

If formula = TRUE → Data allowed ✅ If formula = FALSE → Data blocked ❌

Example 1: Must Start with “INV-”

=LEFT(A1,4)="INV-"

This ensures invoice numbers start with “INV-”:

• INV-001 → ✅ Allowed
• ABC-001 → ❌ Blocked

Example 2: Future Dates Only

=A1>TODAY()

This only allows dates after today:

• Tomorrow’s date → ✅ Allowed
• Yesterday’s date → ❌ Blocked

Example 3: Email Must Contain @

=ISNUMBER(FIND("@",A1))

• john@email.com → ✅ Allowed
• john.email.com → ❌ Blocked

Example 4: No Duplicates!

=COUNTIF(A:A,A1)=1

This prevents duplicate entries in column A:

• First “Apple” → ✅ Allowed
• Second “Apple” → ❌ Blocked

Common Custom Formula Patterns

Pro Tips for Custom Formulas

1. Always reference the first cell in your range
2. Use relative references (A1, not $A$1) for ranges
3. Test your formula in a regular cell first
4. The formula must return TRUE or FALSE

🎯 Putting It All Together

Here’s a complete validation setup for an order form:

graph TD
    A["Order ID Cell"] --> B["Custom: =LEFT A1 4 ='ORD-'"]
    C["Quantity Cell"] --> D["Whole number: 1 to 1000"]
    E["Status Cell"] --> F["List: Pending, Shipped, Done"]
    G["Ship Date Cell"] --> H["Date: >= TODAY"]

    B --> I["Error: Must start with ORD-"]
    D --> J["Error: Enter 1-1000"]
    F --> K["Input: Select order status"]
    H --> L["Error: Must be future date"]

✨ Key Takeaways

🚀 You’re Ready!

You now know how to:

• ✅ Set up basic validation rules
• ✅ Create dropdown lists that prevent errors
• ✅ Add helpful input messages
• ✅ Configure error alerts at three levels
• ✅ Write custom formulas for any scenario

Your spreadsheets are now protected by the best bouncer in town! 🛡️

Remember: Good data in = Good decisions out!