Ravindra BagaleCourses & study guides मराठी Track your progress

8. Charts

8.14 Drop-down Driven Dynamic Chart (INDEX / XLOOKUP)

By Last updated:

Drop-down driven dynamic chart with example — in 30 seconds

What: A helper row picks one city's numbers with XLOOKUP, based on a drop-down. The chart reads only the helper row.

Why: One chart serves every city. The manager picks Nashik and the chart redraws, with no copies of the chart.

Tiny example: Pick Nashik in B1. The helper row fills with Nashik's six months: 1.8, 2.0, 2.3, 3.1, 2.6, 2.2.

First look at the data (before → after):

Drop-down driven dynamic chart with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — Helper rows on the Dashboard when B1 = Nashik

Row Aug Sep Oct Nov Dec Jan
3: Month Aug Sep Oct Nov Dec Jan
4: Sales 1.8 2.0 2.3 3.1 2.6 2.2

About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Meera Joshi) are made up for this lesson on drop down driven dynamic chart index xlookup.

Before table and formula as text

Before — ChartData sheet (₹ lakh, fictional)

A: Month B: Pune C: Nashik D: Nagpur
Aug 3.1 1.8 1.6
Sep 3.4 2.0 1.7
Oct 4.2 2.3 2.1
Nov 5.6 3.1 2.9
Dec 4.8 2.6 2.4
Jan 4.1 2.2 2.0

About this data: Demo table only — not real customer or company data. Built to practise drop down driven dynamic chart index xlookup.

Formula / click path

Dashboard!B1: drop-down (Data Validation › List: Pune,Nashik,Nagpur)
B4: =XLOOKUP(B$3, ChartData!$A$2:$A$7, XLOOKUP($B$1, ChartData!$B$1:$D$1, ChartData!$B$2:$D$7))
Chart title cell: =B1&" – monthly sales (₹ lakh)"

Do it step by step:

  1. On the Dashboard sheet, make a drop-down in B1 with the list Pune, Nashik, Nagpur.
  2. Type the six months across B3:G3.
  3. In B4, type the formula shown above and copy it across to G4.
  4. Select B3:G4 and insert a Line chart.
  5. Change B1 to Nagpur. The line redraws.

The user picks a city from a drop-down; one chart redraws for that city.

Steps in Excel

  1. In Dash!B1 create a drop-down (Data Validation › List) with the city names Pune,Nashik,Nagpur.

  2. Build a helper row: months in Dash!B3:G3 (Jul…Dec). In Dash!B4:

    =INDEX(ChartData!$B$2:$D$7, MATCH(B$3, ChartData!$A$2:$A$7, 0), MATCH($B$1, ChartData!$B$1:$D$1, 0))

    copy across to G4. (Microsoft 365 alternative in B4: =XLOOKUP(B$3, ChartData!$A$2:$A$7, XLOOKUP($B$1, ChartData!$B$1:$D$1, ChartData!$B$2:$D$7)).) 3. Chart title cell: =B1&" – monthly sales (₹ lakh)". 4. Select B3:G4 › Insert › Charts › Clustered Column. 5. Link the chart title to the title cell: click the title › in the formula bar type = › click the title cell › Enter. 6. Change the drop-down to Nagpur – the chart and its title change.

Ravindra Bagale's Tip

In a dynamic chart, many students build the chart directly on the big table and then try to change the "series" – it becomes a mess. The rule: always build the chart on a small helper range, and fill the helper range with formulas (INDEX/XLOOKUP) based on the drop-down. Link the chart title to a cell too, otherwise the city changes but the title stays "Pune".

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of drop down driven dynamic chart index xlookup on a blank sheet for BrightLedger Finance.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Vikram Rao and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Yuki Tanaka.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply drop down driven dynamic chart index xlookup to a 5-row made-up BrightLedger Finance table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with drop down driven dynamic chart index xlookup.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as BrightLedger_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Total parcels (Easy): Add up the "Parcels" column.
  2. Average cost (Easy): Average of the Cost (£) column, rounded to 2 decimals.
  3. Rows in Royal Mail (Easy): How many rows have Category/group "Royal Mail"? (the "Carrier" column)
  4. Largest parcels (Easy): The largest value in the "Parcels" column.
  5. Parcels for Royal Mail (Medium): Total of the Parcels column where Carrier is "Royal Mail".
  6. Royal Mail in Manchester (Medium): Total of the Parcels column where Carrier is "Royal Mail" and City is "Manchester".
  7. Best in Royal Mail (Medium): Largest value in the "Cost (£)" column where "Carrier" is "Royal Mail".
  8. Value of row 2 (Medium): Multiply the first-row Parcels by the first-row Cost (£).
  9. Look up Priya Kulkarni (Medium): The Parcels for "Priya Kulkarni" (use a lookup on the "Name" column).
  10. Total value (Hard): Sum of "Parcels" times "Cost (£)" for all rows.
  11. Share of Royal Mail (Hard): Share of the "Parcels" column that is "Royal Mail", rounded to 3 decimals.
  12. Total "Parcels" for Evri (Hard): Add up the "Parcels" column for the rows where "Carrier" is "Evri".
  13. Above average? (Hard): "Above" if the "Cost (£)" value in the first row is above the average of the "Cost (£)" column, else "Below".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Drop Down Driven Dynamic Chart Index Xlookup?
Card 1: What is Drop Down Driven Dynamic Chart Index Xlookup? · Download PNG
Revision card 2: Tiny example — Drop Down Driven Dynamic Chart Index Xlookup
Card 2: Tiny example — Drop Down Driven Dynamic Chart Index Xlookup · Download PNG
Revision card 3: Checklist — Drop Down Driven Dynamic Chart Index Xlookup
Card 3: Checklist — Drop Down Driven Dynamic Chart Index Xlookup · Download PNG