A drop-down list as a chart selector

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which Data Validation setting creates a drop-down list of items taken from a range of cells?

A Allow: Whole number, with the Source set to the range of items

B Allow: List, with the Source set to the range of items

2. What does the drop-down cell do in a chart selector?

A It stores the item the user picks, so formulas can read it and the chart can show that item

B It draws the chart itself, so no formulas are needed

3. Which is the better way to use the selector cell in a formula that feeds the chart?

A =XLOOKUP($B$1, A5:A10, B5:B10)

B =XLOOKUP("Maths", A5:A10, B5:B10)

4. The user picks 'Science' in the drop-down but the chart still shows Maths. What is the most likely cause?

A Drop-down lists can only be used with text, never with charts

B The chart's data comes from cells that do not read the selector cell

5. Why give the selector cell a name like SelectedSubject?

A Formulas such as =XLOOKUP(SelectedSubject, ...) are easier to read and check

B A cell must be named before a drop-down can be added to it

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=$F$2:$F$6		A=XLOOKUP(Selected_Item,A2:A9,C2:C9)
2=INDEX(B2:E2,MATCH(H1,B1:E1,0))		BThe Data Validation List source: the range of items that appear in the drop-down
3Naming the input cell makes a formula easier to read than using a cell address		CThe chart reads the original table, not the formula cells that depend on the drop-down, so choosing a different item changes nothing on the chart.
4A label next to the selector tells the user what to choose		DG1: Choose a subject:
5Allow: List Source: F2:F6		EReads the item chosen in the drop-down cell H1 and returns its value for the chart to use
6Chart data: =Sheet1!$B$2:$B$9 (the full table)		FThe source is missing the = sign, so Excel treats F2:F6 as typed text and the drop-down shows only one item called F2:F6 instead of the items in the range.

Definitions

Data Validation List

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Data Validation Allow: ____ Source: =$E$2:$E$6

Name Box for B1: Selected =INDEX(B2:B6,MATCH(________,A2:A6,0))

A1: Choose a region B1: drop-down list C1: ="Sales: "&__

=INDEX(B2:B6,_____(B1,A2:A6,0))

=INDEX(B2:B6,MATCH(B1,A2:A6,_))

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A A1: Choose a student

B B1: Data Validation > List

C Source: =$A$4:$A$9

D E4: =XLOOKUP(A1,A4:A9,B4:B9)

Question 2

A A1: Pick a month

B B1: Data Validation

C Allow: Whole number

D Source: =$A$4:$A$9

Question 3

A Select A4:A9

B Data > Data Validation

C Allow: List

D Source: =$A$4:$A$9

Question 4

A Select B1

B Name Box: Pick, press Enter

C E4: =XLOOKUP(Pik,A4:A9,B4:B9)

D Chart series values: E4

Question 5

A Select B1

B Type in B1: Choose a student

C Data Validation > List

D Source: =$A$4:$A$9

Answer Key

A drop-down list as a chart selector

Section A: Quick Questions

B — Allow: List, with the Source set to the range of items

A — It stores the item the user picks, so formulas can read it and the chart can show that item

A — =XLOOKUP($B$1, A5:A10, B5:B10)

B — The chart's data comes from cells that do not read the selector cell

A — Formulas such as =XLOOKUP(SelectedSubject, ...) are easier to read and check

Section B: Match the Pairs

=$F$2:$F$6 → The Data Validation List source: the range of items that appear in the drop-down

=INDEX(B2:E2,MATCH(H1,B1:E1,0)) → Reads the item chosen in the drop-down cell H1 and returns its value for the chart to use

Naming the input cell makes a formula easier to read than using a cell address → =XLOOKUP(Selected_Item,A2:A9,C2:C9)

A label next to the selector tells the user what to choose → G1: Choose a subject:

Allow: List Source: F2:F6 → The source is missing the = sign, so Excel treats F2:F6 as typed text and the drop-down shows only one item called F2:F6 instead of the items in the range.

Chart data: =Sheet1!$B$2:$B$9 (the full table) → The chart reads the original table, not the formula cells that depend on the drop-down, so choosing a different item changes nothing on the chart.

Section C: Definitions

Data Validation List — A drop-down in one cell, made with Data Validation, that lets the user pick an item from a range; formulas read that cell so the chart shows only the chosen item.

Section D: Fill in the Blanks

Data Validation
Allow: List
Source: =$E$2:$E$6

Name Box for B1: Selected
=INDEX(B2:B6,MATCH(Selected,A2:A6,0))

A1: Choose a region
B1: drop-down list
C1: ="Sales: "&B1

=INDEX(B2:B6,MATCH(B1,A2:A6,0))

Section E: Spot the Error

D — A1 holds only the label text. The formula must read the selector cell B1, so the chart follows the chosen item. It should be =XLOOKUP(B1,A4:A9,B4:B9).

C — A whole number rule only accepts numbers and gives no drop-down. To get a drop-down of items from a range, Allow must be List.

A — The drop-down must go in the single selector cell B1. Selecting the list range A4:A9 would put a drop-down in every cell of the list itself. It should be Select B1.

C — The named input cell is called Pick, but the formula uses Pik, which does not exist and gives #NAME?. It should be =XLOOKUP(Pick,A4:A9,B4:B9).

B — The label belongs in A1, next to the selector. Typing it in B1 overwrites the selector cell. It should be Type in A1: Choose a student.