How to Make a Dashboard in Excel (and a 2-Minute Shortcut)

To make a dashboard in Excel, put your data in a clean table, build pivot tables that summarise it, add a chart for each pivot, connect them with slicers, and arrange everything on one sheet. It takes about an hour the first time and a few minutes to refresh each month after that. This step-by-step guide walks through each stage with a sales example, shows how to build a KPI dashboard in Excel, covers the Google Sheets version, and ends with a faster AI route if you just need the dashboard, not the practice.

We make one of the tools in the last section, the KissMySkills AI Dashboard. We say where it fits and where it does not. If you already have Excel, it is free, flexible and works offline, and for many people it is the right answer.

Before you start: shape your data

Most Excel dashboard problems are data problems. Spend ten minutes here and the rest goes smoothly. Your data should be one table where:

• Row 1 holds the column names, one per column, with no merged cells.
• Each row is one record: one order, one deal or one transaction.
• There are no blank rows, subtotal rows or notes inside the data.
• Dates are real dates, not text. If they are left-aligned in the cell, Excel probably sees them as text.
• Numbers are real numbers, without stray spaces or text like "n/a".

For this guide we use a simple sales table you can recreate in a few minutes, or swap in your own export from Shopify, Stripe or your CRM:

Prefer to follow along with a ready file? Download the free sample dashboard (.xlsx). It has 200 rows of this sales data as a Table named Sales, the summary workings, a finished dashboard sheet with KPI tiles and charts, and notes on adding the pivot tables and slicers below.

Now turn it into an Excel Table: click any cell in the data and press Ctrl+T (Cmd+T on Mac), check "My table has headers" and click OK. Rename it on the Table Design tab, for example to Sales. A Table grows automatically when you paste new rows, which is what makes the dashboard easy to refresh later.

If your export is messy, our guide to AI for Excel data cleanup shows how to fix it quickly.

Step 1: create pivot tables

Pivot tables do the summarising. You will usually want three or four, each answering one question.

1. Click inside your Table, then go to Insert > PivotTable. Choose New Worksheet and click OK.
2. In the PivotTable Fields pane, drag Order Date to Rows and Revenue to Values. Excel groups dates by month automatically in recent versions; if not, right-click a date and choose Group > Months.
3. Rename the sheet to Calc. This sheet holds your workings; the dashboard goes on its own sheet later.
4. Create a second pivot on the same sheet: Product in Rows, Revenue in Values. Sort it largest to smallest.
5. Create a third: Channel in Rows, Revenue in Values.
6. Create a fourth for headline numbers: Revenue and Units in Values, nothing in Rows. This gives you grand totals for the KPI tiles.

Tip: check that each Values field says "Sum of" and not "Count of". If Excel counts instead of sums, a column contains text somewhere. Fix the data, then refresh.

Step 2: add charts

1. Click inside the monthly revenue pivot and go to PivotTable Analyze > PivotChart. Pick a Line chart. Lines are best for trends over time.
2. For the product pivot, insert a Bar chart (horizontal). Bars make rankings easy to read.
3. For the channel pivot, use a Column chart. A pie works only if you have three or four channels; with more, it becomes hard to read.
4. Clean each chart: delete the legend if there is only one series, give it a plain title ("Revenue by month"), and right-click the field buttons to hide them (Hide All Field Buttons on Chart).

Keep colours consistent. One main colour for all charts, with a second colour only for highlights, looks calmer and is easier to read than a rainbow.

Step 3: slicers and timelines

Slicers are the buttons that make the dashboard interactive.

1. Click any pivot and choose PivotTable Analyze > Insert Slicer. Tick Region and Channel.
2. Click any pivot and choose PivotTable Analyze > Insert Timeline. Tick Order Date. A timeline is a date slicer you can drag across months or quarters.
3. Connect each slicer to all your pivots: right-click the slicer, choose Report Connections (or PivotTable Connections), and tick every pivot table. Do the same for the timeline.

Now clicking "West" filters every chart at once. This last step is the one people forget, and without it only one chart responds.

Step 4: lay out the dashboard

1. Add a new sheet called Dashboard.
2. Turn off gridlines: View tab, untick Gridlines.
3. Cut and paste the charts, slicers and timeline from the Calc sheet to the Dashboard sheet. They stay connected.
4. Put KPI tiles across the top (see the next section), the monthly trend under them, and the product and channel charts below.
5. Put the slicers and timeline in a column on the left or a strip along the top.
6. Align everything: select several objects and use Shape Format > Align. Hold Alt while dragging to snap to cell edges.
7. Add a title in cell A1 and a line saying when the data was last updated.

Next month, paste the new rows under your Table and press Data > Refresh All. The pivots, charts and slicers update together.

KPI dashboard in Excel: example

A KPI dashboard in Excel adds big headline numbers above the charts. Here is how to create a KPI dashboard in Excel with four tiles: total revenue, units sold, average order value and revenue vs target.

