How to add a drop down list in Google Sheets

A drop down list in Google Sheets lets people pick a value from a set of options instead of typing it. Entries stay consistent, and typos can’t creep in.

A dropdown is a data validation rule: it limits a cell to the options you define and shows them as a menu. The steps below show how to add one. Further down, you’ll see how to fill it from a range, color-code it, edit, copy, or remove it, and build a dependent dropdown.

How to add a drop down list in Google Sheets

To insert a dropdown, you need the cells and the options you want people to pick from.

1. Select the cell or range where you want the dropdown.
2. Go to Insert > Dropdown. You can also right-click the cells and choose Dropdown, or go to Data > Data validation and click Add rule.
3. In the Data validation rules sidebar, leave Criteria set to Dropdown.
4. Type each option in its own field.
5. Click Done.

The cell now shows a chip with an arrow. Click it and pick an option. To create a dropdown that reads its options from cells in your sheet, use Dropdown (from a range), explained below.

What are the benefits of using dropdown lists in Google Sheets?

There are many benefits of using dropdown lists. Some of these benefits include:

Accuracy: Dropdown lists in Google Sheets can help improve data accuracy by limiting the available options to predetermined choices. This eliminates the chance of errors and typos, which makes it easier to analyze the data later.

Time-Saving: Dropdown lists can save you a ton of time by providing a quick and easy way to enter data. Instead of typing in the values, all you need to do is select an option from the dropdown menu.

Consistency: With dropdown lists, you can rest assured that your data will remain consistent because users will have access to a predefined set of options to choose from.

In this example, let’s take a look at a spreadsheet that includes various car makes, models, and their corresponding prices.

How to create a dropdown from a range

Pick the criterion by how often the list changes. For a few fixed options, such as To do, In progress, and Done, the Dropdown criterion with typed items works well. The catch is that every change means opening the data validation sidebar and editing the items by hand.

However, if you select Dropdown (from a range), you can update the list automatically whenever you add or remove items from that range.

In this example, Google Sheets has created the dropdown list from the Cars tab, pulling data from the specified range (starting from column A2 and continuing downwards). If you add more data within that specified range, Google Sheets will automatically update the dropdown list.

The options appear in the same order as the cells in the source range. To reorder them, sort the range, or point the dropdown at a sorted copy built with the SORT function. In a typed list, drag the items by the handle on their left in the sidebar.

How to color-code a drop down list

If you think your dropdown menu looks bland and unattractive, you have the option to customize it by assigning different colors to each item.

Click the circle next to the item you want to customize. Now, all you have to do is choose a color for this item’s background.

You can assign a different color to each item, which makes the values easier to scan when you review or sort the data.

Chip colors replace the older method of writing a conditional formatting rule for each option. Use Format > Conditional formatting only when the dropdown value should color a whole row, as shown in this guide to conditional formatting based on another cell.

Advanced dropdown options: help text, invalid data, and display style

When you build a dropdown menu and apply data validation in Google Sheets, there are a few advanced options you can use to customize the validation criteria.

If you want to show some help text for a selected cell, check the box and write your custom message.

When you input invalid data instead of choosing an item from the dropdown menu, Google Sheets will show you a warning message.

However, you can also choose the option Reject the input if the data is invalid. Google Sheets will automatically reject the input and show a message box.

You can also customize the display style if you don’t want the dropdown chips. When you select Arrow, Google Sheets will only display a down arrow in the cell.

If you choose Plain text, the dropdown menu won’t be visible. When you double-click the cell, the menu will appear. If you start typing the text in the cell, Google Sheets will show you the available options that match your text.

How to edit a drop down list

Select a cell with the dropdown, go to Data > Data validation, and click the rule in the sidebar. You can also open the dropdown and click the pencil icon at the bottom of the list.

In the sidebar, change the cells under Apply to range, add or delete items, or drag them into a new order. Click Done to save the changes.

How to copy a drop down list

Copy the cell with the dropdown and paste it into another cell. The rule and its options come with it.

To paste only the dropdown and keep the formatting of the target cells, use Edit > Paste special > Data validation only. To copy it down a column, drag the fill handle in the bottom-right corner of the cell.

How to remove a drop down list

Select the cells with the dropdown and go to Data > Data validation. Click the rule, then click Remove rule. The values already in the cells stay where they are; only the dropdown goes away.

How to create a dependent drop down list

A dependent drop down list, also called a conditional dropdown, shows options based on the value picked in another dropdown.

In other words, the choices presented in the second dropdown menu depend on what was selected in the first dropdown menu. For example, if you select Toyota, the options available in the second dropdown menu will be Toyota vehicles.

In order to build a dependent dropdown list, check out the instructions below.

1. Create the first dropdown menu

First, select Dropdown (from a range) in the Criteria section.

Select the cell where you will apply the data validation. In this example, I’ll create the dropdown menu in cell F2.

After selecting the range where you want to create the dropdown, choose the range of cells containing all the options you need to add to the list (column A, from cell A2 onwards).

2. Filter data from the first dropdown

I’ll use the FILTER function to create a formula that returns only the available options that match the first column.

The range is the data to be filtered. In this example, it’s the data from column B. 

The condition is the column or row containing values corresponding to the first column. It’s the data from the first column that matches the value in cell F2.

Here’s the formula: =filter(B2:B,A2:A=F2)

When I select a car make from the menu in cell F2, all the available car models from that brand will appear in column D.

3. Create the second dropdown

I’ll create another dropdown menu in cell G2, pulling data from column D.

When I choose a car make in the first menu, the second dropdown will change based on the option selected in the first dropdown list.

Now, I can create a formula using the FILTER function again to display the price.

Take your data validation to the next level

That’s how you can create a dependent dropdown menu in Google Sheets. 

When several people enter data into the same spreadsheet, try Sheetgo Forms. You build a form with the same checks, dependent dropdowns included, and people fill it out without needing access to your spreadsheet.

Each response lands as a row in Google Sheets. Sheetgo then moves those rows into the reports and files your team uses, so the data stays clean and AI-ready from the first entry.