Excel Pivot Tables Cheat Sheet — Pivot, Slicer, Calculated Field, Grouping | Dataplexa
Back to Cheat Sheets

Excel Pivot Tables

Create & configure  ·  Slicers  ·  Grouping  ·  Calculated fields  ·  Value settings  ·  Pivot charts

Sheet 2 of 3 Excel 365 Beginner–Inter. Printable

Creating a Pivot Table

step by step
  1. Prepare your data — one header row, no blank rows/columns, no merged cells. Each column = one field.
  2. Select any cell inside the data range (or select the whole range).
  3. Insert → PivotTable (ribbon) or press Alt N V.
  4. Choose location — New Worksheet (recommended) or Existing Worksheet, then click OK.
  5. Drag fields into the four areas in the PivotTable Fields pane on the right.
  6. Refresh when source data changes — right-click pivot → Refresh, or Alt F5.
The 4 Pivot Areas
AreaWhat it doesDrag here
Filters Top-level report filter — shows a dropdown above the pivot Region, Year
Columns Spreads categories across column headers Quarter, Month
Rows Groups data down the left side Product, Name
Values Aggregated numbers in the body Sales, Revenue
Format as Table first: Select your data → Ctrl+T → Insert → PivotTable. A Table auto-expands when you add rows, so the pivot always includes new data after refresh.

Value Field Settings

summarise & show as
Summarise Value By
FunctionReturns
Sum Total of values (default for numbers)
Count Count of entries (default for text)
Average Mean of values
Max / Min Highest / lowest value
Product Multiplies all values together
StdDev Standard deviation (sample)
Var Variance (sample)
Show Values As — % & more
OptionShows
% of Grand Total Each cell as % of overall total
% of Column Total % within each column
% of Row Total % within each row
% of Parent Row % of the subtotal above
Running Total In Cumulative sum down a field
Rank Largest→Small Rank 1 = highest value
Difference From Delta vs a base item
How to access: Right-click any value cell → Value Field Settings. Change both the summarise function and the "Show Values As" display on two separate tabs.

Grouping

dates · numbers · text
Group Dates Automatically
  1. Add a date field to Rows or Columns.
  2. Excel 2016+ auto-groups into Years → Quarters → Months. Expand/collapse with the +/− buttons.
  3. To customise: right-click a date cell → Group → pick Seconds / Minutes / Hours / Days / Months / Quarters / Years.
  4. To ungroup: right-click → Ungroup.
Group Numbers & Text
  1. Numbers — right-click a number row item → Group → set Starting at, Ending at, By (interval, e.g. 10 for age brackets 0–9, 10–19…).
  2. Text / manual — hold Ctrl and click the row items you want to group → right-click → Group. A "Group1" label appears; rename it by typing.
Date grouping requires real dates. If Excel won't group, the date column contains text. Fix with DATEVALUE() or Data → Text to Columns → Date format.

Slicers & Timelines

interactive filtering
Insert a Slicer
  1. Click anywhere inside the pivot table.
  2. Go to PivotTable Analyze → Insert Slicer (or Insert → Slicer).
  3. Tick the field(s) you want — e.g. Region, Category.
  4. Click OK — a floating slicer panel appears.
  5. Click any button on the slicer to filter the pivot instantly.
  6. Hold Ctrl to select multiple slicer items.
  7. Click the × (Clear Filter) icon top-right to reset.
Connect Slicer to Multiple Pivots
  1. Right-click the slicer → Report Connections.
  2. Tick all the pivots you want this slicer to control.
  3. Click OK — one slicer now filters all connected pivots simultaneously.
Insert a Timeline (Date Slicer)
  1. Click the pivot → PivotTable Analyze → Insert Timeline.
  2. Select a date field → OK.
  3. Use the dropdown to switch between Years / Quarters / Months / Days.
  4. Drag the handles to filter a date range.
Slicer Tips
Style a slicer
Click slicer → Slicer tab
→ choose a Slicer Style
→ Slicer Settings to
  rename caption, sort order

Multi-column slicer buttons
Slicer tab → Columns: 3
Resize slicer to fit

Remove a slicer
Click it → Delete key
Slicers work on Tables too — insert a slicer on a formatted Table (Ctrl+T) to filter it without a pivot.

Calculated Fields & Items

custom formulas
Insert a Calculated Field
  1. Click inside the pivot → PivotTable Analyze → Fields, Items & Sets → Calculated Field.
  2. Give it a Name (e.g. Profit Margin).
  3. Build the formula using field names from the list — double-click to insert.
  4. Click Add then OK.
