Generate key reports dynamically with automated data updates and visualizations
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Weekly reports get skipped because assembling them takes an hour. This template reduces the weekly task to pasting one export: the commentary sheet is the only thing a human writes.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Metric | This week | Last week | Δ | 4-wk avg | Trend |
| 2 | Signups | 412 | 377 | +9.3% | 391 | ↑ |
| 3 | Activation % | 34.2% | 36.8% | -7.1% | 35.9% | ↓ |
| 4 | Tickets | 88 | 121 | -27.3% | 104 | ↓ |
| 5 | MRR added | 18,400 | 16,900 | +8.9% | 17,250 | ↑ |
Why each one is written the way it is
=IF([@[Last week]]=0, "", [@[This week]]/[@[Last week]]-1)
Week-on-week change, blank rather than an error for a metric that starts this week.
=AVERAGEIFS(History[Value],History[Metric],[@Metric],History[Week],">="&ThisWeek-3)
Four-week average, which damps the single-week noise that makes weekly reports over-reacted to.
=IFS([@[4-wk avg]]=0,"", [@[This week]]>[@[4-wk avg]]*1.02,"↑", [@[This week]]<[@[4-wk avg]]*0.98,"↓", TRUE,"→")
Direction against the recent average rather than against last week, with a 2% dead band so flat metrics do not flicker.
="Week "&ISOWEEKNUM(ReportDate)&" · "&TEXT(ReportDate-6,"d mmm")&" – "&TEXT(ReportDate,"d mmm yyyy")
The report header, generated from one date cell so nobody ships a report with last week's title.
A worked example with real numbers
A weekly update had been produced by one person for two years and stopped the week they left. Rebuilt around a single pasted export and a generated header, it took the replacement eight minutes rather than an hour, and — because the four-week average was now visible next to each figure — the standing debate about whether a single bad week meant anything largely ended.
Tab by tab
| Tab | Contents |
|---|---|
| Weekly | The report itself, entirely calculated. |
| History | Every week appended, the source for averages and trends. |
| Commentary | The only sheet a human writes into. |
| Import | Paste target for the raw export. |
| README | The weekly routine, in order, with the one manual step marked. |
What is included, and what to look at next
Specific to this workbook, not generic
Under ten minutes once set up: paste the export, read the trends, write three lines of commentary. If it takes longer, something that should be calculated is still being typed.
Yes, and it should be the shortest part. Numbers without interpretation get skimmed; the value is in naming what changed and what you are doing about it.
In Google Sheets, an Apps Script trigger can email the report every Monday morning. That is a small automation project.
"This automated reporting system has saved us hours each week. The reports are consistently accurate and professional, and stakeholders love the insights."