Interactive Dropdowns and Linked Metrics

A static report shows one fixed view. An interactive dashboard lets the reader pick what they want to see, and the numbers respond instantly. The key tool is a dropdown selector wired into your formulas.

The idea is simple: a single cell holds the user's choice, such as a region or month. Every metric on the dashboard references that one cell. Change the dropdown, and the entire dashboard recalculates around the new selection.

In this lesson you will build a dropdown and link totals, counts, and filtered views to it.

A dropdown comes from Data Validation. Select the selector cell, for example B1, then open Data, Data Validation, and choose List.

For the source you can point at a range of valid options:

• Source range: =Lists!A2:A6 holding East, West, North, South, All.
• Or generate the list with a formula like =SORT(UNIQUE(Sales!A2:A500)) in a helper column and point the validation at it.

Now B1 shows a little arrow and only accepts a value from your list. That single cell becomes the control knob for the dashboard.

Decide on one cell as your control, say B1. Every formula will read it. The discipline of routing all interactivity through one cell keeps a dashboard easy to understand and maintain.

A first linked metric is total sales for the chosen region. With B1 holding the selection:

Pick West in B1 and this returns West's total. Pick North and it updates immediately. One formula, infinite views.

Add a second linked number: how many orders the selected region had. COUNTIF reads the same selector cell.

Place this next to your total:

Because both the total and the count point at B1, they always describe the same selection. Build every dashboard tile to read the control cell and they will never disagree with each other.

Dashboards usually need a way to see everything. If your list includes an All choice, your formula must handle it because SUMIF would look for a literal region named All.

Use IF to branch on the All selection:

When B1 is All, you get the full total; otherwise you get the filtered total. This pattern keeps an everything view available without breaking the criteria logic.

Beyond single numbers, the dropdown can drive a whole table of detail rows. FILTER reads the selector and spills the matching rows.

Below your metrics, place:

Choose East and every East row appears; choose West and the block rewrites. The third argument gives a clean message when nothing matches, so an empty selection never shows an ugly error on the dashboard.

Real dashboards often have several selectors, such as Region in B1 and Quarter in B2. Combine them by reading both in one formula.

Use SUMIFS to honor both choices at once:

Now the reader narrows the view by region and quarter together. Each added dropdown is just another criteria pair fed from its own control cell.

A polished dashboard echoes the current choice in its heading so readers know what they are looking at. Build a dynamic title by joining text with the selector cell.

In a title cell, write:

If B1 is North, the heading reads Sales Summary for North. The & operator joins text and cell values. This small touch makes an interactive dashboard feel finished and self-explanatory.

If new regions appear in your data, a hard-coded dropdown list goes stale. Keep it fresh by feeding the validation from a spilled formula.

In a helper area, put:

Then point Data Validation at that spill range using its hash reference, such as =Lists!A2#. As new regions arrive, the list grows and the dropdown offers them automatically. Your control stays accurate with no manual edits.

If your dashboard includes a chart, you can make its title follow the dropdown too. Charts let a title reference a cell, so point that title at a formula cell that reads the selector.

Build the dynamic caption in a spare cell:

Then in the chart, set the title to reference this cell. Now switching B1 from East to West also relabels the chart. Every visible element, numbers and visuals alike, tracks the one control cell.

A few principles keep interactive dashboards trustworthy:

• One control per choice: route each selector through a single, clearly labeled cell.
• Read, never duplicate: every tile references the control cell, so they all agree.
• Plan for All and empty: handle the everything case and the no-match case gracefully.

With these habits, a reader changes one dropdown and watches totals, counts, tables, and titles update together as one living report.

Confirm you understand how to keep an everything view working.

You turned a static report into an interactive dashboard:

• Data Validation created a dropdown in a single control cell like B1.
• SUMIF and COUNTIF linked metrics to that selection, with an IF branch to handle an All option.
• FILTER drove a detail table from the same control, and SUMIFS combined two dropdowns.
• A joined-text title and a spilled validation list kept the dashboard self-explanatory and current.

Next you will craft headline KPI cards and conditional highlights that flag the numbers that matter most.