Typos in a status column ('Done', 'done', 'DONE', 'doen') destroy sorting and counting. A drop-down list enforces clean choices and looks polished.
Two minutes per column, plus the color coding upgrade that makes project trackers feel professionally built.
Before you start
- Google Sheets
- A column that needs controlled options
The steps, start to finish
1. Select the cells, open Data validation
Highlight the column range (click the letter to catch all of it), then Data, Data validation. The rules panel opens on the right.
2. Add the options
Click Add rule, choose Dropdown under Criteria, and type each option: Paid, Pending, Overdue, one per row with colors available right there. Each option can carry a chip color, that's the secret to visual dashboards.
- Colors turn cells into instant visual status
- Options can also live in a range on another tab (Dropdown from a range)
3. Choose how rebels are handled
The Advanced options offer 'Show warning' (flags odd entries in red but allows them) or 'Reject input' (blocks anything not on the list). Reject for discipline, warn for diplomacy.
4. Dropdown from another sheet (the scalable way)
Criteria: Dropdown (from a range), point it at your list on a Lists tab. Edit the list once, every dropdown updates automatically: the pattern professionals use for trackers that live for years.
5. Copy it down responsibly
Data validation travels with copy paste: paint the format wherever the column grows. Or apply to the entire column letter from the start and forever forget about it.
6. Bonus: the magic checkbox and timestamps
Insert, Checkbox gives true/false cells data loves (and COUNTIF counts). Combined with drop-downs, a simple sheet now powers project dashboards, habit trackers and inventory counts.
A few things worth knowing
- Filter views (Data, Filter views) pair perfectly: row colors from your drop-downs become instant 'show only Overdue' dashboards.
- Named ranges (Data, Named ranges) make 'Dropdown from a range' references readable: Products instead of Lists!A2:A40.
- The same feature exists in Excel (Data, Data Validation, List): syntax differs slightly, the concept transfers completely.
Keep reading
- How to Use XLOOKUP (The Formula That Makes VLOOKUP Obsolete)
- How to Use Pivot Tables: Summarize 10,000 Rows in 60 Seconds
- How to Unsend an Email in Gmail (And Extend the Grace Period to 30 Seconds)
Column, Data validation, options with colors, reject or warn, Lists tab for life. Your spreadsheet just gained manners and a dashboard suit.
