How To Create A Google Sheets Drop Down Menu

In this post, we’ll look at how to create a Google Sheets Drop Down Menu.

Google Sheets Drop Down Menu

Drop down menus are great for doing better 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.

Step 1: Create the data validation

Select the cell you want your drop down menu to be in.

Then go to the menu: Data > Data validation

Google Sheets Drop Down Data Validation

Step 2: Add the items

For drop down menus you need to choose either

  1. List of items, or
  2. List from a range

as the Criteria.

List of items

The “List of items” is the simpler one. You simply enter the values as you want them to appear in your list, separated by commas.

For example, here I’m creating a drop down that user could use to select their communication preference: Email, Phone, Text, Mail

Google Sheets Drop Down List Of Items

List 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 “List 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 them into the data validation as a list of items will be a huge pain in the backside, especially when it comes to making changes.

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

Google Sheets Drop Down List From A Range

You can either enter the range as a formula

=A1:A50

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

select Data Range

Step 3: Create the drop down

Make sure the checkbox “Show dropdown list in cell” is checked.

Google Sheets Drop Down Save

Then click “Save” to add the drop down to your cell.

Note: at first the cell will be empty, but you’ll see a triangle option in the corner. Click that to open the drop down and select an item.

Step 4: Resuse

Luckily 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 grade 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:

Google Sheets Drop Down Menu

And here’s a similar example of a course results feedback system that sends data from Google Sheets to Slack, that uses a drop down list for data entry:

Send data from Google Sheets to Slack

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.

Categorizing transactions in Google Sheets with Tiller

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.

Google Sheets Drop Down Dynamic Chart

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:

Dynamic dashboard in Google Sheet
Dynamic dashboard using a Google Sheets drop down technique

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 itmes are shown in the second drop down.

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

Leave a Reply

Your email address will not be published.