Creating a dropdown list in Google Sheets

A dropdown list in Google Sheets lets you click a cell and choose from preset options instead of typing. To add one, select the cell or range where you want the dropdown, open the Data menu, choose Data Validation, then pick "List of items" or "List from a range" depending on where your options live. Type or select your choices, click Done, and the dropdown appears.

Dropdowns work best when you have a fixed set of answers — like status values (Pending, Approved, Rejected), department names, or yes/no choices. They prevent typos, keep data consistent across your sheet, and make it faster to fill in repetitive information. Anyone viewing or editing the sheet can use the dropdown without knowing how it was built.

Key Takeaways

  • Select the cell or range, go to Data > Data Validation, and choose "List of items" to type your options directly into the validation rule.
  • Use "List from a range" if your options already exist in another part of the sheet, so changes to that list update the dropdown automatically.
  • Dropdowns prevent spelling mistakes and keep everyone entering data the same way, which matters if you plan to sort or filter later.
  • You can set a dropdown to show an error message if someone tries to enter something not on the list, or allow them to ignore the rule.

Typing options directly into the validation rule

The simplest dropdown uses "List of items". Select your cell or cells, open Data > Data Validation, choose "List of items" from the dropdown menu, then type your options separated by commas. For example, type Active, Inactive, On Hold and click Done. Each option becomes a choice in the cell's dropdown arrow.

This method works well for short lists that rarely change. If you need to edit the options later, you go back into Data Validation and update the text. The downside is that if you use the same list in multiple places on your sheet, you have to update each one separately.

Pulling options from cells elsewhere in your sheet

If your options already exist in a column or row — say, a list of team members in column B or product names in rows 1 through 10 — use "List from a range" instead. Select your data validation cell, go to Data > Data Validation, choose "List from a range", then click the range icon and select the cells containing your options. Click Done.

This approach saves work because the dropdown automatically updates if you add or remove items from the source list. If you add a new team member to column B, that name appears in every dropdown that pulls from column B without you having to edit the validation rules. This is especially useful for shared sheets where multiple people might update the source list.

Controlling what happens when someone enters the wrong value

By default, Google Sheets shows a warning if someone types something not on your dropdown list, but lets them enter it anyway. If you want to be stricter, open Data Validation again, find the "If invalid" dropdown at the bottom, and choose "Reject input". Now anyone who tries to type a value not on the list gets an error and cannot save the cell until they pick from the dropdown.

You can also add a custom error message so people know why their entry was rejected. In the same Data Validation window, check "Show validation help text" and type something like "Please choose from the list provided." This message appears when someone hovers over the cell or tries to enter an invalid value.

Dropdowns across multiple cells at once

You do not have to add a dropdown to one cell at a time. Select a range — for example, cells A2 through A50 — then open Data > Data Validation and set up your list. The same dropdown applies to every cell in that range. This is much faster than clicking each cell individually, and it ensures consistency across your sheet.

If you later realize you need the dropdown in more cells, select those cells and repeat the process. You can also copy a cell with a dropdown and paste it to other cells; Google Sheets copies the validation rule along with any formatting.

Using dropdowns with conditional formatting or filters

Dropdowns pair well with other Google Sheets features. If you use filters on your data, the filter menu shows only the values that actually appear in that column, which usually matches your dropdown list. If you set up conditional formatting — like highlighting cells based on their value — the dropdown ensures only the values you planned for will appear, so your formatting rules work as expected.

For example, if your dropdown contains "On Time", "Late", and "Pending", and you set conditional formatting to color "Late" entries red, you know every entry in that column will be one of those three values. No surprises from typos or unexpected entries.

Frequently Asked Questions

Can I make a dropdown that shows different options based on what is in another cell?

Google Sheets does not have built-in dependent dropdowns, but you can approximate this with careful use of "List from a range". Create separate lists for each category in different areas of your sheet, then use a formula-based approach or manually update the range reference. For complex conditional dropdowns, you may need to use Google Forms or a third-party add-on instead.

What if I want to let people type values that are not on the dropdown list?

Set Data Validation to "Show warning" instead of "Reject input". This displays a message but allows the entry. The person sees the warning and can choose to proceed anyway. This is useful when you want to suggest common values but not lock people into them.

How do I remove a dropdown from a cell?

Select the cell, open Data > Data Validation, and click the trash icon in the bottom right corner. The validation rule is deleted and the dropdown disappears. If you want to remove it from many cells at once, select the range first, then delete the validation rule.

Can I copy a dropdown to a different sheet in the same file?

Yes. Copy the cell with the dropdown, switch to the other sheet, select where you want it, and paste. Google Sheets copies the validation rule. If the dropdown uses "List from a range" that references the original sheet, the reference updates automatically to point to the correct sheet.

Does the dropdown work the same way on mobile as on desktop?

Dropdowns work on mobile, but the interface is slightly different. On a phone or tablet, tapping a cell with a dropdown shows a list of options to choose from. The functionality is the same, though the visual presentation is adapted for a smaller screen.