How to build a weekly KPI dashboard from Google Sheets (without a data team)

It's Monday at 8:40 AM. You paste 3 exports into the sheet, fix the formulas that broke, and send the numbers by 10. On Wednesday someone asks why revenue doesn't match finance's figure, and you spend an hour finding a totals row that got counted twice.

Here's how to build a weekly KPI dashboard in Google Sheets that you can rebuild in minutes and explain line by line, with built-in functions only.

What decision should a weekly KPI dashboard serve?

Start with the reader and the decision, not the data. Write one sentence: who reads this on Monday, and what will they decide? For example: "Each Monday the team lead decides whether to spend the week on getting new signups or on helping recent signups finish setup."

That sentence filters everything after it. For each candidate number, ask: if this moved 10%, would the decision change? If not, leave it off the dashboard.

Which 5 to 7 metrics should you pick?

Pick 5 to 7: enough to explain a change, few enough for one screen. A useful mix is 1 or 2 outcome numbers, 2 or 3 drivers that explain them, and 1 health check that warns when something breaks.

A hypothetical set for that decision, built from 3 exports:

Write each definition before you build. If 2 people can get different numbers from it on the same rows, it isn't finished. Our metrics dictionary template gives you a format.

How should you set up the sheet?

Use 3 kinds of tabs, and never mix them:

• Raw tabs. One per export: Raw_Signups, Raw_Payments, Raw_Tickets. Each week, clear the old export and paste the new one as is. Don't edit the pasted cells, sort them, or add totals. The only additions are formula columns to the right of the export.
• The report tab. Only formulas that read the raw tabs. A number typed by hand can't be traced.
• A notes tab. The definitions, the week rule, and the rows you left out each week.

Keep exactly one header row in each raw tab, with one plain name per column. Google's pivot table help is direct about it: "Each column needs a header."

Then add one helper column to the right of each export, the Monday that starts the row's week. If the date is in column A:

=IF(A2="", "", INT(A2)-WEEKDAY(A2,2)+1)

With type 2, WEEKDAY numbers Monday as 1 through Sunday as 7, so the formula lands on that week's Monday. INT rounds down to a whole number, which drops the time of day if your export has one. Format the column as a date, call it Week start, and fill it down well past the last row. Empty rows stay blank, and next week's longer export is still covered. Every method below groups by this column, so they always agree on the week.

How do you turn rows into weekly numbers?

There are 3 ways, and each suits a different job.

Pivot tables, for exploring

Select the data, choose Insert > Pivot table, and put Week start in Rows and the amount in Values. It refreshes when the source cells change. To trace a cell, click Edit under the table and double-click the cell: a new sheet opens with its source rows. Good for spotting odd weeks, less so as a fixed report, since each new week shifts the layout.

QUERY, for a weekly trend table

One formula returns a whole table. If Raw_Payments has the date in A, the amount in D (refunds negative), and Week start in E:

=QUERY(Raw_Payments!A1:E, "select E, sum(D) where E is not null group by E order by E", 1)

The last argument says there is 1 header row. Leave it out and Sheets guesses. Inside the query, columns are named by letter.

COUNTIFS and SUMIFS, for the report tab

For the numbers people read, use one formula per cell. Put the Monday of the week you're reporting on in B1 (say, October 5, 2026, for October 5 to 11). If Raw_Signups has the account type (Customer or Test) in C, Setup done (Yes or No) in D, and Week start in E, with New signups in B3:

• New signups: =COUNTIFS(Raw_Signups!E:E, B1, Raw_Signups!C:C, "Customer")
• Activation rate: =COUNTIFS(Raw_Signups!E:E, B1, Raw_Signups!C:C, "Customer", Raw_Signups!D:D, "Yes")/B3
• Revenue: =SUMIFS(Raw_Payments!D:D, Raw_Payments!E:E, B1)

