How to Create a KPI Dashboard in Excel
To create a KPI dashboard in Excel, choose five to eight KPIs, calculate each one for this period and the last, compare them with a target, and show them as cards above the trends that explain them. The hard part is not the charts; it is defining each KPI so it is calculated the same way every month.
Define your KPIs with the steps below, or upload the data and get headline figures with the change against last period, trends and breakdowns, calculated and explained, in one step.
1. Choose and define the KPIs
Write one line per KPI before touching Excel: its name, the formula, the column it comes from, and whether higher is better. "Revenue = SUM of Net Sales for the period" and "Conversion rate = orders ÷ sessions" leave nothing to argue about later. Five to eight is enough; a dashboard with twenty numbers has no headline.
2. Calculate this period, last period and target
On a Calc sheet, compute each KPI for the current month and the previous one with SUMIFS or COUNTIFS on the data Table, keyed on a month cell so the whole sheet moves when you change it. Add the target next to each. Change is (this − last) ÷ last; show it as a percentage.
3. Build the KPI cards
On the dashboard sheet, give each KPI a card: the value in a large font, the change underneath, and a conditional-format arrow or colour against the target. Keep the cards in one row at the top so the eye reads them first. Rates are averaged over the whole period, never summed month by month.
4. Add the trends that explain them
Under the cards, one chart per important KPI: a line of the last 12 months, and one bar chart for the breakdown that explains the most (by product, region or channel). A month cell or a slicer at the top lets the reader move the whole dashboard to another period.
Upload a spreadsheet, get the dashboard
Excel, CSV or Google Sheets. DataMimi recognises what the data is and builds:
- Headline figures with the change against the previous period
- The trend over time, and breakdowns by category, product and region
- Written findings, checked against the figures
- Filters: pick a period, press a bar to narrow everything to that group
- Next month's file added without counting a month twice
- Or connect Google Sheets and refresh on a schedule
Free account, no card. The free plan includes credits to build your first dashboards.
What usually goes wrong with it
KPIs defined differently each month
Revenue with refunds one month and without the next makes the change meaningless, and nobody notices until the numbers are questioned.
DataMimi It shows the formula and the columns under every figure, so the KPI is visibly the same calculation each month.
Rates added up
Summing a conversion rate or a margin across months gives a number that means nothing; rates have to be recalculated from their parts for the period.
DataMimi It never sums a rate: margins and conversion rates are recalculated from their parts for the period shown.
Balances treated as flows
A cash balance, MRR or headcount is a level: the KPI is the latest value, not the total of every month.
DataMimi It recognises levels such as MRR, balances and headcount and shows the latest value instead of a total.
Too many numbers
A dashboard with twenty equally sized figures has no headline, so readers skip it.
DataMimi It picks the headline figures that fit the kind of data and puts the breakdowns under them.
Common questions
How do I create a KPI dashboard in Excel from scratch?
Define five to eight KPIs with their formulas, calculate each for this and last period on a Calc sheet, show them as cards with the change and a target colour, and put the trend charts that explain them underneath.
What is a KPI dashboard in Excel?
A single sheet that shows the handful of numbers a business is run on, each compared with the previous period and a target, with the charts that explain the change.
How do I make KPI cards in an Excel dashboard?
A cell with the value in a large font, the percentage change below it, and conditional formatting that turns it green or red against the target. Shapes linked to cells work too.
What is the difference between a KPI dashboard and a KPI report?
The report lists the numbers; the dashboard puts them against last period and target so the reader sees at a glance what needs attention.
Can I make a KPI scorecard in Excel?
Yes: a scorecard is the card row on its own, usually with a status column. Build the cards first and the charts are optional.
Turn your file into a dashboard
Upload the spreadsheet and DataMimi lays it out — figures, trend, breakdowns and findings — in one step.