What you will build

Add a controlled Status dropdown to the Personal budget table so users can choose only the valid workflow states.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

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.

Make a Google Sheets copy (access required) ↗View original

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.

The current-audit _result workbook shows the complete A1:E5 personal-budget table before validation is created.

Hands-on practice

Step 1: Select E2:E5 in the Status column; do not include the E1 header.

The Status range E2:E5 is selected in the _result workbook; the E1 header is excluded.

Step 2: Choose Data > Data validation. In the Data validation rules sidebar, choose Add rule. Confirm Apply to range is E2:E5, keep Criteria as Dropdown with exactly Planned and Posted, open Advanced options and keep If the data is invalid = Reject the input, then choose Done.

The Data menu is open with the pointer directly on Data validation.
The Data validation rules sidebar is open for E2:E5 with the pointer on Add rule.
The rule editor shows Apply to range E2:E5, a Dropdown with Planned and Posted, Reject the input, and the Done button.

Step 3: Open the dropdown in one Status cell and confirm Planned and Posted are available. Then try entering a value outside those two options; Sheets should show a data-validation error and keep the original valid value unchanged.

The E2 dropdown is open and shows exactly Planned and Posted.
Google Sheets rejects Invalid in E2 and states that the allowed values are Planned and Posted.

Checkpoint

DateCategoryDescriptionAmountStatus
2026-09-01IncomeSalary1500Posted
2026-09-02FoodGroceries85Posted
2026-09-03TransportBus pass30Planned
2026-09-05HousingRent700Planned

Checkpoint: A1:E5 still matches the five headers and four sample rows; E2:E5 has a dropdown containing only Planned and Posted, and values outside those two options are rejected by Reject the input.

Final checkpoint: A1:E5 still contains the original four sample rows after validation testing.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the Date, Category, Description, Amount, Status source fields.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

  • ✓Apply the dropdown only to E2:E5; do not include the E1 header.
  • ✓Open Data > Data validation, choose Add rule, and confirm Apply to range is E2:E5.
  • ✓Use Criteria = Dropdown with exactly Planned and Posted.
  • ✓In Advanced options, keep If the data is invalid = Reject the input.
  • ✓Verify the dropdown and try an invalid value; Sheets should reject it while the A1:E5 sample remains unchanged.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. Which range should receive the Status dropdown in this lesson?
2. Which menu path opens the validation-rule workflow?
3. Which values belong in the dropdown?
4. Which setting blocks values outside the dropdown?

Next lesson

Continue in recommended learning order with Apply formatting and freeze headers in Personal budget.