Data Dashboard Copy link

When to use

Trigger when the user asks to:

• "Build a dashboard" / "make a dashboard" on top of existing data
• Create a "demo view", "interactive summary", or "filterable view"
• Add KPIs, charts, and slicers around a flat data table or pivot
• Showcase a tool's ability to regenerate / re-filter data live

Do NOT trigger for: simple one-off charts, single pivot table requests, or formatting-only tasks.

Hard constraints (always)

• Never add, rename, or delete columns on the source data sheet. The only structural change allowed is wrapping the existing range as an Excel Table.
• Never modify existing pivot sheets the user has already built. Build alongside them.
• The dashboard must remain live — slicer selections should update KPIs and charts together.

Workflow

Step 1 — Inspect the data

Read the first few rows of the source sheet with get_cell_ranges (headers + 3-5 data rows). Identify:

• A numeric measure column (e.g. Amount, Revenue, Sales) — primary KPI
• A quantity / count column if present
• 3-4 categorical columns suitable for slicing (Customer, Product, Store, Status, Region, Category)
• A date column if present (for time-series chart)

Done when: you know the table name candidate, the measure column, and the slice dimensions.

Step 2 — Confirm scope (only if ambiguous)

If the data has many candidate dimensions or no obvious measure, use ask_user_question with two short questions max: dashboard scope (full vs minimal) and location (new sheet vs existing). Skip if the request is clear.

Step 3 — Convert source to Excel Table

Slicers require a Table. Wrap the source used range as a Table named tblOrders (or tbl<DataName>). If a table already exists, just rename it. Apply TableStyleMedium2.

const used = src.getUsedRange();
used.load("address");
await context.sync();
const tables = src.tables;
tables.load("items/name");
await context.sync();
let tbl = tables.items[0] || src.tables.add(used.address, true);
tbl.name = "tblOrders";
tbl.style = "TableStyleMedium2";

Done when: the table exists and is named.

Step 4 — Create Dashboard sheet

Add a new sheet named Dashboard. If one exists, delete and recreate. Do NOT set position via the property (it errors); use Excel.WorksheetPositionType if needed, or leave at end.

Done when: Dashboard sheet exists and is active.

Step 5 — Title bar, subtitle band, KPI cards

Layout (top to bottom):

• Row 2 (height 36): title — merged B2:N2, large bold white on dark blue (#1F4E78)
• Row 3 (height 40): subtitle / spacer band — optional merged B3:N3 subtitle text, or leave empty for breathing room below the title
• Row 4 (height 8): spacer
• Row 5 (height 24): KPI headers — 4 cards across [B:I], each merged across 2 columns (B5:C5, D5:E5, F5:G5, H5:I5), white bold on #2E75B6
• Row 6 (height 40): KPI values — large bold #1F4E78, thin #1F4E78 borders around each card

KPI formulas use SUBTOTAL over tblOrders[<column>] so they respect slicer filters:

• Total measure: =SUBTOTAL(9, tblOrders[Amount]) — currency format
• Total rows: =SUBTOTAL(3, tblOrders[<id column>]) — integer
• Total quantity: =SUBTOTAL(9, tblOrders[Quantity]) — integer
• Average unit metric: =SUBTOTAL(1, tblOrders[Unit Price]) — 2-decimal currency

Done when: all 4 KPIs return numbers (not errors) — verify via formula_results.

Step 6 — Build pivot tables on a hidden helper sheet

Create a sheet _PivotData. Build 4 pivots using the table name as source ("tblOrders") so they share the slicer cache:

• pvtCustomer at A1: rows = primary entity (Customer), values = sum of measure
• pvtProduct at E1: rows = secondary entity (Product), values = sum of measure
• pvtStore at I1: rows = location/store, values = sum of measure + sum of quantity
• pvtStatus at M1: rows = status, values = count of id

Sort pvtCustomer and pvtProduct descending by the data hierarchy.

Done when: all 4 pivots have populated address ranges — load layout.getRange().address to verify.

Step 7 — Add charts on Dashboard

Anchor charts via setPosition("topLeftCell", "bottomRightCell"):

• Chart 1 (BarStacked or BarClustered) — Revenue by Customer, source = pvtCustomer range
• Chart 2 (BarClustered) — Top N Products by Revenue, source = first 15-16 rows of pvtProduct
• Chart 3 (ColumnClustered) — Revenue & Quantity by Store, source = pvtStore
• Chart 4 (Doughnut) — Orders by Status, source = pvtStatus

Hide legend on single-series charts. Bold 12pt titles. Place in a 2x2 grid below KPIs (e.g. B8:G24, H8:N24, B25:G40, H25:N40).

Done when: 4 charts render with titles.

Step 8 — Add slicers

Use workbook.slicers.add(table, "<Column Name>", dashboardSheet). Slicers attached to the table propagate to:

• The table's SUBTOTAL formulas (KPIs)
• Any pivot built from the table (charts)

Add 4 slicers for the categorical columns identified in step 1. Set name, caption, then position via left, top, width, height properties. Place along the right side of the dashboard.

Done when: clicking a slicer tile updates KPIs AND charts together.

Step 9 — Polish

• Set column widths (~75pt for content columns, narrow 12pt gutters)
• Confirm row heights: title 36, subtitle/spacer band 40, KPI header 24, KPI value 40
• Add thin border around each KPI card
• dash.gridlines = false
• Hide the _PivotData helper sheet: helper.visibility = Excel.SheetVisibility.hidden

Done when: Dashboard sheet is clean, gridlines off, helper hidden.

Step 10 — Verify and report

• Activate Dashboard sheet
• Read back KPI values to confirm they're not errors
• Tell the user: where the dashboard is, what the slicers do, and that the source columns are unchanged

Common gotchas

• worksheet.position = N throws — don't set it via the property. Add the sheet, accept default position, or use WorksheetPositionType enum on add().
• Pivot tables sourced from a range ('Sheet'!A1:O100) do NOT share the slicer cache — slicers won't filter them. Always source pivots from the table name ("tblOrders").
• Table slicers (workbook.slicers.add(table, ...)) filter the table; pivot slicers filter only one pivot. Use table-based slicers so KPIs + pivots all respond.
• Loading pivotTable.name requires load("items/name") and a sync first. Same for any property read.
• Don't dump pivot data into chart calls — pass the pivot's layout.getRange() (or a sub-range like first 16 rows for "top N" charts).

Customization notes for non-sales data

The skill above uses sales/orders terminology (Customer/Product/Store/Status) as the canonical example. For other domains, map to the same shape:

• HR data: Employee / Department / Location / Status, measure = Headcount or Salary
• Finance: Account / Cost Center / Period / Status, measure = Amount
• Inventory: SKU / Warehouse / Category / Status, measure = On-Hand or Value
• Tickets: Assignee / Priority / Team / Status, measure = Count or Hours

Pick whichever 4 categorical columns make the most natural slice dimensions, and the most meaningful numeric column as the primary KPI measure.