Measuring KPIs in Excel seems simple until the sheet grows, the data becomes outdated, and no one trusts the final number. These are the questions that come up most often when someone tries to set up a real dashboard in a spreadsheet, with direct answers and no promises of magic.
What formula do I use to calculate a monthly KPI that doesn’t break when adding rows?
The short answer: Structured table (Ctrl+T) + references with column names. If you write =SUM(B2:B500), any new row outside the range is ignored and the KPI lies without warning. When you convert the range into a table and use =SUM(Table1[Month]), Excel expands the reference automatically.
An error I see often: using VLOOKUP with fixed ranges to bring in the month’s target. When a row is inserted above, the reference shifts and the KPI compares March sales with February’s target. With XLOOKUP and a structured table, that failure disappears.
How do I compare the KPI with the previous month without recalculating by hand?
With a helper date column and =SUMIFS(Values; Dates; “>=”&DATE(YEAR(TODAY());MONTH(TODAY())-1;1); Dates; “<“&DATE(YEAR(TODAY());MONTH(TODAY());1)). It’s the most robust way to get the complete previous period, including year changes.
Practical warning: this formula depends on the dates being real dates, not text. If the exported CSV brings “01/03/2026” as a string, the calculation returns zero and it looks like there were no sales. Check with =ISNUMBER(cell) before blaming the formula.
Is Excel good for KPIs with more than 100,000 rows?
It works, but with caveats. The limit is 1,048,576 rows per sheet, so volume isn’t the problem; the problem is the performance of volatile formulas (TODAY(), NOW(), INDIRECT()) and of pivot tables that recalculate on opening. With 200,000 rows and several SUMIFS columns, the sheet can take tens of seconds on every change.
When NOT to use Excel: if you need hourly updates from a database, or if several people edit the same sheet at the same time without version control. There, Power Query + a data warehouse, or a BI tool, avoids the chaos. Excel shines in one-off analysis and small, controlled dashboards.
What KPIs make sense to put in an Excel dashboard?
It depends on the business, but a structure that works:
- Acquisition: sessions, cost per lead, form conversion rate.
- Retention: active customers, monthly churn, purchase frequency.
- Revenue: average ticket, gross margin, recurring revenue.
- Efficiency: acquisition cost vs. customer lifetime value.
Golden rule: one KPI per row, one metric per column, and one date column. If you mix units (percentages with euros) in the same column, the pivot table and charts become unreadable.
How do I prevent the dashboard from showing outdated figures?
Two concrete mechanisms:
- Power Query to import the CSV or SQL query and update with one click (Data > Refresh All). Schedule the refresh when the file opens.
- A control cell with the last update date: =IFERROR(TEXT(MAX(Table1[Date]);”dd/mm/yyyy”);”no data”). If that date doesn’t match today, the KPI isn’t reliable and whoever reads it should know.
This doesn’t guarantee correct data: it guarantees that you know when it was loaded. Validating the source is still your responsibility.
Should I measure KPIs in Excel or move directly to Power BI?
If the team is one or two people, the data fits in one sheet, and the report is sent weekly by email, Excel is enough and faster to set up. If there are multiple sources, role-based permissions, or near-real-time updates, Power BI or Looker Studio save hours. According to official Microsoft documentation, Power Query is the same engine in Excel and in Power BI, so migrating later doesn’t mean redoing the transformations: only the model and the visuals.
What doesn’t work well in Excel: controlling who modified a key formula. There’s no granular change history per cell beyond co-authoring, and a #REF! can go unnoticed for days. If the KPI feeds investment decisions, protect it with a hidden sheet and password, and document each formula in a methodology tab.
How do I document the definition of each KPI?
With a “Dictionary” tab that includes: KPI name, exact formula, data source, calculation period, and owner. Without this, two people calculate the same KPI differently and the meeting turns into a discussion about numbers instead of about decisions. It’s the least flashy part and the one that prevents the most problems.