Google Sheets dashboard with live formulas and filters
Your team already works in Google Sheets and wants one tab that shows how things are going, updating itself as new rows come in. This prompt has your todo.is agent design the dashboard tab with QUERY, SUMIFS and SPARKLINE formulas, dropdown filters and charts, then deliver it as a file to import into Sheets, or put it in your Google Drive when Google is connected.
The prompt
- Build a Google Sheets dashboard for my data [DATA SOURCE]. The data tab has these columns: [COLUMNS]. On a new "Dashboard" tab, show: KPI cells for [KPIS] for the selected period, a dropdown to pick the month and one to pick [FILTER FIELD], a SPARKLINE trend next to each KPI, a QUERY-based top 10 table, and 2 charts (trend by week and breakdown by [BREAKDOWN]). Use only Google Sheets formulas that recalculate when new rows are added (open ranges like A2:A, no scripts). Give me the file to import, plus a short guide listing each formula, what it does and which cell to edit. If my Google Drive is connected, save it there as [FILE NAME].
What to change
- [DATA SOURCE]: Attach an export of the sheet (File > Download > .xlsx), or describe the data if it's not ready yet.
- [COLUMNS]: e.g. "Date, Rep, Region, Product, Units, Revenue, Status".
- [KPIS]: e.g. "revenue, deals won, win rate, average deal size".
- [FILTER FIELD]: e.g. "Region" or "Sales rep".
- [BREAKDOWN]: e.g. "product" or "lead source".
- [FILE NAME]: e.g. "Team Sales Dashboard".
Example result
- Team Sales Dashboard · Dashboard tab (example)
- Data tab: "Deals" with columns Date (A), Rep (B), Region (C), Product (D), Units (E), Revenue (F), Status (G)
- Filters (top of the tab)
- • B2: month dropdown (data validation, list of months from the data)
- • B3: region dropdown with "All" as the first option
- KPI cells
- • Revenue (B6): =SUMIFS(Deals!F2:F, Deals!G2:G, "Won", Deals!A2:A, ">="&B2, Deals!A2:A, "<"&EDATE(B2,1), Deals!C2:C, IF(B3="All","*",B3))
- • Deals won (B7): the same with COUNTIFS
- • Win rate (B8): =B7 / COUNTIFS(Deals!G2:G, "<>Open", Deals!A2:A, ">="&B2, Deals!A2:A, "<"&EDATE(B2,1))
- • Average deal size (B9): =IFERROR(B6/B7, 0)
- Sparklines
- • C6: =SPARKLINE(QUERY(Deals!A2:G, "select month(A)+1, sum(F) where G = 'Won' group by month(A)+1 label month(A)+1 '', sum(F) ''", 0), {"charttype","column"})
- • Shows revenue by month next to the KPI
- Top 10 table (E5)
- • =QUERY(Deals!A2:G, "select B, sum(F) where G = 'Won' group by B order by sum(F) desc limit 10 label B 'Rep', sum(F) 'Revenue'", 0)
- Charts
- • Line chart: revenue by week, from a helper range on a hidden "Calc" tab
- • Bar chart: revenue by product for the selected month
- Formula guide
- • Every range is open-ended (A2:A), so new rows are included automatically
- • To change the definition of a win, edit "Won" in B6 and B7
- • No Apps Script, so it works for anyone with edit access
- Tip: protect the Dashboard tab (Data > Protect sheets) so formulas don't get overwritten.
How to do it with todo.is
- Attach an export of your sheet and copy the prompt with your columns and KPIs.
- Paste it on the Today screen in todo.is, or send it to your agent on Slack.
- Your agent builds the dashboard tab and the formula guide, and saves it to Drive if Google is connected.
- Import the file into Google Sheets (File > Import), then ask your agent to adjust any KPI or chart.
Tips for a better result
- Keep the data in one clean tab with one header row. Dashboards break when people add notes between rows.
- Use open ranges like A2:A so new rows are counted without editing formulas.
- QUERY is powerful but sensitive to column types. Make sure a column holds only numbers or only text.
- Protect formula cells and leave only the dropdowns editable for your team.
Google Sheets dashboard: FAQ
- How do I make a dashboard in Google Sheets? Keep raw data on one tab, then build a dashboard tab with summary formulas (SUMIFS, QUERY), dropdown filters, sparklines and charts that point to those summaries.
- Can a Google Sheets dashboard update automatically? Yes. With formulas on open ranges, it recalculates whenever rows are added, whether by hand, a form or an import.
- Can todo.is edit my existing Google Sheet directly? With Google connected it can work with your Drive files. It can also give you the formulas to paste into your own sheet.
- Should I use Excel or Google Sheets for a dashboard? Google Sheets is easier to share and edit together in real time. Excel has more chart and pivot options and works offline.
JavaScript is required to use the todo.is app.