Dropdown lists from validation in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a dropdown list in cell A1 using data validation.

Excel

Select cell A1, then go to Data > Data Validation > Allow: [1].

Drag options to blanks, or click blank then click option'

ANumber

BText

CList

DDate

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'Text' instead of 'List' will not create a dropdown.

Selecting 'Number' or 'Date' restricts input but does not create a dropdown list.

The dropdown list is created by choosing 'List' in the Allow field of Data Validation.

2fill in blank

medium

Complete the formula to set the source of the dropdown list to cells B1 to B5.

Excel

In the Source box, enter: =[1]

Drag options to blanks, or click blank then click option'

AD1:D5

BA1:A5

CC1:C5

DB1:B5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range that does not contain the list items.

Forgetting to include the equal sign '=' before the range.

The source range for the dropdown list should be the cells containing the list items, here B1 to B5.

3fill in blank

hard

Fix the error in the formula to allow dropdown list from named range 'Fruits'.

Excel

In Source, enter: =[1]

Drag options to blanks, or click blank then click option'

AFruits

BFruits()

C='Fruits'

D"Fruits"

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around the named range name.

Using parentheses like a function call.

When using a named range in data validation, just type =Fruits without quotes or parentheses.

4fill in blank

hard

Fill both blanks to create a dropdown list with options 'Yes' and 'No' directly in the Source box.

Excel

In Source, enter: =[1],[2]

Drag options to blanks, or click blank then click option'

A"Yes"

B"Maybe"

C"No"

D"Ok"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around the options.

Using semicolons instead of commas.

To create a dropdown with specific text options, list them in quotes separated by commas: ="Yes","No".

5fill in blank

hard

Fill all three blanks to create a dropdown list from a dynamic named range 'Colors' that refers to cells C1 to C10.

Excel

Define named range 'Colors' as =OFFSET([1], 0, 0, COUNTA([2]), 1) and use =[3] in Source.

Drag options to blanks, or click blank then click option'

AC1

BC1:C10

CColors

DC2:C11

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell references in OFFSET.

Not using the named range name in the Source box.

The OFFSET function starts at C1, counts non-empty cells in C1:C10, and the named range 'Colors' is used in the Source box.