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. Here, I’ll walk through how to add a dropdown list using both methods so you can add more clarity and categorization to your spreadsheets.
How to create and add a dropdown list in Google Sheets
If you’re looking for a quick refresher, here’s the short version of how to create 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 grey 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.
Automate Google Sheets
Sure, you can use Google Sheets to manually track your lead data. Or you can automate the process. How? Use Zapier to connect Google Sheets to your other lead source apps, like Facebook and Google Ads.
And if you don’t use Google Sheets to track leads, there are countless data-related tasks you can automate with Zapier. Here are a few workflows to get you started.
To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.
Related reading:
This article was originally published in March 2023 by Luke Strauss. The most recent update was in September 2023.
