A weekly KPI snapshot is different from a full dashboard. It's not meant to be comprehensive — it's meant to be fast. The goal is: in 60 seconds, what do I need to know about this week? This guide shows you how to build one in Google Sheets that answers that question clearly.
What Goes in a Weekly Snapshot
Pick 5-8 numbers. Not more. The weekly snapshot works precisely because it's constrained. If you put 30 metrics in it, people stop reading it.
Good candidates for a weekly snapshot: revenue this week, new customers, pipeline added, churn, top of funnel (leads or sessions), a key operational metric (tickets closed, deliveries made, whatever's core to your business), and cash on hand if you're tracking burn.
The Layout
Stack your metrics vertically, one per row. Four columns: Metric Name, This Week, Last Week, Change. That's it. No charts, no sub-tables, no commentary. Just the numbers and the movement.
Add a fifth column for the trend arrow using a simple IF:
=IF(C2>D2, "▲ " & TEXT((C2-D2)/D2,"0%"), "▼ " & TEXT((C2-D2)/D2,"0%"))
Where C2 is this week and D2 is last week. This renders as "▲ 12%" or "▼ 4%" inline. Apply conditional formatting to make the arrows green or red.
Populating the Data
Keep a running log sheet with one column per week and one row per metric. Your snapshot formulas reference the most recent two columns:
=INDEX(WeeklyLog!B:ZZ, MATCH(A2, WeeklyLog!A:A, 0), COUNTA(WeeklyLog!1:1))
This pulls the most recent week's value for each metric automatically as you add new columns to the log. You never have to update the snapshot formula — just add data to the log.
Sharing It
The simplest distribution: set the sharing link to "anyone with the link can view" and paste it into your team Slack or email every Monday morning. Or use Google Apps Script to email it automatically as a PDF each week.
If you want something fancier, use the SPARKLINE function to add a tiny trend line for each metric — one formula per row showing the last 8 weeks at a glance.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: Your metrics are already tracked in the spreadsheet.
"I have a weekly log on Sheet 1 with one row per metric and one column per week. Build a snapshot sheet showing this week vs. last week for each metric, with percentage change and color coding for positive and negative movement."
SheetXAI reads your log, builds the snapshot view, adds the change formulas, and applies the conditional formatting.
Example 2: Your data lives across your CRM, ad platforms, and finance tools.
"Pull this week's revenue, new customers, pipeline added, and ad spend from Stripe, HubSpot, and Google Ads, and build a weekly snapshot showing week-over-week change for each."
SheetXAI connects to all three sources and builds the unified snapshot automatically.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a KPI Dashboard in Google Sheets, How to Automate Reports in Google Sheets, and Google Sheets AI Guide.