Example Calculated Field Formulas
Profit margin %
= Profit / Revenue

Tax amount
= Revenue * 0.18

Revenue per unit
= Revenue / Units

Bonus flag
= IF(Sales > 10000,
     Sales * 0.05, 0)
Calculated fields use entire column totals — they can't reference individual cells. For complex logic, add a helper column to the source data and refresh the pivot instead.

Sorting, Filtering & Layout

arrange & display
Sorting Pivot Data
Sort by row labels A→Z
Click row label dropdown → Sort A to Z

Sort by values (e.g. highest sales first)
Click value cell → Sort →
Sort Largest to Smallest

Manual drag sort
Hover edge of item cell until
cursor shows 4-arrow → drag
Layout Options
LayoutBest for
Compact Default — all row fields in one column. Saves space.
Outline Each row field in its own column, with subtotals above.
Tabular Classic table look — easiest to copy/paste out. No indent.
Change layout: PivotTable Analyze → Design → Report Layout → choose Tabular for the most copy-friendly format.

Pivot Charts

visualise pivot data
Insert a Pivot Chart
  1. Click inside the pivot table.
  2. PivotTable Analyze → PivotChart (or Insert → PivotChart).
  3. Choose chart type — Column, Bar, Line, Pie, etc. → OK.
  4. The chart links live to the pivot — filter the pivot and the chart updates instantly.
  5. Slicers connected to the pivot also filter the chart automatically.
Chart Type Guide
ChartUse when…
Clustered Column Compare categories side by side
Stacked Bar Show part-to-whole across categories
Line Trend over time
Pie / Doughnut Single series composition (<6 slices)
Combo Two metrics with different scales
Treemap Hierarchical part-to-whole
Waterfall Running total, gains & losses
Hide Chart Field Buttons
Field buttons clutter the chart
— hide them for presentations

Right-click a field button
→ Hide All Field Buttons
   on Chart

Re-show:
PivotChart Analyze tab
→ Field Buttons → Show All

Move chart to its own sheet
Right-click chart border
→ Move Chart → New sheet

Essential Pivot Keyboard Shortcuts

speed up workflow

Create & Refresh

Alt N V Insert PivotTable
Alt F5 Refresh pivot
Ctrl Alt F5 Refresh all
Alt F4 Close dialog

Navigate & Select

Tab Move to next field
Enter Expand/collapse
← → Collapse/expand group
Ctrl * Select entire pivot

Format & Group

Alt Shift → Group selection
Alt Shift ← Ungroup selection
Ctrl T Format as Table
Ctrl 1 Format cells dialog

Common Mistakes & Best Practices

troubleshoot
Data Prep Mistakes
✗ Blank rows or columns in data
✗ Merged cells in the header
✗ Numbers stored as text (left-aligned)
✗ Dates stored as text strings
✗ Duplicate/inconsistent labels
   ("North" vs "north" vs "North ")

✓ Clean header in row 1
✓ No blank rows between data
✓ Consistent text case (PROPER/TRIM)
✓ Format as Table (Ctrl+T) first
Pivot Doesn't Update?
New rows added to source data
→ Right-click → Refresh
→ Or: if source is a Table,
   it expands automatically

Source range changed
→ PivotTable Analyze
   → Change Data Source
   → update the range

Grand totals wrong
→ Design → Grand Totals
   → On for Rows and Columns
Power Tips
Show items with no data
Right-click row item → Field
Settings → Layout → Show
items with no data ✓

Repeat item labels
Design → Report Layout
→ Repeat All Item Labels

Custom number format
Right-click value → Number
Format → Accounting / Custom

Preserve formatting on refresh
PivotTable Options → Layout
→ Preserve cell formatting ✓

Pivot Tables Mastery Checklist

sheet 2 complete
Build & ConfigureKey point
Create a pivot from a Table Ctrl+T first
Use all 4 areas correctly Filters/Cols/Rows/Values
Change summarise function Value Field Settings
Show values as % % of Grand Total
Filter & GroupKey point
Add interactive filter button Insert Slicer
Connect slicer to many pivots Report Connections
Filter by date range visually Insert Timeline
Group dates into months/quarters Right-click → Group
AdvancedKey point
Add a custom formula to pivot Calculated Field
Create a linked chart PivotChart
Refresh data after update Alt + F5
Fix data not updating Change Data Source
Next up → Sheet 3: Excel Charts  ·  bar · line · scatter · sparkline · combo — create professional charts, format axes, add trendlines, and build dashboards.
Back to Cheat Sheets