1. On the Dashboard sheet, merge a block of cells for each tile (for example B3:D5) and give it a light fill.
2. In the tile, reference the totals pivot. The simplest way is to type = and click the grand total cell on the Calc sheet. Excel writes a GETPIVOTDATA formula, which keeps working when the pivot changes shape.
3. For average order value, divide revenue by the number of orders: =GETPIVOTDATA("Revenue",Calc!$A$30)/COUNTA(Sales[Order Date]), adjusting the cell reference to your totals pivot.
4. For revenue vs target, put the monthly target in a cell and show =revenue_cell/target_cell formatted as a percentage.
5. Add conditional formatting so the vs-target tile turns red below 90% and green above 100%.
6. Format numbers large (24 pt or more) with the label small above them.

Note that formulas based on the whole Table, like the COUNTA above, ignore slicers. If you want every tile to respond to slicers, build each tile from a pivot instead. Keep it to seven KPIs or fewer; more turns the dashboard back into a report. Our guide to KPI dashboards covers which KPIs to choose. If formulas are slowing you down, see how to write Excel formulas with AI, or use our free Excel formula generator.

The same approach makes a sales dashboard in Excel: swap the tiles for revenue closed, deals won, win rate and average deal size, and the charts for pipeline by stage and revenue by rep.

Google Sheets version

Here is how to create a dashboard in Google Sheets with the same data. The ideas are the same; the menus differ.

1. Import the data into one sheet with a single header row. Name the sheet Data.
2. Insert pivot tables: select the data, go to Insert > Pivot table, choose a new sheet called Dashboard. Add Order Date to Rows (right-click a date and use Create pivot date group > Month) and Revenue to Values as SUM. Repeat for Product and Channel, choosing Existing sheet and the Dashboard sheet.
3. Add charts: select a pivot and go to Insert > Chart. Use the Chart editor to pick line, bar or column.
4. Add slicers: go to Data > Add a slicer, choose the data range and the column (Region or Channel). A slicer filters the charts and pivot tables on its own sheet that use the same data range, so build the pivots, charts and slicers on one sheet.
5. KPI tiles: use the Scorecard chart type, which shows one big number and can compare it with a baseline.
6. Lay it out on that sheet (call it Dashboard), hide gridlines (View > Show > Gridlines), and share it like any Google Doc.

Sheets has no timeline control like Excel's, so use a slicer on the date column instead. The advantage is sharing: anyone with the link can see the live version. For more on AI inside Sheets, see AI in Google Sheets.

Excel vs Power BI vs AI dashboards

Power BI vs Excel is a common question once a dashboard starts to matter. Here is how the main approaches compare for a small business:

Power BI price checked October 2026 on Microsoft's site.

Where Excel wins: flexibility, formulas, offline use, and it is already on most work computers. You control every cell. Where Power BI wins: when data refreshes from many sources, tables need joining, and different people need different permissions. Our Power BI vs Tableau guide covers that step up. Where an AI dashboard wins: speed, when you just need to see and share the numbers from one export.

The 2-minute shortcut from CSV

If you do not need to learn pivot tables and just want the dashboard, there is a faster route:

1. Save your data as a CSV (File > Save As > CSV) or copy the cells.
2. Open the KissMySkills AI Dashboard.
3. Drop the CSV in, paste the rows, or paste a public Google Sheet link. You can also click "Try sample data" to see how it works first.
4. The tool builds KPI tiles, trends and breakdowns in your browser. Your CSV is not uploaded or stored.
5. Rename the title, add or remove charts, and print to PDF if you need a copy.

The free version works with one table up to 5,000 rows and 1 MB, with no sign-up. With the KissMySkills All-Access subscription ($15 a month or $129 a year) you can save dashboards, share a read-only link and refresh from a Google Sheet. You can also connect the KissMySkills connector to Claude or ChatGPT and ask your agent to build the dashboard from your data.

Be honest with yourself about the trade-off. The AI dashboard is faster, but Excel is more flexible: custom formulas, any layout you like, multiple tables, and no internet needed. If you already have Excel and enjoy building, the tutorial above is the better long-term skill. If you want to get better at Excel with AI help, try using Claude as an Excel tutor.

FAQ

How do I make a dashboard in Excel?

Format your data as an Excel Table, create pivot tables that summarise it (for example revenue by month, by product and by channel), insert a PivotChart for each, add slicers and a timeline connected to all pivots, then move the charts to a separate sheet with gridlines turned off and KPI tiles across the top.

How do I create a KPI dashboard in Excel?

Build the pivot tables and charts first, then add KPI tiles at the top of the dashboard sheet. Each tile is a merged cell block that references a pivot total with GETPIVOTDATA, formatted large, with conditional formatting to show whether it is above or below target. Keep it to seven KPIs or fewer.

How do I create a dashboard in Google Sheets?

Put your data on one sheet, insert pivot tables with Insert > Pivot table, add charts with Insert > Chart, use Scorecard charts for KPI tiles, add filters with Data > Add a slicer, and arrange everything on a separate Dashboard sheet. Share it with a link.

How do I build a dashboard?

Start with the questions the dashboard must answer and pick seven or fewer KPIs. Clean your data into one table, summarise it, choose one chart per question (lines for trends, bars for comparisons, tiles for headline numbers), and lay it out with the most important number top left. Then review it on a regular schedule.

Skip the pivot tables

If you need the dashboard today and the Excel skills can wait, skip the pivot tables: upload your CSV to AI Dashboard. It is free in your browser, and your data stays on your computer.