A dashboard puts the numbers that matter on one screen: total sales, the trend over time, the best products and regions. You do not need special software; Excel’s pivot tables, charts and slicers are enough. This guide builds a simple sales dashboard step by step.
Quick answer
- Turn your data into a Table (Ctrl + T).
- Create a few pivot tables on a separate sheet: sales by month, by product and by region.
- Add a pivot chart for each one and place them on a Dashboard sheet with KPI tiles at the top.
- Add slicers to filter everything at once, then use Data → Refresh All when new data arrives.
What we will build
- 3 KPI tiles: total sales, number of orders, average order value
- A line chart: sales by month
- A bar chart: sales by product
- A column chart: sales by region
- Slicers: filter by region and salesperson
Step 1: Prepare the data
Put your raw data on a sheet called Data, with one row per sale and one header row:
| Date | Region | Salesperson | Product | Sales |
|---|---|---|---|---|
| 05/01/2026 | North | Sara | Laptop | 650 |
| 08/01/2026 | South | Omar | Monitor | 210 |
| 03/02/2026 | East | Lina | Laptop | 720 |
Click inside the data and press Ctrl + T to make it a Table. Name it Sales under Table Design → Table Name. New rows added to the table will flow into the dashboard automatically.
Step 2: Create the pivot tables
Add a sheet called Calc for the working parts. For each pivot table, click inside the Sales table and choose Insert → PivotTable → Existing Worksheet, then pick a cell on Calc.
- Pivot 1, sales by month: Date in Rows (group by Months and Years), Sales in Values.
- Pivot 2, sales by product: Product in Rows, Sales in Values. Sort largest to smallest.
- Pivot 3, sales by region: Region in Rows, Sales in Values.
- Pivot 4, KPI numbers: Sales in Values twice: once as Sum and once as Count (number of orders).
Leave a few empty columns between pivot tables so they do not overlap when they grow. New to pivot tables? Read How to make a pivot table in Excel first.
Step 3: Add the charts
- Click inside Pivot 1 and choose PivotTable Analyze → PivotChart → Line.
- Do the same for Pivot 2 (Bar) and Pivot 3 (Column).
- Cut each chart (Ctrl + X) and paste it on a new sheet called Dashboard.
- Clean them up: give each a clear title, remove the legend if there is only one series, and right-click a chart → Hide All Field Buttons.
Step 4: Build the KPI tiles
At the top of the Dashboard sheet, make three tiles. Merge a few cells for each, give them a light fill colour, and link them to the KPI pivot table with a simple formula, for example =Calc!B4.
A more robust option is GETPIVOTDATA, which Excel writes for you if you type = and click a cell inside a pivot table. For the average order value, divide total sales by the number of orders:
=IFERROR(Calc!B4/Calc!C4, 0)
Format the tiles with a large, bold font and a short label above each number, such as Total sales.
Step 5: Add slicers
- Click any pivot chart, then choose PivotChart Analyze → Insert Slicer.
- Tick Region and Salesperson.
- Right-click each slicer → Report Connections and tick all the pivot tables, so one click filters the whole dashboard.
For a date filter, use Insert Timeline and choose the Date field.
Step 6: Make it look clean
- Turn off gridlines: View → uncheck Gridlines.
- Use one or two colours only. Highlight the most important number with your accent colour.
- Line up charts with the Alt key held down while dragging (they snap to cells).
- Put the most important information top left, where people look first.
Step 7: Update it
Paste new rows at the bottom of the Sales table, then click Data → Refresh All (Ctrl + Alt + F5). Every pivot table, chart and tile updates. If your data comes from an export, load it with Power Query so the whole dashboard refreshes in one click.
Common mistakes
- Too many charts: 3–5 visuals that answer real questions beat 12 that nobody reads.
- Slicers that only filter one chart: connect them to every pivot table with Report Connections.
- Pie charts with many slices: use a sorted bar chart instead; it is much easier to read.
- Forgetting to refresh: pivot tables do not update by themselves.
FAQ
Can I build a dashboard in Google Sheets?
Yes. Use pivot tables, charts and slicers in Google Sheets the same way. Some formatting options are simpler.
Should I use Power BI instead?
For a few charts that you update monthly, Excel is enough. Power BI is better for large data, many sources and sharing interactive reports with a team.