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

A good dashboard turns hundreds of rows into a few numbers you can act on: are sales ahead of target, what's selling, and where customers come from. This guide shows how to create a dashboard in Excel from scratch, step by step, using only what's already in Excel.

You'll plan the dashboard, turn your data into a table, summarize it with PivotTables (or formulas), add charts, KPI cards and slicers, then lay everything out on one screen. The steps work in Microsoft 365 and Excel 2016 or later, on Windows or Mac. Menu names are from Microsoft 365. Excel 2016 and 2019 use older names for some tabs, such as Analyze for PivotTable Analyze.

What is a dashboard in Excel?

An Excel dashboard is a single sheet of charts, key numbers and filters that summarizes data stored elsewhere in the workbook. It shows how things are going at a glance, so you don't have to read every row.

Stephen Few, whose book Information Dashboard Design is a standard reference on the subject, defines a dashboard as a visual display of the most important information needed to reach one or more objectives, arranged on a single screen so it can be monitored at a glance. Two parts of that definition matter most when you build one in Excel:

• The most important information. A dashboard isn't a report. If a number doesn't help someone make a decision, leave it out.
• One screen. If people have to scroll to see the key numbers, it's a report with charts in it.

Most Excel dashboards are built from the same five parts: KPI cards for the headline numbers, a trend chart, one or two breakdown charts, a short table of top items, and filters such as slicers or drop-down lists.

Plan your dashboard before you build

Ten minutes of planning saves hours of rebuilding. Before you open Excel, write down:

1. Who it's for. An owner checking the business once a day needs different numbers from a manager running one department.
2. The 3 to 5 questions it must answer. For example: Are we on track for this month's sales target? Which products sell best? Which channel brings in the most revenue? Is the trend up or down?
3. The KPI behind each question, and what you'll compare it with: a target, last month or the same month last year. A number on its own says very little.
4. The time period it covers, such as this month, the last 12 months or the year to date.
5. A rough sketch. Draw boxes on paper: headline numbers along the top, the main trend chart below them, breakdowns underneath. You'll copy this layout in step 7.

If you're not sure which numbers matter in your business, start with the KPIs used in your industry. Our personal trainer client tracking guide shows how that works for one type of business.

How to create a dashboard in Excel in 7 steps

The example in this guide is a small online shop. Its sales data has one row per order, with these columns: Date, Order ID, Channel, Product, Category, Units and Amount.

Step 1: Put your data in an Excel table

Dashboards break when the data underneath them is messy, so start here. Your data should have:

• One header row with a short, unique name for each column
• One row per record (one order, booking or transaction), with no blank rows and no subtotals in between
• No merged cells
• Real dates and numbers, not text that looks like them
• Consistent categories: "Website" every time, never "website" or "Web site". Drop-down lists (Data > Data Validation) stop typos at the source.

Then click any cell in the data and choose Insert > Table (or press Ctrl+T; on a Mac, Cmd+T). Tick My table has headers and click OK. On the Table Design tab (the Table tab on a Mac), give the table a short name such as Sales.

A table matters because it grows. When you type in the row directly below it, the table expands to include the new row, and anything built on the table can pick that row up. Fixed ranges like A2:G500 silently miss row 501.

Step 2: Set up three sheets

Keep the data, the calculations and the display apart:

• Data: the table, and nothing else. This is where new rows go.
• Calc (or Pivots): the PivotTables or formulas that summarize the data. Nobody needs to look at this sheet.
• Dashboard: charts, KPI cards and filters only. Nobody types here, so nothing gets overwritten.

Step 3: Summarize the data with PivotTables or formulas

Each question from your plan needs a small summary table. There are two ways to build them.

PivotTables are the quickest. Click in the Sales table, choose Insert > PivotTable, and place it on the Calc sheet. Then drag fields into place. For revenue by month, drag Date to Rows (Excel groups dates by year, quarter and month automatically) and Amount to Values. Build one PivotTable per question: revenue by month, revenue by product, revenue by channel. Build them all from the same table, because that lets one slicer filter all of them later.

Formulas give you full control of the layout and update the moment data changes. For revenue in a month, with the month's first day in cell A2:

=SUMIFS(Sales[Amount],Sales[Date],">="&A2,Sales[Date],"<"&EDATE(A2,1))

And for the number of orders from one channel:

=COUNTIFS(Sales[Channel],"Website")

PivotTables are faster to build and great for exploring. Formulas are better when you want an exact layout and no refreshing. Many good dashboards use both.

Step 4: Add charts

Pick the chart that answers the question, not the one that looks most impressive:

To chart a PivotTable, click inside it and choose PivotTable Analyze > PivotChart. For formula ranges, select the cells and use Insert > Recommended Charts. On Windows, Alt+F1 drops in a default chart instantly.

Then cut each chart and paste it on the Dashboard sheet. Clean it up as you go: give it a title that says what it shows, delete the legend if there's only one series, and remove extra gridlines. On PivotCharts, hide the gray field buttons with PivotChart Analyze > Field Buttons > Hide All.

