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
-
A Google account
-
Go to Google Sheets and open a blank sheet
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:
-
Select cells under Status (B2 to B4)
-
Click Data > Data validation
-
In the sidebar, set criteria as:
-
Drop-down
-
Options: Not Started, In Progress, Completed
-
-
Click Done
Now you have clickable drop-downs in each cell.
Step 3: Add Checkboxes (for "Completed")
Steps:
-
Select cells under Completed (C2 to C4)
-
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:
-
Select cells under Task Name (A2 to A4)
-
Go to Data > Data validation
-
Choose:
-
Criteria: Text → Text length → greater than or equal to → 3
-
-
Click “Reject input”
-
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:
-
Use a new column (e.g., Column D)
-
Select D2 to D10
-
Go to Data > Data validation
-
Set criteria: Custom formula is
-
Enter: =AND(ISNUMBER(D2), D2 > 10)
-
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.
