Pivot Dashboard — One Interactive Screen from Pivots, Charts & Slicers

A dashboard is a single screen that answers your key questions at a glance. This tutorial brings the whole series together — pivot tables for summaries, pivot charts for visuals, and slicers for interactivity — into one clean, shared dashboard.

Table of Contents

1. Plan Your Dashboard
2. Set Up the Worksheet
3. Add the Pivot Building Blocks
4. Add the Charts
5. Connect the Slicers
6. Polish the Design
7. Keep the Dashboard Up to Date
8. Do It with AI

Plan Your Dashboard

Before touching Excel, decide which questions the dashboard must answer. For the sample sales data, common questions are:

• What is total revenue, and is it growing by month?
• Which region performs best?
• Which products and categories sell most?

Map each question to a component:

Best practice: sketch the layout on paper first — KPIs across the top, charts in a grid below. It saves a lot of rearranging later.

Set Up the Worksheet

A dedicated sheet keeps the dashboard clean and separate from your data and raw pivots.

1. Create a new sheet and name it Dashboard.
2. Hide gridlines: View tab → untick Gridlines.
3. Add a title band: fill a row across the top with a solid color (dark navy works well), white bold text like 2026 Sales Dashboard.
4. If the source data lives on another sheet, this is a good moment to hide that sheet too.

Simple explanation: the dashboard sheet is just a canvas — but hiding the gridlines instantly makes it look like a real dashboard, not a spreadsheet.

Add the Pivot Building Blocks

Each chart needs its own pivot. You can place them on a hidden helper sheet named PivotSource so your dashboard stays clean.

1. Create pivots from the raw data (Tutorial 1).
2. Build one per component:
   • Revenue by month (Date in Rows, Revenue in Values) → for the line chart.
   • Revenue by region (Region in Rows) → for the bar chart.
   • Units by product (Product in Rows, Units in Values) → for the top-products chart.
3. Keep each pivot slim — no unnecessary subtotals, minimal grand totals.

Why separate pivots: one pivot powers one chart. A single overloaded pivot makes its chart cluttered and hard to read.

Add the Charts

Now turn each pivot into a pivot chart (Tutorial 4):

1. Click a pivot → PivotTable Analyze → PivotChart.
2. Choose the matching type — Line for months, Bar for regions, Column for products.
3. Move each chart onto the Dashboard sheet and position it in the layout grid.
4. Hold Alt as you drag to snap charts to the cell grid so they line up perfectly.

Delete the pivots from the dashboard view afterward — the charts keep working because they reference the pivots on the helper sheet.

Note: if a chart suddenly looks empty, the pivot it points to may have been deleted. Keep all pivots on the helper sheet until the dashboard is final.

Connect the Slicers

Interactivity comes from slicers wired across every chart (Tutorials 2 and 4):

1. On the dashboard, go to Insert → Slicer.
2. Pick fields like Region and Product.
3. For each slicer, right-click → Report Connections and tick all the pivots feeding your charts.

Now one click on a region button updates every chart at once. Add a Timeline for the Date field so users can scrub through months.

Why it is useful: report connections are what make a set of charts a dashboard instead of a wall of static pictures — every element answers the same filter together.

Polish the Design

Formatting turns functional into professional:

• Set a consistent chart style from Chart Design → Chart Styles across all charts.
• Turn off chart clutter: remove gridlines, trim legends to one clean line.
• Give every chart a clear title ("Revenue by Month" instead of "Total").
• Match KPI tiles to the title band color.
• Test with a few slicer clicks — nothing should overlap or overflow.

Common mistake: slicing only part of the charts. Test every slicer against every chart; the ones without a connection will stay static and break the illusion.

Keep the Dashboard Up to Date

A dashboard that shows stale numbers is worse than none.

• Refresh everything: Data tab → Refresh All, or Ctrl + Alt + F5.
• Set pivots to refresh on open: right-click each pivot → PivotTable Options → Data → Refresh data when opening the file.
• Add a small note cell: Last updated: =TODAY() so viewers know the data's age at a glance.

Best practice for recurring use: consider a simple refresh macro if the dashboard is opened by less technical teammates. A one-click [Refresh All] button beats hunting through ribbon tabs.

Do It with AI

• ChatGPT: plan the screen before building — prompt Design a sales dashboard layout with total revenue, revenue by month, revenue by region, and top products. Give me the KPI row and chart grid as a text sketch. Use its sketch as your layout map.
• Copilot in Excel: after the dashboard is built, type Add a Slicer for Region. — then still wire it to every chart with Report Connections as shown above. Type Summarize what this dashboard shows. for a quick sanity check of the totals.
• Remember that AI won't refresh your data — you still own Refresh All and the "Last updated" stamp.