Toggle the Displayed Metric (CHOOSE)

Excel Formulas › Dashboards & Reporting

All versionsCHOOSE

Let one chart or tile switch between revenue, units, and margin with a single control. CHOOSE (or INDEX) returns the selected metric’s value from an index a button or dropdown sets.

Quick formula: return the metric for the chosen index:
=CHOOSE(selector, revenue, units, margin)
A selector of 1/2/3 picks the matching argument; wire it to option buttons or a dropdown.

Functions used (tap for the full reference guide):

CHOOSEINDEX

The example

Selector = 2 → show Units.

AB
1SelectorShows
22 (Units)5,200

The formula

The formula:

=CHOOSE(selector, revenue, units, margin) // index picks the metric

How it works

How it works:

  1. CHOOSE(index, a, b, c) returns the index-th argument — 1→a, 2→b, 3→c.
  2. Drive the index from option buttons (form control) or a small dropdown.
  3. Point a chart series at the CHOOSE cell (or a column built with it) so it switches too.
  4. For many metrics, INDEX(metric_row, selector) scales better than a long CHOOSE.

One toggle, whole dashboard. Put the selector in a single cell and build every tile and chart series off CHOOSE/INDEX of it. A form-control option group or a dropdown sets the number, and the entire view flips between metrics — the same one-driver pattern as the dropdown dashboard, applied to what is shown rather than which item.

Try it: interactive demo

Live demo

Choose a metric.

Value:

Variations

INDEX for many metrics

Scales better:

=INDEX(metric_values, selector)

Label too

Switch the title:

=CHOOSE(selector, "Revenue", "Units", "Margin")

Switch a whole column

Per row:

=CHOOSE($selector, A2, B2, C2)

Pitfalls & errors

Index from 1. CHOOSE is 1-based; a 0 or blank selector errors.

Match the order. Argument order must line up with the selector meaning.

INDEX for length. Many options? INDEX of a range beats a sprawling CHOOSE.

Practice workbook

📊
Download the free Toggle the Displayed Metric (CHOOSE) practice workbook
A metric-toggle sheet with the INDEX, label, and column variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I toggle a dashboard metric in Excel?
Use =CHOOSE(selector, revenue, units, margin) with the selector set by option buttons or a dropdown.
What if I have many metrics?
Use =INDEX(metric_values, selector) — it scales better than a long CHOOSE.
How do I switch the chart title too?
Use =CHOOSE(selector, "Revenue", "Units", "Margin") for the label.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SWITCH function · Dynamic dashboard dropdown · INDEX & MATCH 2D

Function references: CHOOSEINDEX