What you will build
You will set up the intended Status validation rule, verify the exact range and allowed dropdown values, test an allowed entry, and restore the original sample so you know what to check when a valid-looking entry is rejected.
Learning objectives
Open the practice workbook
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Hands-on practice
Step 1: Select B2:B4, the three Status cells in the data rows. Do not include the header B1; the validation rule should apply only where users enter statuses.
Step 2: Choose Data > Data validation. This opens the Data validation rules sidebar for the selected range.
Step 3: In the rule, confirm Apply to range is Example!B2:B4, Criteria is Dropdown, and the allowed items are exactly Done, Working, and Planned. If you are starting from a fresh practice copy with no rule yet, use Add rule and enter these same settings.
Step 4: Select B2 and press Enter to open its dropdown. Confirm that the same three allowed choices are available in the cell.
Step 5: Choose Working in B2. Sheets accepts the value because it exactly matches an allowed dropdown item; this proves the rule is working for that cell.
Step 6: Restore B2 to Done so the workbook returns to the original sample before you compare the checkpoint.
Checkpoint
| Task | Status |
|---|---|
| Brief | Done |
| Dataset | Working |
| QA | Planned |
The final sample is restored. The validation rule on B2:B4 allows exactly Done, Working, and Planned.
Why a valid-looking entry can still be rejected
Data validation checks both where the rule applies and whether the entered value matches the rule. A value can look reasonable but still fail if the rule is attached to the wrong cells, if the expected item is missing from the dropdown list, or if the text differs from the allowed item.
Common mistakes and fixes
Do not fix the symptom by removing validation or typing over a warning. Correct the rule range or dropdown items, then test one known allowed value and restore the sample.
Independent practice
Add one temporary task row, extend the validation rule to its Status cell, choose one allowed status from the dropdown, then remove the test row and restore the original range when finished.
Key takeaways
- ✓Apply data validation only to the intended input cells, not the header.
- ✓For this sample, the validated range is B2:B4.
- ✓The allowed Dropdown items are exactly Done, Working, and Planned.
- ✓Testing a known allowed value confirms whether the rule works on the target cell.
- ✓After troubleshooting, restore the original sample so the checkpoint remains reproducible.
Check your understanding
Answer four questions about the practice sheet.
Next lesson
Continue after you can identify whether a rejection comes from the validation range or from the allowed-value list.
Comments
No comments yet. Be the first to join the discussion.