How to Create a Drop-Down List in Google Sheets

A drop-down list in Google Sheets turns a cell into a menu, so instead of typing a value you pick one from a fixed set of choices. It keeps data tidy and consistent — everyone enters “Paid” rather than a mix of “paid”, “PAID” and “pd” — which makes filtering, sorting and formulas far more reliable. This guide explains, step by step, how to create a drop-down list in Google Sheets, how to allow more than one selection, how to colour-code and style it, and the mistakes that trip people up. Everything here was checked against Google’s own help pages and works the same whether you are in Auckland or Invercargill.

What is a drop-down list in Google Sheets?

A drop-down list is a cell setting that limits what can be entered to a list of pre-approved options shown as a small menu. Google builds this on a feature called data validation — a rule that checks whether the value in a cell is allowed. When the rule is a dropdown, Sheets displays the options as clickable “chips”, which have been part of Google Sheets since 2022 and were made easier to set up and colour in 2024.

Drop-downs are useful anywhere you want controlled input: a status column (To do / In progress / Done), a GST column (Standard-rated / Zero-rated / Exempt), staff names, regions, or product categories. Because every entry comes from the same list, your charts, filters and functions behave predictably. If you often work with spreadsheet functions, a clean dropdown column pairs naturally with the examples in our Google Sheets formulas guide.

It helps to know the difference between a dropdown and simple data validation. All dropdowns are data-validation rules, but not every validation rule is a dropdown — you can also validate that a cell holds a date, a number in a range, or a tick box. A dropdown is the validation rule most people want because it shows the allowed answers right there in the cell, so nobody has to remember them.

How to create a drop-down list in Google Sheets step by step

You create a drop-down list in Google Sheets by selecting the cells, opening the data-validation panel, choosing a Dropdown criterion and entering your options. Google gives you three ways to open that panel, and they all lead to the same place. The steps below are for Google Sheets on a computer, which is where dropdowns are easiest to build.

Method 1: Insert > Dropdown

1. Select the cell or range of cells that should contain the drop-down list.
2. Click Insert in the top menu, then choose Dropdown.
3. In the Data validation rules panel that opens on the right, leave the criterion set to Dropdown.
4. Type your first option in the field, then click Add another item for each additional choice.
5. Optionally click the coloured circle beside each item to assign it a colour.
6. Click Done. A small arrow or chip now appears in the cell.

Method 2: Data > Data validation

This is the classic route and gives you access to every option in one place.

1. Highlight the target cells.
2. Click Data, then Data validation.
3. Click Add rule.
4. Under Criteria, choose Dropdown (type the options yourself) or Dropdown from a range (point to cells that already hold the list).
5. Add or adjust your items, pick colours if you want them, then click Done.

Method 3: Right-click or the @ menu

Right-click any selected cell and choose Dropdown to jump straight into the same panel. Alternatively, type @ in an empty cell and select Dropdowns from the list of smart components — handy if you prefer the keyboard. Google also offers preset dropdowns (for example Priority or Review status) that come pre-coloured, so you can insert a ready-made list and tweak the wording afterwards.

Dropdown versus Dropdown from a range: which should you use?

Choose Dropdown when your options are short and rarely change, and Dropdown from a range when the list is long or needs to stay editable. The difference is where the options live.

• Dropdown: you type each option directly into the rule. Fast to set up, but to change the list you must reopen the data-validation panel.
• Dropdown from a range: you first type the options into cells (for example Z2:Z10, perhaps on a separate “Lists” tab), then tell the rule to read that range. Edit a cell and every dropdown using that range updates automatically.

For anything that grows over time — a client list, a product catalogue, team members — the range method saves work. Keep the source list on its own hidden sheet so nobody deletes it by accident.

A practical tip for the range method: name your options range or leave a few blank cells at the bottom of it, so new items you type later are picked up automatically. If you point the rule only at the exact cells that currently hold data, a sixth option added below the fifth may sit outside the range and never appear in the menu. Giving yourself a little headroom — say Lists!A2:A50 for a list that starts with ten items — avoids editing the rule every time the list grows.

