In this post, we’ll look at how to create a 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
Step 2: Add the items
For drop down menus you need to choose either
- List of items, or
- 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
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:
You can either enter the range as a formula
Or click the grid in the input box, which opens a second popup which lets you select the range with your mouse:
Step 3: Create the drop down
Make sure the checkbox “Show dropdown list in cell” is checked.
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
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:
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:
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
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 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.