Dropdown lists from validation in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of dropdown list selection formula

You have a dropdown list in cell A1 with options: Apple, Banana, Cherry. Cell B1 contains the formula =IF(A1="Banana", "Yellow", "Other"). What will be the value in B1 if you select "Banana" from the dropdown in A1?

A #VALUE!

B Yellow

C Other

D Banana

💡 Hint

Think about what the IF formula checks and returns based on the dropdown choice.

The formula checks if A1 equals "Banana". If true, it returns "Yellow"; otherwise, it returns "Other". Since you select "Banana", B1 shows "Yellow".

❓ Function Choice

intermediate

Choosing the right function to create a dropdown list

Which Excel feature or function is used to create a dropdown list for data validation?

A Data Validation with List option

B VLOOKUP function

C SUM function

D Conditional Formatting

💡 Hint

Dropdown lists come from a special feature under the Data tab.

Dropdown lists are created using Data Validation with the List option, not by formulas or formatting.

🎯 Scenario

advanced

Dynamic dropdown list with named range

You want a dropdown list in cell A1 that updates automatically when you add new items to the list in cells B1:B5. You create a named range called 'Fruits' referring to B1:B5. Which formula should you enter in the Data Validation source box to make the dropdown dynamic?

A =INDIRECT("B1:B5")

B =B1:B5

C =OFFSET(B1,0,0,COUNTA(B:B),1)

D =Fruits

💡 Hint

Named ranges can be used directly in Data Validation source.

Using the named range 'Fruits' (=Fruits) in the source box makes the dropdown use that range and update when the range changes.

📊 Formula Result

advanced

Result of INDIRECT function in dropdown source

You have a named range 'Colors' referring to cells C1:C4. In Data Validation source, you enter the formula =INDIRECT("Colors"). What will the dropdown list show?

A The list of values in cells C1 to C4

B The text 'Colors'

C An error message

D Empty dropdown list

💡 Hint

INDIRECT converts text to a reference.

INDIRECT("Colors") returns the range that the name 'Colors' refers to, so the dropdown shows the values in C1:C4.

❓ data_analysis

expert

Analyzing error in dropdown list source formula

You set up a dropdown list with Data Validation source formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). When you add a new item in column A, the dropdown does not update correctly and shows extra blank entries. What is the most likely cause?

A OFFSET function syntax is incorrect and causes error

B Data Validation does not support OFFSET function

C COUNTA counts all non-empty cells including header, causing extra blanks

D The formula should use COUNT instead of COUNTA

💡 Hint

Think about what COUNTA counts in the column including headers.

COUNTA counts all non-empty cells including the header, so OFFSET includes the header row and possibly blank cells below, causing extra blanks in dropdown.