So you're trying to keep heaps of data organized and correctly formatted in one spreadsheet with multiple collaborators—what could possibly go wrong?
Yeah.
If you use Google Sheets to manage your data, there are some features that can help with data consistency and accuracy. One of those features: dropdown lists.
An in-cell dropdown includes a list of values, such as numbers or categories, which you can select from to add or organize data. You can create a dropdown list in Google Sheets by entering preset values or using existing data. I'll walk through how to add a dropdown list using both methods so you can add more clarity and categorization to your spreadsheets.
If you're just looking for a cheat sheet, here's the short version of how to add a dropdown list in Google Sheets. (Keep scrolling for a more detailed breakdown.)
Select the cell or cells where you'd like to add a dropdown list.
In the top menu bar, click Data, then select Data validation. Or you can right-click your desired cell and select Dropdown.
Choose the criteria for your dropdown menu, and add your options.
How to create and add a dropdown list in Google Sheets with preset values
Let's say you use Google Sheets to track lead data, like names and locations. In this example, your leads are based only in four locations: New York, Los Angeles, Chicago, and Houston. You can add a dropdown list with only these cities, making it that much faster to keep track of everyone.
Open your spreadsheet.
Select the cell or cells where you'd like to add a dropdown menu. In the example below, I want to add a dropdown list beside each lead to capture their location.
In the top menu bar, click Data. Then select Data validation.
Alternatively, you can right-click the cell you want to add a dropdown to and select Dropdown.
In the Data validation rules panel, enter your dropdown options as separate values and assign each one a unique color. To add more options to your list, click Add another item.
Click Done.
Now, in those cells, you'll only be able to select from one of the options in the dropdown list—Google Sheets won't let you type anything "invalid" in there. That's why they call it data validation.
How to create and add a dropdown list in Google Sheets with existing data
Let's say you already entered the options you want to use for your dropdown directly in your spreadsheet. Here's the easiest way to create a dropdown list in Google Sheets with those existing values.
Select the cells with the values you want to include in your dropdown list.
Right-click your highlighted cells, and then select Dropdown.
The value of the cells you highlighted will automatically populate as unique dropdown options in the Data validation rules panel. By default, each option is assigned a light gray color. I recommend changing this to make it easier to distinguish between each option.
Once you've modified your options, click Done.
How to edit or remove a dropdown list in Google Sheets
Editing a dropdown list is just as straightforward as creating one.
Click the cell with the dropdown that needs to be updated.
Click the Edit icon, which looks like a pencil.
In the Data validation rules panel, make your changes to the dropdown list. To delete the dropdown entirely, click Remove rule.
Click Done.
How to use your new dropdown list
Using a dropdown list in Google Sheets isn't data science (though it can help with it). Just click the arrow that populates next to the shaded bubble in the cell with your list, and choose an option to populate the cell.
More often than not, you'll want to use your dropdown list for an entire row or column of cells. To do this, you can either drag the bottom-right corner of the cell with your dropdown menu to the cells you want to populate with the same list, or copy and paste the contents of the cell into other cells.
Why add a dropdown list in Google Sheets?
You can select an option from a dropdown list without doing any typing, which reduces the likelihood of entering a typo or incorrect info. If you like being at the mercy of human error, I can't stop you, but I will cringe.
And it's not just about accuracy—it also saves time, especially if you're collaborating on a sheet and want to ensure everyone involved has access to the same inputs.
Plus, it opens up a lot more options for how you can put Google Sheets to use. You can use dropdown lists to create forms for things like data collection, inventory and project management, surveys, time tracking, and expense reports. They'll be easier to create—and a lot prettier.
Google Sheets dropdown list FAQ
Looking for more tips on using Google Sheets dropdown lists? Turns out there's a decent amount of nuance to these tiny things.
How do I create a dropdown list in Google Sheets with multiple selections?
To allow multiple selections in your dropdown lists, go to Data > Data validation > Add rule, and select Allow multiple selections.
How do I add categories to a dropdown list in Google Sheets?
If you need more categories in a dropdown list, go to Data > Data validation, select the rule associated with your list, then click Add another item.
How do I autofill a dropdown list in Google Sheets?
To autofill a dropdown list, select the range of cells with the values you want to include in your list. Right-click the cells and select Dropdown. You can see a step-by-step guide with screenshots here.
Automate Google Sheets
Dropdown lists are just one way to reduce the chance of human error in Google Sheets. Another tactic: automate data entry. Use Zapier to connect Google Sheets to your other apps, so you can automatically import lead data, form submissions, or anything else from your tech stack. Learn more about how to automate Google Sheets, or get started with one of these pre-built workflows.
Save new Gmail emails matching certain traits to a Google Spreadsheet
Add new Facebook Lead Ads leads to rows on Google Sheets
Send emails via Gmail when Google Sheets rows are updated
Generate rows in Google Sheets for new Unbounce submissions
Zapier is the leader in workflow automation—integrating with thousands of apps from partners like Google, Salesforce, and Microsoft. Use interfaces, data tables, and logic to build secure, automated systems for your business-critical workflows across your organization's technology stack. Learn more.
Related reading:
This article was originally published in March 2023 by Luke Strauss. The most recent update, with contributions from Dylan Reber, was in September 2024.