Step 5: Add KPI cards for the headline numbers

KPI cards are the big numbers along the top: revenue this month, orders, average order value, revenue against target. Build each one in a few cells on the Dashboard sheet:

• A small label, such as "Revenue this month"
• The value in a large, bold font, linked to the Calc sheet (for example =Calc!B2)
• A comparison underneath, such as "▲ 8.2% vs last month" or "92% of target"

Keep to four to six cards. If everything is a headline, nothing is.

Step 6: Add slicers so people can filter

Slicers are clickable buttons that filter PivotTables and the charts built on them. Click a PivotTable, choose PivotTable Analyze > Insert Slicer, and tick the fields people will want to filter by, such as Channel or Category. For dates, PivotTable Analyze > Insert Timeline adds a slider that filters by year, quarter, month or day. On a Mac, timelines need Microsoft 365 or Excel 2021 or later.

To make one slicer filter every chart, select it and choose Slicer > Report Connections, then tick every PivotTable. Only PivotTables built from the same data source appear in the list, which is why step 3 builds them all from one table.

Step 7: Lay it out, tidy up and share

Arrange the parts on the Dashboard sheet following your sketch: KPI cards across the top, the main trend chart large and near the top, breakdown charts below and slicers along the top or left edge. Then:

• Align everything. On Windows, hold Alt while you drag or resize a chart and its edges snap to the cell gridlines. To line up several objects, Ctrl+click them (Shift+click on a Mac) and use Shape Format > Align.
• Hide the gridlines and headings. On the View tab, untick Gridlines and Headings.
• Use two or three colors, consistently. If "Website" is blue in one chart, make it blue in every chart.
• Protect the layout with Review > Protect Sheet if other people will use it. Slicers need an extra setting to keep working on a protected sheet.
• Refresh when the data changes. PivotTables don't update on their own. Use Data > Refresh All (Ctrl+Alt+F5 on Windows) after adding rows, or turn on Refresh data when opening the file in PivotTable Analyze > Options > Data. Formula-based panels update immediately.

Save the workbook to OneDrive or SharePoint if a team needs to see it, or send the file.

An example of the finished layout: our Profit and Loss Dashboard, with KPI cards along the top and a department slicer that filters every card and chart.

Example: a one-page sales dashboard

Here's how the online shop's dashboard maps from questions to panels:

Common mistakes to avoid

• Too many charts. Six to eight panels is plenty for one screen. More than that and nothing stands out.
• Numbers without context. "$48,200" means little. "$48,200, 92% of target" tells you what to do.
• 3D charts and crowded pies. They distort the numbers. Flat bars and lines are easier to read.
• Typed totals. A total you typed by hand won't change when the data does. Every number should come from a formula or PivotTable.
• Fixed ranges. Formulas pointing at A2:A500 stop counting at row 500. Point them at table columns instead.
• Forgetting to refresh. A PivotTable shows the data as it was at its last refresh.
• Merged cells in the data. They break sorting, filtering and PivotTables. In layouts, use Center Across Selection instead (Format Cells > Alignment).

Frequently asked questions

What is a dashboard in Excel?

A dashboard in Excel is one sheet that summarizes your data with KPI cards, charts and filters, so you can see how things are going at a glance. The data and calculations sit on other sheets, and the dashboard only displays the results.

What is the easiest way to create a dashboard in Excel?

Format your data as a table, build a PivotTable for each question you want answered, add a PivotChart to each one, then insert a slicer and connect it to all the PivotTables. Move the charts and slicer to one sheet and hide the gridlines.

Can I create a dashboard in Excel without pivot tables?

Yes. Functions such as SUMIFS, COUNTIFS and AVERAGEIFS can calculate every number a PivotTable would, and they update as soon as the data changes, with no refresh. Drop-down lists can replace slicers for filtering.

What version of Excel do I need?

PivotTables, charts and slicers work in Microsoft 365 and Excel 2016 or later, on Windows and Mac. Newer functions such as XLOOKUP, FILTER and UNIQUE need Microsoft 365 or Excel 2021 or later.

Can Copilot create a dashboard in Excel for me?

If your Microsoft 365 plan includes Copilot, yes. Copilot in Excel can create charts, PivotTables and whole dashboards from your data. Check its numbers before you rely on them. The steps in this guide are also how you fix or extend what it builds.

Does Excel have dashboard templates?

Yes. Excel includes some templates under File > New (File > New from Template on a Mac), and you can buy ready-made dashboards built for a specific business. Ours come with sample data, so every chart is filled in when you first open the file.

Sources

• Microsoft Support: Create and share a Dashboard with Excel and Microsoft Groups
• Microsoft Support: Resize a table by adding or removing rows and columns
• Microsoft Support: Use slicers to filter data
• Microsoft Support: Refresh PivotTable data
• Microsoft Support: Get started with Copilot in Excel
• Stephen Few: Dashboard Confusion, InformationWeek (2004)