Build a dashboard in Excel or Google Sheets
The file falls apart after every fresh export: ranges shift, slicers come loose. This one is built to survive the next paste-in and the one after.
Hard · 40 min · set up once
What you get
an .xlsx file · set up once
File "sales_dashboard.xlsx", three sheets Source raw export, paste it here and nowhere else Calc pivots by channel, month, category Board 4 charts, slicers: channel, month, rep Ranges are structured tables: add rows freely, formulas don't point at row numbers. Built for Excel. Opened in Google Sheets, two of the slicers are unsupported and will drop out.
A sample on made-up data — your numbers will be your own.
Who it fits
- You need a file you can hand to a colleague: they swap the export and it all recalculates.
- The report lives in Excel and has to stay there — finance has no access to the cloud.
When it won't work
You want an online dashboard that refreshes itself on a schedule — that's a separate scenario.
How the agent does it
Describe the breakdowns
Say where the file will be opened most. Sheets and Excel treat slicers and pivots differently, and building for both at once helps neither.
Run the build
Data goes onto three sheets and the charts hang off the pivots, not the raw export — so a refresh doesn't break the visuals.
Test a fresh export
Ask the agent to review its own work: which formulas shift when the row count changes, and what a person must never edit by hand on refresh.
What you set
Connect Google Sheets and 1C. From you: the breakdowns you need in the pivots, and where the file will live — Google Sheets or an XLSX.
What you'll need
Starter prompt
Copy the prompt or open it straight in a chat with the agent.
Build a sales dashboard for [period] in [Google Sheets / XLSX] on 1C data. Arrange the file like this: a "Source" sheet — a flat table with no merged cells and a date column in a single format; a "Summary" sheet — pivot tables by [breakdowns]; a "Dashboard" sheet — charts and slicers tied to the pivots rather than to the source directly. Set the pivot ranges as whole columns or named ranges so that nothing shifts when a new export has a different number of rows. Don't hard-code calculated values into the dashboard cells: every figure must be recalculated from "Source". On a separate "How to refresh" sheet describe: where to paste the new export, what to click and what breaks if the column order in the export changes.
Open in chatReview how the file is built. List: — formulas and ranges that will shift if the new export has more or fewer rows; — cells where the value is typed in as a number rather than calculated from "Source"; — pivots and slicers that will lose their link if the column order or names change; — elements not supported in [Google Sheets / Excel] that will break on opening; — what exactly a person has to do on a refresh and what they must not touch by hand. Whatever you can rebuild yourself, show as a "proposed changes" list; leave the rest to me.
A second prompt — the agent uses it to review its own work and show what's left for you.