You can build a free KPI dashboard in Excel by turning a tidy data range into a table, calculating a few clearly defined measures, and presenting the results with KPI cards and a chart. This tutorial uses Microsoft Excel for Microsoft 365 on Windows and a small monthly example; you can create the workbook yourself without buying a dashboard template.
What a KPI dashboard shows
A KPI, or key performance indicator, is a measure chosen to track progress toward a specific business or team goal. A dashboard brings a small set of these measures together so a reader can quickly see the current result, how it compares with a target, and whether it is changing over time. A dashboard is not automatically useful just because it contains charts: the measures need clear definitions, reliable source data, and an audience that knows what action the results can inform.
For this tutorial, the example is a monthly sales and service dashboard. It shows revenue, revenue target, orders, and customer complaints. From those fields, the dashboard can display revenue attainment and complaints per 100 orders. These measures are illustrative, not universal standards. A service team might choose response time or resolution rate; a project team might track milestones completed against plan. Pick measures that reflect the work being managed rather than filling the page with every number available.
It also helps to distinguish a KPI from a metric. A metric is any measured value; a KPI is a metric treated as important to a goal. For example, total website visits may be a useful metric, but it is not necessarily a KPI if the goal is to improve qualified leads and visits do not indicate lead quality.
Plan the measures before opening Excel
Write down the audience, the decision the dashboard should support, and the definition of each measure before building charts. Specify the reporting period, units, owner, and source. For a percentage, document the numerator and denominator; for a target, clarify whether it is monthly, cumulative, or based on a different period. Consistent definitions make the dashboard easier to maintain and prevent two people from interpreting the same label differently.
- Revenue: the amount recorded for the selected month, in the reporting currency.
- Revenue target: the planned revenue for that same month.
- Revenue attainment: actual revenue divided by target, formatted as a percentage.
- Complaint rate: complaints per 100 orders. This is a rate, not the percentage of unique customers who complained.
Keep the number of headline KPIs modest. Four clearly labeled measures are often easier to scan than a crowded page of unrelated indicators. If readers need additional detail, provide a supporting table or a separate worksheet rather than shrinking every chart and label.
Prepare the source data
Create a worksheet named Data. In row 1, enter these headings: Month, Revenue, Revenue Target, Orders, and Complaints. Enter one row per month, with the first day of each month stored as a real Excel date. For a small demonstration, use January through March 2026:
- January 1, 2026 — revenue 42,000; target 40,000; orders 840; complaints 21.
- February 1, 2026 — revenue 45,000; target 45,000; orders 900; complaints 18.
- March 1, 2026 — revenue 48,000; target 46,000; orders 960; complaints 24.
Format the Month column as a month and year, such as Jan 2026, while retaining the underlying date value. Select the data and choose Insert > Table, confirm that the table has headers, and select OK. Tables expand when new rows are added and carry formatting and formulas into added rows, which makes them a practical source for a growing dashboard. Give the table a descriptive name from the Table Design tab, such as SalesData.
Keep one record per period and one type of information per column. Avoid merged cells, blank spacer rows inside the data, totals mixed into the monthly records, and manually typed currency symbols. Apply number formats instead. If figures come from exports, check that dates are actual dates and numbers are numeric values rather than text; sorting and formulas can behave unexpectedly otherwise.
Build the KPI calculations
Create a worksheet named Dashboard. Put a reporting-month selector in G2 and enter January 1, 2026 as its initial value, formatted as Jan 2026. You can add a drop-down later with Data > Data Validation and the List option, using a range containing the available month dates. For this short example, select a month by typing or choosing one of the dates already present on the Data sheet.
Place labels in F4:F8: Actual revenue, Revenue target, Attainment, Orders, and Complaints per 100 orders. The following formulas assume the source dates are in Data!A2:A4; revenue is in Data!B2:B4; target is in Data!C2:C4; orders are in Data!D2:D4; complaints are in Data!E2:E4; and the selected month is Dashboard!G2.
- With the source layout above and the selected month in Dashboard!G2, enter this in Dashboard!G4 to return revenue:
=SUMIFS(Data!$B$2:$B$4,Data!$A$2:$A$4,$G$2). For January, the result is 42,000. - With the same layout and selector, enter this in Dashboard!G5 to return the target:
=SUMIFS(Data!$C$2:$C$4,Data!$A$2:$A$4,$G$2). For January, the result is 40,000. - With actual revenue in G4 and target in G5, enter this in Dashboard!G6:
=G4/G5. For January, the result is 1.05, which displays as 105% when G6 is formatted as a percentage. - With orders in Data!D2:D4, dates in Data!A2:A4, and the selector in G2, enter this in Dashboard!G7:
=SUMIFS(Data!$D$2:$D$4,Data!$A$2:$A$4,$G$2). For January, the result is 840 orders. - With complaints in Data!E2:E4, orders in Data!D2:D4, dates in Data!A2:A4, and the selector in G2, enter this in Dashboard!G8:
=IF(SUMIFS(Data!$D$2:$D$4,Data!$A$2:$A$4,$G$2)=0,0,SUMIFS(Data!$E$2:$E$4,Data!$A$2:$A$4,$G$2)/SUMIFS(Data!$D$2:$D$4,Data!$A$2:$A$4,$G$2)*100). January has 21 complaints and 840 orders, so the result is 2.5 complaints per 100 orders.
The formulas use SUMIFS to add values matching the selected month. If you replace the fixed ranges with table references or extend the ranges as data grows, make sure the criteria ranges and sum ranges cover the same rows. Format revenue as currency or a clearly labeled number, attainment as a percentage, and the complaint rate with an appropriate number of decimal places. A percentage format changes how a number is displayed; it does not change the underlying calculation.
Turn calculations into a readable dashboard
Make the worksheet understandable at a glance. Put a descriptive title and the reporting period near the top, then arrange the KPI values in a row or a compact grid. Use larger type for the figures, smaller text for labels, and consistent number formats. Keep enough whitespace that the values do not run together. If revenue is shown in thousands, label it clearly rather than relying on readers to infer the scale.
To show the monthly revenue trend, select the Month and Revenue fields on the Data sheet, then choose Insert > Recommended Charts and select a suitable line or column chart. Check that the month labels appear in chronological order and that the vertical axis makes the scale clear. Move or copy the chart to the Dashboard sheet and add a meaningful chart title, such as Monthly Revenue. A chart of only three months is useful for demonstrating the layout, but it is not enough history to support broad claims about a long-term trend.
Use Home > Conditional Formatting sparingly to flag values that need attention, such as attainment below a target threshold. Explain what a color means with a label or legend; do not rely on red and green alone, since some readers may not distinguish those colors easily. Avoid three-dimensional charts, excessive decorative icons, and gauges that obscure the actual value. A clear number with its target and period is often more informative than a complicated visual.
Keep the dashboard accurate as it grows
Before sharing the workbook, test the selector with each month and compare the displayed values with the source rows. January’s revenue attainment should be 105% because 42,000 divided by 40,000 equals 1.05. February’s attainment is 100%, and March’s is about 104.35% because 48,000 divided by 46,000 is approximately 1.0435. The complaint rates are 2.5, 2, and 2.5 per 100 orders, respectively.
Common problems include mismatched dates, an incorrect denominator, duplicated source records, and targets covering a different period from actual results. A blank result can mean the selected date does not exactly match the source date. A zero complaint rate may mean there were no complaints, but it can also signal missing data; confirm which interpretation is correct before presenting it. If orders are zero, the example formula returns zero to avoid a division error, so consider displaying a separate note or blank state when zero means “not applicable” in your reporting context.
Refresh and review the source whenever new data is added. If you use a chart based on an Excel Table, confirm that it includes newly added records. Protecting worksheet structure can reduce accidental edits, but it does not make a workbook a secure repository for confidential information. Share only data the audience is permitted to see, and follow your organization’s access and retention rules.
Free templates, alternatives, and maintenance
A self-built workbook costs nothing beyond access to Excel, but it does require time to define, check, and maintain the measures. Excel also offers template discovery from File > New; available templates can vary by account, installation, and time. Treat a downloaded or built-in template as a starting layout, not a verified business model. Inspect its formulas, sample data, date assumptions, and licensing or sharing terms before using it with real information.
For a very small report, a formatted table and a chart may be enough; a dashboard is not mandatory. For a larger or repeatable report, a PivotTable can summarize source records and can be refreshed when the source changes. If several people need a shared, interactive report or the source data is too large for a simple workbook, a dedicated reporting or business-intelligence tool may be more suitable. Those alternatives can involve setup, permissions, training, or subscription costs, so compare them against the actual reporting need rather than assuming a more complex tool is automatically better.
Save the workbook with a clear name and date or reporting period, and keep a separate copy of the raw source if it must remain unchanged. If the dashboard is used every month, document how to add data, update the month list, check formulas, and refresh any summaries. A brief handover note can prevent a useful workbook from becoming dependent on one person who remembers how it was assembled.
Frequently Asked Questions
Can I make a KPI dashboard in Excel for free?
Yes, if you already have access to Excel, you can build one using a worksheet, formulas, tables, and charts without purchasing a separate template. Whether Excel itself is free depends on your device, account, and license; the tutorial does not require a paid dashboard add-in.
Does Excel have a free KPI dashboard template?
Excel’s File > New area can show templates, but availability can differ. You can also create a reusable workbook from the steps above. Always inspect a template’s formulas and assumptions before replacing its sample data with live figures.
What should a basic KPI dashboard include?
Include a reporting period, a small set of defined measures, targets or comparisons where relevant, and a chart or table that helps explain change over time. Label units and clarify any rate’s denominator so readers can interpret the figures.
How do I make a KPI dashboard update when I add data?
Keep source records in an Excel Table and use formulas or summaries that cover the expanding source. Check charts and any month-selection list after adding records; a table helps with expansion, but it does not guarantee every separately configured range updates.
Should I use a PivotTable or formulas?
Use formulas for a small, fixed set of calculations that need to sit in specific dashboard cells. A PivotTable is useful for summarizing larger sets of records across categories and periods. Both approaches need correct source data and periodic checks.
Why does my KPI formula show zero?
Check that the selected month exactly matches a date in the source, that the source values are numeric, and that the selected period contains records. For a rate, verify that the denominator is nonzero and that returning zero is meaningful rather than hiding missing or inapplicable data.
Related
- How to Unhide Rows in Excel Using Keyboard Shortcuts
- Unhide Rows in Excel: Shortcut Keys and Other Methods
- Unhide Columns in Excel Using the Keyboard
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Chart IF Formula Examples in ExcelYes. In Excel, use an IF formula to create chart-ready values in helper columns, then select those values…
- How to Create a Candlestick Chart in Excel: Step by StepTo create a candlestick chart in Excel, arrange each trading period in columns ordered Date, Open, High, Low,…
- Excel Pie Chart Template: Create, Customize, and ReuseAn Excel pie chart template is a reusable chart design: it saves a chart type and formatting so…
- How to Put a Pivot Table in Google Sheets: Step by StepTo put a pivot table in Google Sheets, select a cell in your data, choose Insert > Pivot…
- How to Put Sparklines in ExcelTo put sparklines in Excel, select the blank cell or cells where you want the mini charts, choose…
- Excel Histogram Bin Size: How to Set the Right WidthIn Excel for Microsoft 365 on Windows, select the histogram’s horizontal axis, open Format Axis, and under Axis…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.