SUMIFS adds a row only when it meets every condition. COUNTIFS needs ranges of the same size, so whole columns are simplest. Anyone can click a cell and read what it counts.

Add the date the data runs through

In the report tab, compute the last date each export covers:

=MAX(Raw_Payments!A:A)

MAX ignores cells without numbers, so the header doesn't interfere. Show it next to each number that uses that export, as "Data through October 11, 2026," because exports rarely end on the same day. If the date is earlier than the Sunday that ends your week, mark the week as partial. More in our guide to showing data freshness.

Should you use ISOWEEKNUM or WEEKNUM?

If your team says "week 41," everyone must mean the same 7 days. Sheets has 2 functions that disagree:

• WEEKNUM(date) starts weeks on Sunday by default, and week 1 is the week that contains January 1. Its optional type changes the start day, and type 21 switches to the ISO system.
• ISOWEEKNUM(date) follows ISO 8601: weeks run Monday to Sunday, and week 1 contains the year's first Thursday.

In 2026, week 41 is October 4 to 10 under WEEKNUM and October 5 to 11 under ISOWEEKNUM. Same label, different days, different totals.

Year ends are worse. With the WEEKNUM default, December 27 to 31, 2026 is week 53 and January 1 and 2, 2027 are week 1, so one real week becomes 2 short ones. ISOWEEKNUM keeps December 28, 2026 to January 3, 2027 together as week 53, but January 1 to 3 then belong to 2026's week 53. Build a label from the calendar year plus the week number and they become "2027-W53."

The fix is the Week start column. A Monday date never splits a week and sorts correctly across years. If people like week numbers, show ISOWEEKNUM as a label beside it, and write the rule in the notes tab: "Weeks run Monday to Sunday (ISO 8601)."

Which errors should you check before you send?

Check every raw tab for 5 problems. Each has a quick test for the report tab, using COUNTA (counts every cell with a value, including text and spaces) and COUNT (counts numbers, ignores text).

With dates in A, IDs in B, and amounts in D: =COUNT(Raw_Payments!A2:A) counts dates, =COUNTA(Raw_Payments!B2:B) counts IDs, and =COUNT(Raw_Payments!D2:D) counts amounts. On a clean tab all 3 match.

• Totals rows. Say your export has 1,203 payment rows plus a Total row holding their sum. A total of the whole column is now double the real revenue. Delete the row and log it in the notes tab.
• Blank amounts. A row with an ID but no amount still counts in COUNTIFS, while SUMIFS adds nothing for it, so the average payment drops. Fix them at the source, or leave them out and say how many.
• Duplicates. Overlapping exports repeat rows. Next to the IDs, =COUNTIFS(B:B, B2) shows how many rows share each ID; above 1 is a duplicate. Data > Data cleanup > Remove duplicates deletes them, but it also treats values that differ only in letter case or formatting as duplicates. Copy the duplicates to your notes tab first.
• Merged headers. A merged "Q3" cell over several columns, or a second header row, leaves columns without their own name. Unmerge and keep one header row.
• Dates stored as text. QUERY treats each column as one type: the majority type wins and other values become null. So a QUERY over a date column with 40 text dates among 1,200 real ones skips those 40 without warning. MAX skips them too, which can make your data-through date too early. Convert them with DATEVALUE after checking the sheet's locale (File > Settings), which sets the default date format. The formats DATEVALUE understands depend on region and language, so 05/10/2026 can become May 10 or October 5.

Where Every Number fits

If the Monday rebuild eats an hour, or people keep asking where a number came from, try our $99 concierge pilot. You send one spreadsheet, and a person builds the dashboard by hand, following the rules the product will automate after launch. You get 5 to 7 metrics with plain-English definitions you approve first, a receipt behind every number, every left-out row listed with its reason, and the date the data runs through. See a weekly KPI example, or how it works.

It's not a good fit if your sheet already works and nobody questions the numbers. It's also not a live connection: the pilot is built from the spreadsheet you send.