Last Updated on January 10, 2024 by Jake Sheridan
How to Add a Dropdown in Google Sheets
Have you ever tried to keep lots of data organized and correctly formatted in one spreadsheet?
What about doing the same but with multiple collaborators in your sheet?
Gets messy, real quick right?
Well it doesn’t have to.
Google Sheets has some handy features to help manage your data.
One such feature is: dropdown lists.
Here, I’ll walk show you how to add a dropdown list in Google Sheets to add more order (and less chaos) to your spreadsheets.
How to create a dropdown list in Google Sheets (Quick method)
- Choose the cell where you’d like to add a dropdown list.
- Select Data >>> Data validation OR right-click >>> Dropdown.
- Choose your criteria for your dropdown menu.
Need more detail?
Here’s a step-by-step walkthrough insert a dropdown menu in Google Sheets:
To add a cell dropdown list in your spreadsheet, you must first select the target cell you want to convert into a dropdown list.
In this example, we want the user to select a meal from a list of options. We want the result to appear in cell C2.
In the Data menu, click on the Data validation option.
A panel labeled ‘Data validation rules’ will appear on the right side of your screen.
Click on the Add rule button to proceed.
Select the option ‘Dropdown’ as the main criteria type.
By default, Google Sheets will provide two placeholder options.
You can replace these options by editing the values in each textbox. Click on the ‘Add another item’ button to add more options as needed.
Click on the ‘Done’ button to apply the data validation.
Users should now be able to click on the cell to find a dropdown list to choose values from.
You may even specify different colors for different options.
The colored labels can make your dropdown list easier to navigate for users, especially if you have plenty of options or would like to categorize the options a certain way.
Besides writing down the options one by one, you may also choose to retrieve the options from an existing range.
Select the Dropdown (from a range) option and click on the textbox below.
Select the cell range that will populate the dropdown list. Hit the OK button to proceed.
Google Sheets will automatically fill out options from the cell range into the Data validation rules panel.
Your dropdown list should now have options coming from an existing range.
You may make a copy of this example spreadsheet to test it out on your own. Alright, but what if you need to update or remove your dropdown? Read on:
How to Edit or Remove Dropdown List
Editing your Google Sheets drop down is pretty easy.
Just click on the cell with your dropdown and click the edit button:
This will open up the data validation rules which you can now edit.
Removing a drop down is pretty easy too.
Again, open up the data validation rules sidebar and select Remove rule:
This will completely delete your list.
5 Ways To Use Google Sheets Drop Down Lists
Now you’ve got the basics down, let’s look at some advanced uses of drop down menus:
1. Create a Drop-Down List from a Range of Cells
Creating a drop-down list from a range of cells in Google Sheets is a great way to ensure data consistency and ease of entry.
Here’s a step-by-step guide:
Open your Google Sheets document. Navigate to the spreadsheet where you want to create the drop-down list.
Select the cell or cells where you want the drop-down list. Click on the cell or drag to select a range of cells where you want the drop-down list to appear.
In this example, we want to add a drop-down list for cells in the order_status field. The user should be able to quickly mark the order as “For Delivery”, “Completed”, “Cancelled”, and so on.
Click on Data in the menu bar. From the dropdown menu, click the Data validation option.
In the Data validation rules panel, click Add rule.
In the data validation window, under the Criteria section, choose Dropdown (from a range). Next, specify the range for the drop-down list. Click on the grid icon next to the text box under “Dropdown (from a range).” Then, select the range of cells that contain the items you want in your drop-down list. Alternatively, you can type the range directly into the box (e.g., A1:A10).
In our example, we’ll use an existing range containing each status our order could have.
You can choose to give each item its own color. This can help you quickly determine order statuses at a glance.
To change the color of a specific item, click on the circle icon to the left of the textbox with the item name. You can choose from a range of pre-selected colors or choose your own color by clicking on Customize.
Save the data validation rule by clicking Done. Now, the selected range of cells will have a drop-down arrow, showing the list of items when clicked.
2. Add a Drop-Down List via Manually Specified Options
Adding a drop-down list with manually specified options in Google Sheets is a straightforward process. Here’s how to do it:
Open your Google Sheets document. Navigate to the sheet where you want to add the drop-down list. Select the cell or range of cells where you want the drop-down list to appear. Click on the cell or drag across a range of cells where you want to implement the drop-down list.
Click Data > Data Validation to access the Data validation side panel.
In the Data validation rules panel, click Add rule to set up a new data validation rule for the selected range.
In the data validation panel, under the Criteria section, choose Dropdown.
You can now enter the list of items you want to include in your dropdown list. You can click “Add another item” to add new items and you can drag each item up or down using the provided handle on the left.
You can also give each item a designated color to provide a nice visual indicator for your range.
Click Done to implement the drop-down list. After clicking save, the selected cells will show a small arrow indicating a drop-down list. Clicking on the cell will show the options you’ve entered.
3. Copy an Existing Google Sheets Drop-Down List
Copying an existing drop-down list in Google Sheets to other cells is a useful technique for efficiently replicating data validation settings. Here’s a step-by-step guide on how to do it:
Open your Google Sheets document. Go to the sheet that contains the drop-down list you want to copy.
In this example, we want to copy the data validation in column F and use it in column G.
Select the range containing the drop-down list data validation you want to copy.
To copy the range to your clipboard, you can either use the keyboard shortcut Ctrl+C (Cmd+C on Mac) or right-click on the cell and select “Copy” from the context menu.
Right-click on the target cell where you want to copy your dropdown list to. Under Paste special, click Data validation only.
This method will apply the data validation used in the copied data to the target range.
The target range should now have the same data validation as the copied range.
Click on one of the newly pasted cells to ensure that the drop-down list appears correctly and functions as expected.
4. How to Build a Dynamic Chart in Google Sheets using a Drop-Down List
Building a dynamic chart in Google Sheets using a drop-down list involves creating a chart that updates based on the selection made in the drop-down list.
Here’s a step-by-step guide to set this up:
Open your Google Sheets document and navigate to the sheet where you want to create the dynamic chart.
In our example, we want to create a chart that tracks the daily revenue of our business. We want to make our chart dynamic by providing the user a drop-down list to select which region to show in our chart.
Create a drop-down list. Select a cell where you want the drop-down list to appear. Then, go to Data > Data validation.
In the Data validation rules panel, select “Dropdown” for the criteria and enter the items for your drop-down list.
Your selected cell should now show a drop-down list when you click on it.
Next, we’ll create a new table that uses the value chosen in our drop-down list as a condition for a filter.
In our sheet above, we used the formula =QUERY(A1:C363,”SELECT * WHERE Col2='”&E1&”‘”) to output all rows in our original range where the value in the region column is equal to the selected value in cell E1.
Select the new table and insert a chart by clicking Insert > Chart. Customize the chart type and design as needed.
You should now have a chart that uses the filtered table as a data source.
When you change the selection in the drop-down list, the filtered table should also update which in turn also updates our dynamic chart.
5. How to Change and Remove a Google Sheets Drop-Down List
Sometimes you may want to edit the items available in a drop-down list or even remove the drop-down list altogether.
Editing your Drop-Down List
Here’s how you can change the items used in a drop-down list:
First, open the document containing the drop-down list you want to edit.
In this example, we have an order tracker with a column containing the order status of each order. We want to modify the data validation of this column to include more items.
To access the data validation rules panel, click on Data > Data validation.
Alternatively, you can expand the dropdown menu and click on the Edit icon found on the lower-right corner of the menu.
We can then modify the text for each item or change the number of items altogether.
To delete an item, click on the Trash icon to the right of the item name.
To add a new item, click the Add another item button.
Click Done after you’ve finished editing the data validation rule.
In our example above, we’ve added a new item labeled “Refunded”.
If you’re editing a single dropdown menu, Google Sheets may ask if you want to apply these changes to the rest of the range.
Click Apply to all to apply the new changes to all other cells with the same dropdown rule. Otherwise, click Just this instance.
Removing your Drop-Down List
Here’s how you can remove drop-down lists in Google Sheets.
Click Data > Data validation to access the Data validation rules side panel.
You should now see a side panel listing all the data validation rules. Dropdown lists are indicated with the text “Value contains one from range” or “Value contains one from list”.
Hover over the rule you want to remove and click on the Trash icon.
You may also select the Remove all option to remove all data validation rules in the current sheet of your document.
FAQs (about drop downs & data validation)
Got questions? Here’s some frequently asked questions related to drop down lists in Google Sheets:
What is a drop down in spreadsheets?
A drop-down list is a feature in Google Sheets that lets you make a list of items from which you can choose.
What is the use of dropdown?
When you use a drop-down list to limit what can be typed in a cell, people can generally enter data faster and more accurately. When a user clicks on a cell, the down-arrow on the drop-down list shows up, and they can click it to make a choice. Adding drop-down lists to a spreadsheet helps to keep your data consistent.
What is data validation in Google Sheets?
Data Validation is a tool in Google Sheets that lets you decide what kind of information users can put in a cell or a range of cells in a sheet.
How does data validation work?
You can limit what can be typed into a form cell by using data validation rules. You can set up one or more rules to make sure that your form data is correct. Usually, you need to set up a different data validation rule for each column in your worksheet where user-entered values need to be limited.
What’s Next?Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.