How to Create Dropdown Lists with Data Validation in Google Sheets

When sharing collaborative spreadsheets with team members or clients in Google Sheets, free-form text entry inevitably leads to inconsistent records. Different contributors might type “In Progress”, “in progress”, “WIP”, or “pending” for the exact same project status, breaking spreadsheet formulas, pivot tables, and chart aggregations. Implementing Dropdown Lists using Data Validation enforces strict data consistency, speeds up cell input, and improves visual clarity with modern, color-coded pill chips.

Why Use Dropdown Lists in Google Sheets?

Restricting cell input to predefined dropdown choices delivers immediate benefits:

• Standardized Records: Eliminates typos, capitalization variances, and informal abbreviations across shared collaborative worksheets.
• Interactive Dropdown Chips: Renders choices as stylish, rounded chips with custom background colours that visually indicate priority levels or completion states.
• Formula Reliability: Guarantees that functions like SUMIF, COUNTIF, and VLOOKUP calculate without failing due to rogue spelling errors.

Method 1: Insert a Dropdown List via the Insert Menu

Google Sheets provides a dedicated dropdown creator right on the main toolbar:

1. Open your spreadsheet in Google Sheets.
2. Highlight the cells or column range where you want dropdowns to appear (e.g., column C from cell C2 down to C100).
3. Click on the Insert menu at the top of the window and select Dropdown.
4. The Data validation rules panel will open on the right side of your screen.
5. Under Criteria, verify that Dropdown is selected.
6. Enter your desired menu options into the fields (e.g., Not Started, In Progress, Completed, and Blocked). Click + Add another item to add more choices.
7. Click the small circular colour palette dot next to each option to assign distinctive chip background colours (such as Green for Completed and Red for Blocked).
8. Click Done at the bottom of the panel. Your cells now feature interactive clickable dropdown arrows.

Method 2: Create a Dynamic Dropdown from an Existing Cell Range

If your list of options changes frequently—such as an employee directory or product catalogue stored on another tab:

1. In the Data validation rules panel, set the Criteria dropdown to Dropdown (from a range).
2. Click the Select data range grid icon.
3. Highlight the column range on your source worksheet containing your master list (e.g., Lists!A2:A20) and click OK.
4. Any new product or name you type into the source list in the future will automatically populate across all dropdown menus in real time.

How to Remove a Dropdown List

To clear a dropdown rule, select the cells, open Data > Data validation, click the active rule card in the right sidebar, and click Remove rule.