Excel Course
PivotTables & PivotCharts
PivotTables are the analytical engine of Excel dashboards. They summarise thousands of rows into a compact, rearrangeable summary in seconds and when connected to a Power Pivot data model, they become the interface for every measure and relationship you have built. PivotCharts extend that power visually: a chart directly bound to a PivotTable that responds to the same slicers and updates automatically when data changes. This lesson covers PivotTable layout, grouping, value display options, PivotChart creation, and the formatting steps that make both dashboard-ready.
Creating a PivotTable From the Data Model
There are two types of PivotTable in Excel: one sourced from a worksheet range or Table, and one sourced from the Power Pivot data model. For dashboard work, you almost always want the data model version. It gives you access to all your DAX measures, relationships across multiple tables, and better performance on large datasets.
Creating a PivotTable from the Data Model:
Insert → PivotTable
In the dialog: select "Use this workbook's Data Model"
Choose location → OK
The field list shows all model tables and their columns.
Expand a table to see its fields.
Measures appear with a calculator icon (Σ) at the bottom of each table.
PivotTable areas:
Filters → whole-PivotTable filter (appears as a dropdown above the grid)
Columns → column headers (creates a matrix layout)
Rows → row headers (the most common area)
Values → the numbers (always use measures here, not raw columns)
Quick layout tips:
Drag a dimension column to Rows → one row per unique value
Drag a measure to Values → aggregated number per row
Drag a second dimension to Columns → cross-tabulation matrix
Drag a field to Filters → adds a "Report Filter" dropdown above the tablePivotTable Layout Options
Excel offers three PivotTable layout styles that control how row labels are displayed. The choice affects readability significantly, especially when multiple fields are nested in the rows area.
Changing layout:
PivotTable Design tab → Layout → Report Layout
→ Show in Compact Form (default)
→ Show in Outline Form
→ Show in Tabular Form (recommended for dashboards)
Repeat item labels (for Tabular form):
PivotTable Design → Report Layout → Repeat All Item Labels
Remove subtotals:
PivotTable Design → Subtotals → Do Not Show Subtotals
Remove Grand Total row:
PivotTable Design → Grand Totals → Off for Rows and Columns
Remove expand/collapse buttons:
PivotTable Analyze → Show → +/- Buttons → untickTabular form with repeated labels and subtotals removed is the cleanest configuration for a dashboard PivotTable. It reads like a normal data table, which makes it easy for users unfamiliar with PivotTable conventions to understand at a glance.
Grouping in PivotTables
PivotTable grouping lets you automatically roll up date or number fields into larger periods. It is the quickest way to create a time hierarchy without adding calculated columns to the data model.
Grouping date fields:
Right-click any date value in the rows area → Group
Select grouping intervals: Days, Months, Quarters, Years
Multiple selections allowed — Excel creates a hierarchy
Example: selecting Months + Years gives:
Year field at the top level → expand to see Months
To ungroup: right-click → Ungroup
Grouping number fields (creating value bands):
Right-click a number in the rows area → Group
Starting at: 0 · Ending at: 50000 · By: 5000
Creates bands: 0–4999, 5000–9999, 10000–14999 etc
Custom text grouping (manual):
Ctrl+click multiple row items to select them
Right-click → Group
Rename the group by clicking the "Group1" label
Example: combine "North" and "East" into "Northern Regions"| Year | Quarter | Total Revenue |
|---|---|---|
| 2025 | Q1 | £436,000 |
| Q2 | £412,000 | |
| Q3 | £448,000 | |
| Q4 | £324,000 | |
| 2025 Total | £1,620,000 |
Value Display Options — Show Values As
Instead of writing a DAX measure for every percentage and ranking, PivotTables have built-in Show Values As options that transform the displayed values on the fly. These are quick alternatives for simple percentage-of-total, running total, and rank calculations directly in the PivotTable.
Applying Show Values As:
Right-click any value cell → Show Values As
Or: click value field dropdown in field list → Value Field Settings → Show Values As tab
Key options:
% of Grand Total → each value as % of the overall total
% of Column Total → each value as % of its column total
% of Row Total → each value as % of its row total
Running Total In → cumulative sum down the rows
Rank Largest to Smallest → dynamic rank number (1 = highest)
% Difference From → % change vs a base item (e.g. prior month)
Example — show revenue value and % share in same table:
Drag Total Revenue to Values twice
Right-click the second instance → Show Values As → % of Grand Total
Double-click the header → rename to "Revenue Share %"
Result: £ column and % column side by side from one source measureShow Values As is a quick and useful tool but with one key limitation: the transformation is visual only and cannot be referenced by formulas, charts, or KPI cards elsewhere in the workbook. When another visual depends on the percentage value, write a proper DAX measure instead. Use Show Values As only when the percentage is needed for display in the table itself.
PivotCharts — Charts Bound to PivotTables
A PivotChart is directly linked to a PivotTable. When the PivotTable changes because of a slicer, a filter, or a data refresh, the chart changes automatically. The most important setup step after creating a PivotChart is hiding the filter buttons that appear on the chart face by default.
Creating a PivotChart from an existing PivotTable:
Click inside the PivotTable
PivotTable Analyze tab → Tools → PivotChart
Choose chart type → OK
Creating a standalone PivotChart from the data model:
Insert → PivotChart → Use this workbook's Data Model
Creates a PivotChart with its own independent backing PivotTable
Hiding PivotChart filter buttons (always do this):
Click the PivotChart → PivotChart Analyze tab → Show/Hide → Field Buttons → Hide All
Filtering should always come from slicers, not the chart face dropdowns
Moving to dashboard sheet:
Right-click the chart → Move Chart → Object in → Dashboard sheet → OK
Resize using Alt+drag to snap to cell grid
Auto-refresh on file open:
Right-click any PivotTable → PivotTable Options → Data tab
Tick "Refresh data when opening the file"
Dashboard updates automatically every time it is openedConnecting PivotCharts to Slicers
PivotCharts respond to slicers through the same Report Connections mechanism as PivotTables. Connect a slicer to a PivotChart's backing PivotTable and the chart filters automatically whenever the slicer is clicked.
Connecting a slicer to a PivotChart:
Right-click the slicer → Report Connections
PivotCharts appear in the list alongside PivotTables — tick to connect
Refreshing all PivotTables and PivotCharts:
Data tab → Refresh All
All connected PivotCharts update automatically when their backing
PivotTable refreshes — no separate chart refresh needed
Checking which PivotTable backs a PivotChart:
Click the PivotChart → PivotChart Analyze → Data → Change Data Source
Shows the backing data source — useful when troubleshooting why
a slicer is not filtering a chart as expectedSetting auto-refresh on open is a small but high-value detail for recurring dashboard recipients. Instead of remembering to click Refresh All each time they open the file, they see current data immediately. Combine this with a Power Automate flow that updates the source data on a schedule and you have a fully automated, zero-touch reporting pipeline.
🠐 Practice
Q1. You have a PivotTable with OrderDate in rows showing individual dates. You want to see quarterly subtotals for 2024 and 2025 instead. How do you do this?
Q2. You want to show both the revenue value and revenue as a % of grand total side by side in the PivotTable, using only one measure. How do you do this without writing a DAX percentage measure?
Q3. After creating a PivotChart for a dashboard, what is the first step you should always take?
🟣 Quiz
Q1. What is the key difference between Compact form and Tabular form in a PivotTable layout?
Q2. When should you use Show Values As instead of a DAX measure for a percentage calculation?
Q3. You click a Region slicer and the PivotTable updates but the PivotChart does not change. What is the most likely cause?
Next up — Full Dashboard Project, where we bring everything from Section VI together and build a complete end-to-end Sales Performance Dashboard from raw data through Power Query, Power Pivot, DAX measures, PivotTables, PivotCharts, slicers, and a polished visual layout.