Data sheet → pivots → pivot charts → slicer; R feeds the data sheet

◈ 9 cards

The Excel build is six steps: orders on a data sheet, one pivot per panel, a PivotChart on each, KPI cells linked with =GETPIVOTDATA(…) or a plain cell reference, one slicer connected to every pivot through Report Connections. R feeds the data sheet: aggregate(), then write.csv(agg, "agg.csv", row.names = FALSE), then Data → From Text/CSV. Forget row.names = FALSE and the CSV gains a nameless first column of 1..4. The script is kept as the audit trail.

Six steps, in order

The Q1 dashboard from Lesson 14.1 is built in Excel in a fixed order, because each step is the source of the next:

Data sheet. orders — the twelve rows, A1:F13, as an Excel Table (Module 3) so the pivots grow when April arrives.
Pivots. One per panel, each on its own sheet: average amount by prov; sum of amount by month (group the dates, Module 7); sum of amount by prov × channel.
PivotCharts. PivotTable Analyze → PivotChart on each. A PivotChart follows its pivot's layout — sort the pivot descending and the bars sort; add a filter and the chart filters. It is not a static picture of the pivot, and that is the point.
KPI cells. The three tiles are cells on the dashboard sheet that read a pivot: =GETPIVOTDATA("amount", $A$ 3, "prov", "ON") pulls Ontario's value from the pivot whose top-left cell is $A$ 3, and keeps pulling it if the pivot re-sorts. A plain link (=Pivot1!B4) also works, and breaks silently the day row 4 becomes Quebec.
Slicer. Insert Slicer on channel from any one pivot.
Report Connections. Right-click the slicer → Report Connections → tick all three pivots. This is the step people skip: a slicer is born connected to the one pivot it was inserted from, so without it, clicking Online changes one chart and leaves the other two showing all twelve orders — a dashboard that quietly disagrees with itself.

Worked example — R feeds the data sheet

When the source is 100,000 rows, the data preparation happens in R (Module 12) and Excel receives the aggregate, not the raw file. In R:

> agg <- aggregate(amount ~ prov, data = orders, FUN = mean)
> agg$amount <- round(agg$amount, 2)
> write.csv(agg, "agg.csv", row.names = FALSE)
> cat(readLines("agg.csv"), sep = "\n")
"prov","amount"
"AB",1150
"BC",453.33
"ON",498.75
"QC",161.67

Four rows, two columns, a header — exactly what the comparison pivot needs. In Excel: Data → From Text/CSV, pick agg.csv, Load; the table lands on a sheet and becomes the pivot source. When April's orders arrive, re-run the R script, re-save agg.csv, Data → Refresh All, and every pivot, chart and tile updates.

Leave out row.names = FALSE and this happens:

> write.csv(agg, "agg_bad.csv")
> cat(readLines("agg_bad.csv"), sep = "\n")
"","prov","amount"
"1","AB",1150
"2","BC",453.33
"3","ON",498.75
"4","QC",161.67

A first column with an empty header holding the row numbers 1 to 4 — junk that Excel imports as Column1 and that someone will then hide instead of fix.

What is handed over

The deliverable from R to Excel is the small CSV and the script that made it. Not the raw 100,000-row file (Excel would choke and the preparation would be lost); not a screenshot of the console (nobody can re-run a picture); not an .RData from save() (Excel cannot open it). The script is the audit trail: it says exactly which rows were dropped, how the dates were parsed and which aggregate was taken, and it makes next month's refresh a one-line source("prep.R") instead of a morning's work.

Type both, then order the six steps

The figure is the pipeline; the two snippets are the two lines that join R to Excel.

CRISP-DM: deployment → plan deployment — the pipeline is the plan.

The build pipeline. Each step is the source of the next; the slicer is the last step because it must connect to pivots that already exist.