How to Make a Drop-Down List in Google Sheets

If several people type into the same spreadsheet, you'll soon see "Done", "done", "DONE" and "finished" in one column. A drop-down list fixes that by letting people pick from options you choose, which keeps your data clean and your filters working.

Google calls these in-cell dropdowns. I've followed its help page for the steps, including a couple of options that aren't obvious until you know they exist.

Creating a drop-down list in Google Sheets on a computer

Start by selecting the cell or cells that should hold the list. Google then gives you several ways in, and any one will do:

• Insert, then Dropdown.
• Data, then Data validation, then Add rule.
• Right-click the cell and choose Dropdown.
• Type @ in the cell and choose Dropdowns from the components section of the menu.

Then, in the Data validation rules panel, set the Criteria:

1. Choose Dropdown from a range if your options already sit in cells, then select those cells.
2. Or choose Dropdown and type your options, using Add another item for each extra one.
3. Click Done.

Turning cells that already hold data into a drop-down list

Got a column of statuses already typed out? Google says to select the cells, right-click and choose Dropdown. Sheets builds the options from what's there. If a selected cell already has a dropdown, the other values are added to that rule, and the options are ordered down columns first, then across rows.

What happens when someone types something that isn't on the list

By default, Google's help says entries that don't match the list are rejected. If you'd rather let people type something else, with a nudge, click Advanced options and, under "If the data is invalid:", select Show a warning. Then click Done.

For a team tracker, rejecting is usually best because it protects your data. A warning suits looser lists where an odd entry now and then is fine.

Changing how a Google Sheets drop-down looks

In Advanced options, Google lets you set the display style to Chip, Arrow or Plain text. Chips look like small coloured pills and are handy for status columns. The arrow keeps things plain.

If you want people to choose more than one item, Google has Allow multiple selections under the Dropdown criteria. It works only with chip-format dropdowns, you should remove leading and trailing spaces from your options, and Google says multiple selection isn't currently supported on mobile.

Editing or removing a drop-down list in Google Sheets

1. Select the cell or cells, then click Data, then Data validation (or click the dropdown's Edit button).
2. Change the options under Criteria, or the display style in Advanced options.
3. Click Done. If your list comes from a range, changes to those source cells update the list automatically.

To remove one, click Remove rule in the panel. If the cells are empty, Google also says you can select them and press Backspace, or go to Edit, Delete, then Values.

Making a drop-down list in the Google Sheets app on Android

1. Open the spreadsheet in the Sheets app and tap the cells.
2. Tap More at the top right, then Data Validation.
3. Under Criteria, choose List of items and add your entries, or List from a range and enter the cells.
4. To block anything not on the list, choose Reject input under On invalid data.
5. Tap Save.

The labels on Android differ from the computer, which is normal. I couldn't confirm iPhone and iPad steps from the page, so I'd build the list on a computer first.

Planning your Google Sheets drop-down options first

Before you open the menu, it's worth spending two minutes on the list itself. Keep each option short, so it fits in a narrow column and reads well as a chip. Pick one style of wording and stick to it, such as all lowercase or all title case, because that's what your filters will match against later. And if more than one tab will use the same options, put them in a single range on their own tab and point each drop-down at it. That way you change the wording once and every list follows, because Google says lists built from a range update when the source cells change.

Where drop-down lists save time for a small business

• Order or project status: Not started, In progress, Done.
• Lead tracking: new, contacted, replied, booked.
• Content planning: draft, ready, published.
• Team rotas: staff names pulled from a master list.

Consistent options also make filters and counts reliable. If your Sheet feeds a form or survey, my round-up of Google Forms alternatives is worth a look, and if those options end up in client paperwork, filling in a Google Docs template correctly covers the document side.

If a Google Sheets drop-down list isn't working

1. Check you have edit access.
2. Reopen Data, then Data validation and confirm the criteria and the cell range.
3. Make sure your source range hasn't been moved or deleted.
4. If an option is missing, look for stray spaces in the source cells.
5. Remove the rule and create it again if it still misbehaves.

Frequently asked questions

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

Select the cells, then go to Insert and choose Dropdown. Set the criteria to Dropdown from a range or Dropdown with your own items, then click Done.

Can I pick more than one item?

Yes, with Allow multiple selections. Google says it works only with chip-format dropdowns and isn't currently supported on mobile.

What if someone types a value that isn't on the list?

By default it's rejected. In Advanced options, choose Show a warning under If the data is invalid to allow other entries.

How do I remove a drop-down list?

Select the cells, open Data validation and click Remove rule.

Does the list update when my source cells change?

Yes. Google says changes to the source range update the list automatically.