Kpiloom
Excel

How to Create a Dashboard in Excel: Step-by-Step Guide

Learn how to create a dashboard in Excel in seven clear steps: clean data, a calculation sheet, PivotTables, charts, slicers and a layout that people actually read.

By the Kpiloom team · · 4 min read


An Excel dashboard is one sheet that answers the questions you ask every week: are sales up, where is money going, what needs attention. The good news is that you don't need macros or add-ins. Excel already has everything: tables, PivotTables, charts and slicers.

This guide walks through the same seven steps we use to build every Excel dashboard at Kpiloom.

Step 1: Decide what the dashboard must answer

Before you open Excel, write down three to five questions. For a sales dashboard they might be:

  • How much did we sell this month, and is it more than last month?
  • Which region or product is growing, and which is falling?
  • Will we reach our target?

Each question becomes one KPI or one chart. If a number doesn't answer a question, leave it out. A dashboard with 25 numbers is a report, and nobody reads it.

Step 2: Put your raw data in one clean table

Dashboards break when data is messy. Keep all raw data on a sheet called Data and follow three rules:

  1. One row per record (one sale, one invoice, one employee).
  2. One column per field, with a clear header such as Date, Region, Product, Amount.
  3. No blank rows, merged cells or totals inside the data.

Then select the data and press Ctrl + T to turn it into an Excel Table. Name it, for example, tblSales. Tables grow automatically when you add rows, so every formula and chart that uses them stays up to date.

Step 3: Calculate your KPIs on a separate sheet

Create a sheet called Calc. This is where the maths lives, so the dashboard sheet stays clean. Use formulas that read from the table:

KPIExample formula
Revenue this month=SUMIFS(tblSales[Amount], tblSales[Date], ">="&StartDate, tblSales[Date], "<="&EndDate)
Orders=COUNTIFS(tblSales[Date], ">="&StartDate, tblSales[Date], "<="&EndDate)
Average order value=IFERROR(Revenue / Orders, 0)
Growth vs last month=IFERROR(Revenue / RevenueLastMonth - 1, 0)

Put the start and end dates in two named cells (StartDate and EndDate) so one change updates the whole dashboard. Wrapping divisions in IFERROR keeps the dashboard free of #DIV/0! errors in a month with no data.

Step 4: Summarise with PivotTables

For breakdowns such as sales by region or by month, PivotTables are faster and safer than long formulas. Select your table, choose Insert → PivotTable, and place it on the Calc sheet. Drag Region to Rows, Date to Columns (grouped by month) and Amount to Values.

Make one PivotTable per chart you plan to show. Keep them on the Calc sheet, not on the dashboard.

Step 5: Build the charts

Pick the chart that matches the question:

  • Trend over time: line or column chart.
  • Comparison between items: bar chart, sorted from largest to smallest.
  • Share of a total: a donut chart, but only with five slices or fewer.
  • Actual vs target: a bar with a target line, or a simple gauge.

Remove clutter: delete gridlines and the legend when there is only one series, and write a title that states the point, such as "Revenue by month, 2026".

Step 6: Add slicers so people can filter

Click any PivotTable and choose PivotTable Analyze → Insert Slicer. Add slicers for the fields people filter by, such as Region or Product. Then right-click a slicer, choose Report Connections, and connect it to every PivotTable. Now one click filters the whole dashboard.

For dates, Insert Timeline gives a neat month and quarter selector.

Step 7: Lay out the dashboard sheet

Create a sheet called Dashboard and arrange it in this order:

  1. Top row: four to six KPI cards (big number, small label, change vs last period).
  2. Middle: the main trend chart, full width.
  3. Bottom: breakdowns and a small table of top items.

Align everything to the grid, use one accent colour, and hide gridlines (View → Gridlines). Freeze the top rows so the KPI cards stay visible.

Common mistakes to avoid

  • Hard-coding numbers in charts instead of linking them to formulas.
  • Too many colours, which make it hard to see what matters.
  • No date filter, so people can't compare this month with last month.
  • Mixing raw data and the dashboard on one sheet.

Save time with a ready-made dashboard

Building your first dashboard takes a day or two. If you'd rather start from a finished layout, every Kpiloom Excel sales dashboard already includes the Data, Calc and Dashboard sheets described above, with slicers, a timeline and documented formulas. You can also browse finance and HR versions for your industry.

Three good starting points, depending on your business: a shop owner can begin with the retail sales dashboard for Excel, a restaurant can track food cost and covers in the restaurant finance dashboard, and a growing team can follow headcount and attrition in the IT services HR dashboard. Each one uses the same Data, Calc and Dashboard structure, so the steps above apply to all of them.

Frequently asked questions

Can I make a dashboard in Excel without macros?

Yes. Tables, formulas, PivotTables, charts and slicers are enough for an interactive dashboard, and the file stays safe to share.

Which Excel version do I need?

Excel 2016 or later, or Microsoft 365, supports everything in this guide, including slicers and timelines.

How do I update an Excel dashboard with new data?

Add new rows to the data table, then choose Data → Refresh All. Formulas, PivotTables and charts all update.

Skip the build. Start with a ready dashboard.

Templates for Excel and Google Sheets, with live previews.

Browse dashboards

Dashboards related to this article

Keep reading