Dropdown menus in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this dropdown formula?

You have a dropdown list in cell A1 created with the formula =SPLIT("Red,Green,Blue", ","). What will be the list of options shown in the dropdown?

Google Sheets

=SPLIT("Red,Green,Blue", ",")

A Red Green Blue

B Red;Green;Blue

C Red, Green, Blue

D Red|Green|Blue

💡 Hint

Think about how SPLIT separates text by commas.

The SPLIT function splits the text at each comma, creating separate options: Red, Green, and Blue.

❓ Function Choice

intermediate

Which function creates a dropdown list from a range?

You want to create a dropdown menu in Google Sheets that shows all values from cells B1 to B5. Which function helps you do this?

A DATA_VALIDATION(B1:B5)

B INDIRECT(B1:B5)

C ARRAYFORMULA(B1:B5)

D No function needed; use Data Validation with range B1:B5

💡 Hint

Dropdown menus are set up in the menu, not by a formula.

Dropdown menus are created using Data Validation in the menu, where you select the range B1:B5 as the source.

🎯 Scenario

advanced

Dynamic dropdown list that updates automatically

You want a dropdown menu in cell D1 that always shows all unique names from column A, even when new names are added. Which formula should you use as the source in Data Validation?

A =SORT(UNIQUE(A:A))

B =FILTER(A:A, A:A<>"")

C =UNIQUE(A:A)

D =ARRAYFORMULA(A1:A)

💡 Hint

You want unique names sorted alphabetically.

Using SORT(UNIQUE(A:A)) returns a sorted list of unique names from column A, perfect for a dynamic dropdown.

❓ data_analysis

advanced

Identify the error in dropdown list source formula

A user tries to create a dropdown list with this formula as source: =FILTER(B1:B10, B1:B10>"Apple"). What error or issue will occur?

Google Sheets

=FILTER(B1:B10, B1:B10>"Apple")

A Dropdown will be empty if no values are greater than 'Apple'

B Error because comparison with text needs ARRAYFORMULA

C Syntax error due to missing quotes

D No error; dropdown shows all values greater than 'Apple'

💡 Hint

Think about what happens if no values meet the condition.

If no values in B1:B10 are greater than 'Apple', FILTER returns an empty list, so the dropdown will have no options.

🧠 Conceptual

expert

How to create dependent dropdown menus in Google Sheets?

You want to create two dropdown menus: the second menu's options depend on the first menu's choice. Which approach correctly achieves this?

A Use SPLIT function in the first dropdown to separate options for the second

B Use VLOOKUP in the first dropdown to filter second dropdown options

C Use INDIRECT function in the second dropdown's data validation referencing the first dropdown's cell

D Create both dropdowns with fixed lists; dependent behavior is automatic

💡 Hint

Think about how to reference a range dynamically based on a cell value.

Using INDIRECT in the second dropdown's data validation allows it to refer to a named range or range named after the first dropdown's choice, creating dependency.