Most Smartsheet KPI sheets show you one thing well: today. Open the sheet tomorrow and yesterday's numbers are gone, overwritten by the next SUMIFS or COUNTIFS calculation. You can see where you are, but not where you have been.
If you have ever been asked "how did this KPI change over the last two weeks" and had to admit you had no idea, this is the fix. It is not a new tool or a paid add-on. It is a structure change, plus one automation.
I've previously covered how to track your RAG changes on a monthly basis, but this is a more flexible solution.
Start with a single row of KPIs
The first step is to bring your metrics together onto one row, with one column per KPI, instead of scattering them across separate widgets or sheets.
For example, that row might pull together KPIs from three completely different sheets:
Service Desk
- Open tickets, Red, Amber, Green RAG
- Tickets closed today
Resource Management
- Resources over 100% allocation
- Resources under 50% allocation
- Unassigned hours this week
Project Portfolio (Control Center)
- Projects at risk this week
- Projects behind schedule
- Projects closed this month
Each of these columns is populated with a formula that pulls from your source data: INDEX MATCH to look up a specific value, SUMIFS or COUNTIFS to aggregate across a range. You can mix KPIs from completely different areas of the business on this same row, since you will split them across separate dashboards later. The only thing every KPI on this row has in common is the date, because they are all being calculated as of today. This is a good example of a long table, which is a good practice.
Next step on this sheet is to record today's date everyday with an automation. I recommend doing this towards the end of the day, before the next automation, so I set mine at 22:00.
Turn "today's row" into a growing history
A single row only ever shows the present moment. To build history, you need a second sheet, and an automation that copies that row into it on a schedule.
In Smartsheet, this is the Copy Row automation. Set it up to copy the row from your KPI sheet into a new "KPI History" sheet once a day. Over time, the destination sheet accumulates one row per day, each one a frozen snapshot of every KPI as it stood on that date.
Two details matter here, and both are easy to miss.
First, when Smartsheet copies a row, any formulas in that row are replaced with their calculated values on the destination sheet. That is exactly what you want. You are not trying to copy live formulas into your history sheet, you want a fixed record of what the number was on that day, one that will not change retroactively if the source data changes later.
Second, a normal "when a cell changes" trigger will not fire reliably here, because your KPI columns are driven by cross-sheet formulas rather than manual entry. Smartsheet does not treat a formula recalculation the same way it treats a person typing a value. Use a time-based, recurring automation instead (for example, "every day at 6pm"), rather than a change-triggered one. That way, the row gets copied on schedule regardless of what triggered the recalculation upstream.
Put the history sheet behind a report
Before you point anything at the history sheet, add one more layer: build a report on top of it, and use that report as the source for your dashboards, rather than the sheet itself.
The reason is simple. The history sheet grows by one row every single day. If a dashboard widget is set to a fixed range on the sheet, that range needs to be manually extended every time you want it to include the latest rows, otherwise the chart stops including new dates without any warning that it has fallen behind. A report does not have this problem, since it always includes every row that matches its criteria, so as new daily rows land in the sheet, they are automatically included. Point your dashboard at the report, and the range is always "the whole report," with nothing to maintain.
What you get once the history sheet exists
Once this has run for a few weeks, your KPI History sheet is no longer a single line, it is a table with a date column and one row per day. From here, the work shifts from "gathering the data" to "presenting it."
You can point dashboard widgets at the report to build day-by-day trend charts for any KPI, without touching the formulas that generate the raw numbers.
If daily granularity is more detail than a given audience needs, you do not need a second data collection process to see the data weekly or monthly. Use formulas, reference sheets, and reports on top of the same history sheet to roll the daily rows up into weekly or monthly views. The daily sheet stays as your single source of history, everything else is just a different lens on it.
Why this is worth the setup
The appeal of this approach is that it does not ask you to rebuild anything you already have. Your existing KPI formulas stay exactly where they are. You are adding one sheet and one scheduled automation on top of a structure you likely already have in place. From that point on, every "how has this changed" question has an answer sitting in a sheet, rather than requiring you to remember what last week looked like.
If you would like help setting this up for your own KPI sheets, or reviewing whether your current sheet structure is ready for it, get in touch through the contact form below.
