Insert a Dropdown List on Google Sheets

Have you ever wondered how to ensure the validity of data entered on Google Sheets? Well, data validation is a fundamental feature that allows you to validate the data entered and display an error message or reject the input if it doesn’t meet the criteria. But what if you have a long list of possible answers? That’s where dropdown lists come in handy for better readability.

Creating a Dropdown Menu

Dropdown lists on Google Sheets enable users to quickly select an item from a list of pre-filled options or a range of data. They are particularly useful for creating questionnaires or multiple-choice forms. There are two ways to create a dropdown list on Google Sheets, depending on whether you want to validate the data based on a range or a list.

Validating Data Based on a Range

Creating a multiple-choice dropdown list on Google Sheets from a range is useful when you have a large number of possible answers. Here are the steps to follow:

1. Open a Google Sheets page.
2. Select the cell(s) where you want to create the dropdown list.
3. Create a reference list.
4. Select the range of cells where you want to enforce this input.
5. Right-click and select “Data validation”. You can also click on “Data” in the ribbon and select “Data validation”.

In the criteria window that appears, choose “List from a range”. Select the range of cells that contains the list of valid data you have prepared. Check the box “Show dropdown list in cell” and click “Save”.

Validating Data Based on a List

Unlike validating data based on a range, this method allows you to directly list the possible choices in the “Criteria” field of “Data validation”. Here’s how to insert a dropdown list based on a list:

1. Open a Google Sheets page.
2. Select the cell(s) where you want to create the dropdown list.
3. Right-click and select “Data validation”.
4. In the criteria, choose “List of items”.
5. Specify the proposed answers, separating them with commas.
6. Check the box “Show dropdown list in cell”.
7. Click “Save”.

Modifying or Removing a Dropdown List

Google Sheets allows you to easily modify or remove a dropdown list to ensure consistent data input. Unlike Excel, Google Sheets is a collaborative tool that facilitates teamwork. So whether you created the dropdown list or one of your collaborators did, here’s how to proceed:

• To modify a dropdown list, select the cells you want to modify and choose an option:

  • Click on “Data” and select “Data validation”.
  • Click on the dropdown list and then on “Edit”.
  • Modify the dropdown list according to your needs. To change the listed options, edit the elements under “Criteria”. To remove a list, you have two options:
    • Click on “Remove rule”.
    • Select the empty cells, click on “Edit”, then on “Remove” and finally on “Values”.
  • To modify the display style, click on “Advanced options”. Under “Display style”, select an option: bullet, arrow, or plain text. Finally, click “OK”. Any changes you make to the selected range will automatically be applied to the dropdown list.

• To remove a dropdown list:

  • Select the cells with the dropdown list.
  • Click on “Data” and select “Data validation”.
  • Click on “Remove rule”.

Creating Dependent Dropdown Lists on Google Sheets

Dependent dropdown lists are incredibly useful in many situations as they allow you to create simpler and more user-friendly files. To create a dependent dropdown list in Google Sheets, you can use the FILTER function.

Creating a Dependent Dropdown List with the FILTER Function

The FILTER function allows you to create a dropdown list in Google Sheets that depends on another list. For example, you can create a dropdown list to choose a circuit cup in the Mario Kart video game and then, based on that choice, display the available circuits. Here’s how:

1. Create two reference lists: one for circuit cups in column A and one for circuits in column B.
2. Implement data validation using your first list in column A. Right-click the cell where you want to insert the dropdown list and choose “Data validation”.
3. In the “Data validation” window, select “List from a range”. Select the range that contains the circuit cups.
4. Check the box “Show dropdown list in cell” and “Reject input”, then save.
5. In another cell, add the FILTER formula by starting with the “=” sign and opening a parenthesis. Select the range of circuits from column B, then add a semicolon to separate the criteria.
6. Next, select the range that will serve as the filtering criterion, in our example, column A.
7. Compare column A to the chosen data in your first dropdown list by adding “=D2”. Close the parenthesis. The complete formula is: =FILTER(B2:B;A2:A=D2).
8. Create a dropdown list that references your list in column B, for example, in E2. Right-click the range where you want to insert the dropdown list and choose “Data validation” > “List from a range”. Select your range, then save.

Inserting a Dependent Dropdown List on Multiple Rows

Google Sheets allows you to create a dependent dropdown list on multiple rows by combining the FILTER and TRANSPOSE functions. Here’s how:

1. Select the range where you want to insert the cup choices. Right-click and choose “Data validation”.
2. Select column A starting from cell A2 to avoid the header, then click “OK”. Check “Reject input” and save. Now you can choose one of the cups with the created dropdown list.
3. In another cell, add the FILTER formula by starting with the “=” sign and opening a parenthesis. Select the range of circuits from column B.
4. Add a semicolon to insert the condition. Here, the condition is when column A is equal to what you have selected in the cup dropdown list.
5. Transpose the formula by adding “TRANSPOSE” between the “=” sign and “FILTER”, then open and close a parenthesis at the end of the formula. The complete formula is: =TRANSPOSE(FILTER(B2:B;A2:A=D11)).
6. Lock columns B and A by adding “$” before the column letters to prevent them from shifting when you drag the formula down. For example: =TRANSPOSE(FILTER($B$2:$B;$A$2:$A=D11)).
7. Add a dropdown menu in the “Circuit Choice” column that is linked to the adjacent range.

If you stretch your dropdown menu down, the circuits corresponding to the selected cup will appear in the data range. When you add a new cup to your table, the different circuits will be automatically added to the dropdown list.

These tips allow you to create powerful dropdown lists in Google Sheets. If you want to delve deeper into your knowledge of Google collaborative tools, our Collaborative Tools Google training program will teach you the FILTER and TRANSPOSE functions, as well as many other formulas for custom data validation. This 18-hour training program will make you completely self-sufficient in all Google Workspace tools.

Source: Create a dropdown list in a cell – Google Support