• Login / Signup

    • Welcome Back

    Google icon Sign in with Google
    OR
    Forgot Password?
    I agree to abide by Pharmadaily Terms of Service and its Privacy Policy

    Create Account

    Google icon Sign up with Google
    OR
    By signing up, you agree to our Terms of Service and Privacy Policy
    Instagram
    youtube
    Facebook

    Data Validation

    What is Data Validation?

    Data Validation in Google Sheets is used to control what kind of data users can enter into a cell. This helps keep data accurate, consistent, and easy to analyze.

    With it, you can:

    • Create drop-down lists

    • Add checkboxes

    • Restrict text or number formats

    • Use formulas to apply custom rules

    Learning Goals

    By the end of this tutorial, you will:

    • Create drop-down menus for specific choices

    • Use checkboxes for Boolean data

    • Restrict input by character length or formula

    • Build a mini task tracker project using validation

    Prerequisites

    Step 1: Create a Sample Table

    Enter the following data in your blank sheet:

    Task Name Status Completed
    Design Homepage    
    Create Proposal    
    QA Testing    

    Step 2: Add a Drop-down List (for "Status")

    We’ll let users pick status from pre-defined options.

    Steps:

    1. Select cells under Status (B2 to B4)

    2. Click Data > Data validation

    3. In the sidebar, set criteria as:

      • Drop-down

      • Options: Not Started, In Progress, Completed

    4. Click Done

    Now you have clickable drop-downs in each cell.

    Step 3: Add Checkboxes (for "Completed")

    Steps:

    1. Select cells under Completed (C2 to C4)

    2. Click Insert > Checkbox

    You now have checkboxes to mark tasks as complete.

    Step 4: Restrict Data Entry (Task Name Min 3 Characters)

    Let’s make sure tasks are at least 3 characters long.

    Steps:

    1. Select cells under Task Name (A2 to A4)

    2. Go to Data > Data validation

    3. Choose:

      • Criteria: Text → Text length → greater than or equal to → 3

    4. Click “Reject input”

    5. Add help text: Please enter at least 3 characters

    Now, short task names won’t be allowed.

    Step 5: Use Custom Formula for Validation

    You can restrict values with a formula. Example: Only allow numbers > 10.

    Steps:

    1. Use a new column (e.g., Column D)

    2. Select D2 to D10

    3. Go to Data > Data validation

    4. Set criteria: Custom formula is

    5. Enter: =AND(ISNUMBER(D2), D2 > 10)

    6. Click “Reject input”

    Now, users must enter numbers > 10 only.

    Mini Project – Team Task Tracker

    Let’s build a practical task tracker for team management.

    Sample Table:

    Task ID Task Name Assigned To Priority Status Completed Notes
    TSK001 Build Wireframes          
    TSK002 Server Setup          
    TSK003 Testing Phase          

    Add the Following Validations:

    • Drop-down in Priority: Low, Medium, High

    • Drop-down in Status: To Do, In Progress, Done

    • Checkbox in Completed column

    • Assigned To must be minimum 3 characters

    • Task Name must be minimum 5 characters

    • Notes column: Add notes or blockers (optional)

    Bonus Tasks:

    • Apply conditional formatting to color "High" priority red

    • Add filter to view only tasks not completed

    • Use formula: =COUNTIF(F2:F10, TRUE) to count how many tasks are done

    What You Practiced:

    • Creating drop-downs

    • Adding checkboxes

    • Restricting input with rules and formulas

    • Designing a real-world project sheet

    Would you like this as a Google Sheet or Google Slides version? Let’s move to the next lesson if you're ready.

     

    ❮ Previous Next ❯