How To Create A Google Sheets Drop Down Menu - Ben Collins

In this post, we’ll look at how to create a Google Sheets Drop-Down Menu. Here’s an example of a drop-down menu to record the status of deals in a real estate deal pipeline:

Drop-down menus are great for data entry and making your Sheets dynamic.

In this post, we’ll explore both of these techniques with examples.

But first, let’s see how to create a Google Sheets drop-down menu.

How To Create A Drop-Down List In Google Sheets

It only takes a few steps to create a drop-down list in Google Sheets, using the Data Validation tool.

Step 1: Open Data Validation

Select the cell where you want to put a drop-down menu.

Then go to the menu: Data > Data validation

Note: you can also add a data validation rule by right-clicking on the cell, then choose:

View more cell actions > Data validation

Step 2: Add Drop-Down Options

From the data validation rules menu, select +Add rule

Then, choose either Dropdown or Dropdown (from a range) under the Criteria selection.

Dropdown

The Dropdown setup is the simpler one. You simply enter the values as you want them to appear in your list.

Here is the default drop-down setup that shows Option 1 and Option 2.

You can rename each option, change the color of each option, rearrange the order (by clicking and dragging on the dots to the left of each option), or add another item.

You can also enable multiple selections in drop downs, by toggling the checkbox under the options:

You’ll also notice the Advanced options button, which is discussed in detail below.

When you’re ready, click Done to add the drop-down menu to your cell.

Dropdown (from a range)

Typing a long list of items is impractical and error-prone, so if you have more than a handful of items, I recommend using the Dropdown (from a range) criteria option.

In this case, you create a list of all the possible items somewhere in your Google Sheet and link to that.

For example, suppose we want users to select which state they’re based in.

Leaving this as a free entry input cell would be a bad idea because we’ll get a huge number of variations (e.g. “CA”, “Cali”, “California”, etc.) which will all be interpreted differently by the Sheet.

Equally, typing all 50 states into the data validation as a list of items is inefficient.

A better option is to make a list of the items in your Sheet and link to that, as shown in this image:

You can either enter the range as a formula, e.g.

=A1:A50

Or click the grid in the input box, which opens a second popup that lets you select the range with your mouse:

Click OK once you’ve selected the range for the drop-down items.

Then click Done to add the drop-down menu to your cell.

Step 3: Advanced Options

The Advanced options button lets you customize the drop-down menu:

Specifically:

• Show help text for a selected cell lets you specify a note to help users when selecting from the drop-down.

• The If the data is invalid option is useful to warn users if the value they are trying to enter differs from the options in the drop-down menu.

For example, if your drop-down selection is Blue or Red, but a user types Green into that cell, then you can either show a warning message or outright reject the input.

• The Display style option determines how the drop-down is presented in your Sheet.

The Chip option looks like this:

The Arrow option like this:

And the Plain text like this:

To access the menu with the Plain text, double-click on the cell.

Note: the colors of the drop-down cells come from the colors specified in the list of items, above the Advanced options section.

Step 4: Reuse

Thankfully you don’t have to re-enter the list for each new cell you want to use the drop-down in.

Simply copy the cell with the drop-down and paste it elsewhere. The drop-down menu will be available in that cell too.

Drop Down Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

You can also read about drop downs in the Google Documentation.

Google Sheets Drop Down Examples

Better User Input With Drop-Down Lists

The classic use case for a drop-down list in Google Sheets is to create a pre-defined set of inputs that users simply select when they’re entering data.

This is known as data validation (hence why we build drop-downs through the data validation menu).

Consider this example: suppose you’re collecting data about student grades and you require teachers to enter what grade a student belongs to.

You’ll get a mix of answers: 5, 5th, 5th Grade, Fifth, Fifth Grade, etc.

This causes problems because Google Sheets treats all of these as different answers in formulas or pivot tables. Before you can use this data, you’ll have to standardize it so that they’re all recorded as e.g. “5th Grade”.

But if you set your column up with a drop-down input, with choices “1st Grade”, “2nd Grade”, “3rd Grade”, etc., then you’ll avoid this problem altogether and save yourself tons of time.

Here’s how that column would look, using the Arrow display style:

I also use drop-down lists in the Google Sheet my wife and I use to track our spending habits.

The drop-down is a list of transaction categories (e.g. Food, Gas, Insurance, Health, Gifts, etc.). You can imagine how much easier, quicker, and more reliable this system is versus having a freeform input field.

Dynamic Charts Using Drop-Down Menus In Google Sheets

Along with checkboxes and slicers, it’s a great way to add interactivity to your Sheets and let your users control the data.

Dynamic charts really enhance reports and dashboards, allowing for more information to be conveyed in the same amount of screen space.

You can create dynamic charts by combining a drop-down menu in Google Sheets with lookup formulas. When the Google Sheets drop-down value changes, the formulas update their values, and then the chart updates in turn too.

See this article on dynamic charts in Google Sheets.

Dashboards Using Drop-Down List In Google Sheets

Consider this example of a dynamic dashboard, which shows a drop down list in Google Sheets to control the data displayed in the chart:

It lets users explore the data on their own terms and change what they’re seeing.

As you can see, the user is shown a choice of options that control some action downstream.

In this case, the drop-down list lets the user select a parameter (sales channel or time period) and updates the charts based on this choice automatically.

It makes the dashboard really pop! It lets the viewer explore the data in their own way.

Advanced Dependent Drop-Down Menus

It’s possible to combine two (or more) drop-downs that are conditional upon each other. I.e. the selection in the first drop down determines what items are shown in the second drop down.

Learn how to create dependent drop downs on Day 4 of my free course Advanced Formulas 30 Day Challenge.