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:
-
Select cells B2 to B4 (Status column)
-
Go to Data > Data validation
-
Under Criteria, choose Drop-down
-
Enter options: Not Started, In Progress, Completed
-
Click Done
Now each cell has a drop-down menu with selectable values.
Step 3: Add Checkbox (Completed Column)
Steps:
-
Select cells C2 to C4
-
Go to Insert > Checkbox
-
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:
-
Select A2 to A4
-
Go to Data > Data validation
-
Under criteria, choose Text → Text length → greater than or equal to → type 3
-
Click “Reject input” if rule not met
-
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:
-
Select the desired cell range (e.g., A2:A10)
-
Go to Data > Data validation
-
Under Criteria, select Custom formula is
-
Enter the formula: =AND(ISNUMBER(A2), A2 > 10)
-
Choose Reject input for invalid entries
-
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.