How to allow multiple selections in a drop-down list

Google Sheets can now let a single cell hold more than one choice, a feature it added natively in the week of 26 July 2024. Before that you needed a script; today it is a checkbox.

1. Open the dropdown rule through Data > Data validation (or create a new one).
2. Set the criterion to Dropdown or Dropdown from a range and enter your items.
3. Tick Allow multiple selections.
4. Click Done. You can now click several chips in the same cell — useful when one row applies to multiple teams, tags or milestones.

Two limits to know: multiple selections only work with the Chip display style, and your options should not contain leading or trailing spaces. Multi-select is also a desktop feature — the extra picks do not appear when you tap the cell on the Android or iPhone app.

How to colour-code and style your drop-down list

You can give each option its own colour and choose how the dropdown looks, which turns a plain list into an at-a-glance status board. Colours are set per item in the data-validation panel — click the circle next to an option and pick a shade.

Under Advanced options you can also change the Display style:

• Chip: a rounded, coloured pill — the default, and the only style that supports multiple selections.
• Arrow: a plain cell with a small triangle on the right, closer to the older Sheets look.
• Plain text: no visible control until you click the cell — tidy for printing or dense tables.

Colour-coded chips work well in planners and trackers; if you are building a schedule, the same idea sits nicely alongside a free Google Sheets calendar template, and a dark background can make bright chips pop — see our guide to Google Sheets dark mode.

How to edit, copy or remove a drop-down list

To change a drop-down list, select the cell, open Data > Data validation, click the existing rule and edit the items, colours or criterion, then click Done. To copy a dropdown to other cells, copy the cell and use Paste special > Values and formatting, or simply drag the small fill handle at the cell’s corner across the column.

To remove a drop-down list completely, open the same Data validation panel, hover over the rule and click Remove rule. That strips the menu but leaves any value already in the cell as plain text. If you also want to clear the contents, select the cells and press Delete (or use Edit > Delete > Values).

How do you make a dependent (conditional) drop-down list?

Google Sheets has no built-in “dependent dropdown” where the second list changes based on the first, so you build one with a helper formula. The idea is that choosing a category in one cell narrows the options shown in the next. A common approach uses the FILTER function to pull a shorter list into a helper range, then points a “Dropdown from a range” rule at that range.

1. Set up a lookup table with two columns, for example Island in column A (North / South) and Region in column B.
2. Create a normal dropdown in your first cell for the Island choice.
3. In a spare area, enter a formula such as =FILTER(B2:B, A2:A=D2), where D2 holds the Island the user picked. This returns only the matching regions.
4. Make a second dropdown using Dropdown from a range that points at the cells the FILTER formula fills.

It takes a little setup, but the payoff is a tidy, guided form where each answer limits the next. If formulas are new to you, start with the basics in the formulas guide linked earlier and build up from there.

What happens when someone enters invalid data?

By default, Google Sheets rejects anything that is not on the list. You control this under Advanced options, in the section headed If the data is invalid:

• Reject the input (default): typing a value that is not an option is bounced, and the user must pick from the menu. Best for strict data entry where consistency matters most.
• Show a warning: the off-list value is kept but flagged with a small red triangle in the corner of the cell. Useful when you want guidance without blocking the occasional exception.

For shared spreadsheets — rosters, stock sheets, a shared timesheet template — “Reject the input” is usually safest, because it stops typos before they reach your formulas.

Can you create drop-down lists on mobile?

You can use existing drop-down lists in the Google Sheets app on Android and iPhone, and you can create basic ones, but the experience is more limited than on a computer. Tap a cell that already has a dropdown and the options appear for you to choose from. To build one, tap the cell, open the three-dot or toolbar menu and look for Data validation.

The key limitation is that multiple selections do not work on mobile even if the rule allows them — you will only be able to pick one chip. For anything beyond a quick single-choice list, set the dropdown up on a desktop and let your phone handle day-to-day data entry.

Google Sheets dropdowns versus Excel and LibreOffice Calc

