The dropdown feature in Google Sheets is a helpful tool that can save you time and make your data more accurate. It’s easy to set up, and it helps prevent errors and typos. You can customize the lists to fit your needs, and they’re easy to use.
Essentially, a dropdown list is a data validation tool in Google Sheets that allows you to create a list of options for a cell. When users click on the cell, they can simply select an option from the dropdown menu that will appear on the screen. By creating a dropdown list, users can limit the available options to predefined choices, making it easier to enter data accurately and consistently.
In this article, we’ll take a closer look at some of the best practices and advanced features of dropdown menus in Google Sheets.
What are the benefits of using dropdown lists in Google Sheets?
There are many benefits of using dropdown lists. Some of these benefits include:
Accuracy: Dropdown lists in Google Sheets can help improve data accuracy by limiting the available options to predetermined choices. This eliminates the chance of errors and typos, which makes it easier to analyze the data later.
Time-Saving: Dropdown lists can save you a ton of time by providing a quick and easy way to enter data. Instead of typing in the values, all you need to do is select an option from the dropdown menu.
Consistency: With dropdown lists, you can rest assured that your data will remain consistent because users will have access to a predefined set of options to choose from.
If you want to learn how to build dropdown lists from scratch, you can also check out this article about data validation in Google Sheets. Here, we’ll take a look at some of the best practices and advanced features of dropdown menus.
In this example, let’s take a look at a spreadsheet that includes various car makes, models, and their corresponding prices.
How to keep the list up-to-date
If you select the Dropdown criterion to create your menu, you need to input all the items manually. Whenever you need to update the dropdown list, you have to open the data validation sidebar and edit the list of items.
However, if you select Dropdown (from a range), you can update the list automatically whenever you add or remove items from that range.
In this example, Google Sheets has created the dropdown list from the Cars tab, pulling data from the specified range (starting from column A2 and continuing downwards). If you add more data within that specified range, Google Sheets will automatically update the dropdown list.
How to assign colors to items
If you think your dropdown menu looks bland and unattractive, you have the option to customize it by assigning different colors to each item.
Click the circle next to the item you want to customize. Now, all you have to do is choose a color for this item’s background.
You can assign a different color to each item. Not only will this make your spreadsheet more visually appealing, but it will also facilitate data analysis and visualization.
The importance of the advanced options
When build a dropdown menu and apply data validation in Google Sheets, there are a few advanced options you can use to customize the validation criteria.
If you want to show some help text for a selected cell, check the box and write your custom message.
When you input invalid data instead of choosing an item from the dropdown menu, Google Sheets will show you a warning message.
However, you can also choose the option Reject the input if the data is invalid. Google Sheets will automatically reject the input and show a message box.
You can also customize the display style if you don’t want the dropdown chips. When you select Arrow, Google Sheets will only display a down arrow in the cell.
If you choose Plain text, the dropdown menu won’t be visible. When you double-click the cell, the menu will appear. If you start typing the text in the cell, Google Sheets will show you the available options that match your text.
How to create dependent dropdown lists
Dependent dropdown lists allow users to select options from a dropdown menu based on the selection made in a previous dropdown menu.
In other words, the choices presented in the second dropdown menu depend on what was selected in the first dropdown menu. For example, if you select Toyota, the options available in the second dropdown menu will be Toyota vehicles.
In order to build a dependent dropdown list, check out the instructions below.
1. Create the first dropdown menu
First, select Dropdown (from a range) in the Criteria section.
Select the cell where you will apply the data validation. In this example, I’ll create the dropdown menu in cell F2.
After selecting the range where you want to create the dropdown, choose the range of cells containing all the options you need to add to the list (column A, from cell A2 onwards).
2. Filter data from the first dropdown
I’ll use the FILTER function to create a formula that returns only the available options that match the first column.
FILTER(range, condition1, [condition2, …])
The range is the data to be filtered. In this example, it’s the data from column B.
The condition is the column or row containing values corresponding to the first column. It’s the data from the first column that matches the value in cell F2.
Here’s the formula: =filter(B2:B,A2:A=F2)
When I select a car make from the menu in cell F2, all the available car models from that brand will appear in column D.
3. Create the second dropdown
I’ll create another dropdown menu in cell G2, pulling data from column D.
When I choose a car make in the first menu, the second dropdown will change based on the option selected in the first dropdown list.
Now, I can create a formula using the FILTER function again to display the price.
Take your data validation to the next level
That’s how you can create a dependent dropdown menu in Google Sheets.
If you want to take data validation to the next level, you should give Sheetgo forms a try. With Sheetgo forms, you can apply data validation and create dependent dropdowns very easily with just a few clicks.
What’s more, you can make it even easier to input data in Google Sheets. You can input data in Google Sheets easily by simply filling out a form. You can share the form with your colleagues without granting them access to your spreadsheet.
Click the button below to start using Sheetgo Forms.
Sheetgo
