Google Sheets Dropdowns and Data Validation (With Coloured Chips)

Shared sheets get messy fast: “Delhi”, “delhi “, “New Delhi”. Dropdowns stop that at the source.

A basic dropdown

1. Select the cells.
2. Insert → Dropdown (or Data → Data validation → Add rule).
3. Choose “Dropdown” and type the options, or “Dropdown (from a range)” and point to a list on another tab.

Each option can have its own colour, so statuses like Pending, Approved and Rejected show as coloured chips.

Keep the list in one place

Put master lists (states, cost centres, product codes) on a separate tab named Lists and use “from a range”. When you add a new item to the list, every dropdown updates.

Reject bad entries

In the rule’s advanced options, choose “Reject the input” instead of “Show a warning”. Pasted values that aren’t in the list are then refused.

Dependent dropdowns

Want the City list to depend on the State chosen? Put each state’s cities in a column headed by the state name on the Lists tab, then in a helper column use:

=FILTER(Lists!B2:Z50, Lists!B1:Z1 = A2)

and point the City dropdown at that helper range. (This works per row with a helper area; for many rows, Apps Script is cleaner.)

Other useful rules

• Dates only: “Is valid date” plus “is after” a start date.
• Numbers in a range: amounts between 0 and 10,00,000.
• Custom formula: =REGEXMATCH(A2,"^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$") checks the shape of a GSTIN.

Coming from Excel? Read Google Sheets vs Excel.