• 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 in Google Sheets

    Objective:

    Learn how to restrict data entry using drop-down lists, checkboxes, and custom rules through the Data Validation tool.

    Step 1: Sample Table

    Task Name Status Completed
    Design Logo    
    Write Content    
    Final Review    

    We'll apply data validation to the Status and Completed columns.

    Step 2: Create a Drop-Down List (Status Column)

    Steps:

    1. Select cells B2 to B4 (Status column)

    2. Go to Data > Data validation

    3. Under Criteria, choose Drop-down

    4. Enter options: Not Started, In Progress, Completed

    5. Click Done

    Now each cell has a drop-down menu with selectable values.

    Step 3: Add Checkbox (Completed Column)

    Steps:

    1. Select cells C2 to C4

    2. Go to Insert > Checkbox

    3. You’ll now see checkboxes in the cells

    When a task is done, tick the checkbox.

    Step 4: Restrict Data Entry (Custom Rules)

    Let’s restrict the Task Name column to accept only entries with at least 3 characters.

    Steps:

    1. Select A2 to A4

    2. Go to Data > Data validation

    3. Under criteria, choose Text → Text length → greater than or equal to → type 3

    4. Click “Reject input” if rule not met

    5. Optionally, add a help text like “Enter at least 3 characters”

    Now short or empty task names will be blocked.

    Step 5: Custom Formula-Based Validation (Bonus)

    Use formulas to restrict entries. For example, allow only numbers greater than 10 in a column.

    Steps:

    1. Select the desired cell range (e.g., A2:A10)

    2. Go to Data > Data validation

    3. Under Criteria, select Custom formula is

    4. Enter the formula: =AND(ISNUMBER(A2), A2 > 10)

    5. Choose Reject input for invalid entries

    6. Optionally, add help text like “Enter a number greater than 10”

    Now, only numeric values greater than 10 will be accepted.

    Mini Project – Build a Team Task Manager

    You are managing a small team and want to assign tasks, track progress, and ensure only valid data is entered. Let’s build a structured task tracker with validations.

    Create this structure:

    Task ID Task Name Assigned To Priority Status Completed Notes
    TSK001 Landing Page UI          
    TSK002 API Integration          
    TSK003 QA Testing          

    Add the following validations:

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

    • Drop-down in Status: Not Started, In Progress, Completed

    • Checkbox in Completed column

    • Restrict Assigned To to at least 3 characters

    • Limit Task Name to minimum of 5 characters

    • Add help text in Notes: “Add comments or blockers (optional)”

    Bonus Tasks:

    • Create conditional formatting to highlight High priority tasks in red

    • Add a filter to show only incomplete tasks

    • Use =COUNTIF(G2:G4, TRUE) to count completed tasks

    This full mini-project simulates a real project dashboard while teaching data validation.

    ❮ Previous Next ❯