Every major spreadsheet app can make a drop-down list from a validation rule, but the menus and feature names differ. Google Sheets is the most beginner-friendly for coloured chips and multi-select; Microsoft Excel offers deep validation controls; and the free, offline LibreOffice Calc covers the essentials without a subscription. The table below compares how each one handles drop-down lists so you can match the tool to the job.

If you are weighing up which spreadsheet to standardise on, it is worth knowing that a dropdown built in Sheets can survive a round trip to other formats — though complex validation rules sometimes drop when you export. Google Sheets also talks to the rest of Workspace, so a dropdown column can feed straight from a Google Form you have people fill in.

One practical difference for New Zealand users: if you share a sheet with someone who opens it in Excel or LibreOffice Calc, a single-select Google dropdown usually converts to a plain validation list, but the multi-select chips and their colours may not carry across cleanly. If cross-app sharing matters, keep dropdowns single-select and test a sample file before you rely on it.

Common mistakes to avoid

Most drop-down problems come from a handful of avoidable errors. Watch for these before you share your sheet:

• Deleting the source range: if you used “Dropdown from a range” and later delete or move those cells, the list breaks. Keep source lists on a dedicated, clearly labelled tab.
• Stray spaces in options: a trailing space makes “Done ” and “Done” count as different values and can stop multi-select working. Trim your list before you paste it.
• Expecting multi-select on mobile: it is desktop-only, so a phone user will silently be limited to one choice.
• Using “Show a warning” when you need strict data: warnings let bad values through. For reporting columns, choose “Reject the input”.
• Relying on dropdowns for security: data validation guides input, it does not lock a cell. To stop edits you must also use Data > Protect sheets and ranges.
• Case-sensitive formulas: if a VLOOKUP or FILTER fails, check that your dropdown text exactly matches what the formula expects.

Practical examples of drop-down lists

Drop-down lists earn their place fastest in columns that repeat the same handful of answers. Here are everyday setups that work well for New Zealand households, students and small businesses:

• Invoice tracker: a status column with Draft, Sent, Paid and Overdue, colour-coded so overdue rows jump out at a glance.
• GST category: Standard-rated (15%), Zero-rated and Exempt, so each line is classified consistently for your return.
• Team roster: staff names pulled from a “People” tab via Dropdown from a range, updated in one place when someone joins or leaves.
• Project board: a multi-select cell tagging each task with the teams involved — Design, Build, Review — using the Chip style.
• Stock or inventory: a supplier or location list that stops typos from fragmenting your reports.

In each case the dropdown is doing the same job: trading a free-text field, where anything can be typed, for a short menu that keeps the whole column clean enough to sort, filter and total reliably.

Who are drop-down lists best for?

Drop-down lists suit anyone who collects repeatable, categorised data and wants it consistent. Small-business owners use them for invoice status and GST categories; teachers for marking or attendance; project teams for task stages; and households for budgeting categories. They are especially valuable when more than one person edits the same sheet, because they remove the guesswork from what to type. If your spreadsheet is a simple one-off calculation, a dropdown may be overkill — but for any living list that others touch, it is one of the quickest ways to keep your data clean.

Frequently Asked Questions (FAQ)

How do I add a drop-down list in Google Sheets quickly?

Select the cells, click Insert then Dropdown, type your options into the panel on the right (clicking Add another item for each one), and click Done. The whole process takes under a minute for a short list.

Can a Google Sheets drop-down allow more than one selection?

Yes. Open the rule through Data then Data validation, tick Allow multiple selections and click Done. It only works with the Chip display style and only on a computer, not in the mobile apps.

How do I remove a drop-down list but keep the value in the cell?

Select the cell, open Data then Data validation, hover over the rule and click Remove rule. The menu disappears but whatever was already chosen stays in the cell as plain text.

Why is my drop-down list not showing the arrow or chip?

Check the Display style under Advanced options. If it is set to Plain text, the control only appears when you click the cell. Switching it to Chip or Arrow makes the dropdown visible at all times.

Can I link a drop-down list to a list on another sheet?

Yes. Choose Dropdown from a range and point to the cells holding your options, even on a different tab (for example Lists!A2:A20). Editing those cells updates every dropdown that uses the range.