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.
Here, I'll walk through how to add a dropdown list in Google Sheets, so you can add more clarity and categorization to your spreadsheets.
How to create a dropdown list in Google Sheets, step-by-step
I try to avoid clichés, but creating a dropdown list truly is as easy as 1-2-3:
Select the cell where you'd like to add a dropdown menu.
In the top menu bar, select Data > Data validation (or right-click and select Dropdown).
Choose the criteria for your dropdown menu, and add your options.
Here's a more detailed breakdown of this process.
1. Select the cell where you'd like to add a dropdown menu
After opening your spreadsheet, select the cell where you'd like to add a dropdown menu. For example, this could be adjacent to data you want to categorize.
2. In the top menu bar, select Data > Data validation (or right-click and select Dropdown)
There are a couple ways to pull up the Data validation rules sidebar and create a dropdown. The first (and older) way is to click Data in the top menu bar and select Data validation.
Alternatively, you can simply right-click on the cell you selected and click Dropdown to open the sidebar.
3. Choose the criteria for your dropdown menu and add your options
If you want to manually enter the options your dropdown menu will populate, you can leave the menu criteria alone—it will default to Dropdown. Type the title and select a color for each option. You can create more fields with the Add another item button.
If you have your options listed in a range of cells somewhere else in your spreadsheet, you can change the criteria to Dropdown (from a range) and define your range.
Click Done to save your dropdown and begin using it.
Now, in those cells, you'll only be able to select from one of the options—Google Sheets won't let you type anything "invalid" in there. That's why they call it data validation.
How to edit or remove a dropdown list
Editing a dropdown should feel familiar—it's nearly the exact same process as creating one.
Reopen the Data validation rules sidebar, and then select the rule you created for your dropdown.
Alternatively, you can click on the dropdown itself and select the Edit button (represented by a pencil icon). This will take you directly to your dropdown's customization interface in the Data validation rules sidebar.
Then, simply make your changes to the dropdown list. To delete the list entirely, click Remove rule at the bottom of the sidebar.
How to use your new dropdown list
Using a Google Sheets dropdown list 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.
Spreadsheet apps can get pretty technical, but Google Sheets makes adding a dropdown list easy enough. Looking for more ways to speed up and organize your data workflows? Explore how you can automate Google Sheets with Zapier.
Related reading:
