Pivot table learning path

Excel Pivot Tables Guide

Learn how to summarize, filter, and analyze large Excel datasets with Pivot Tables, calculated fields, grouping, and refresh best practices.

Start with a clean source table

  • Use one header row, no blank rows inside the dataset, and consistent column names.
  • Convert the source range to an Excel Table so new rows are included when the Pivot Table refreshes.
  • Place text fields in Rows or Columns and numeric fields in Values for most summary reports.

Common Pivot Table fixes

  • Refresh the Pivot Table after the source data changes.
  • Use grouping for dates and numbers when you need monthly or range-based summaries.
  • Use calculated fields or SUMIFS when the metric is not already in the source data.

Pivot table setup workflow

  • Convert source to Excel Table (Ctrl+T) before Insert Pivot — new rows then appear on Refresh.
  • Remove blank rows and columns from source; blanks create silent gaps in grouped fields.
  • Use the same cleaned Table for SUMIFS reconciliation and Pivot reporting.
  • Archive monthly Pivot exports as values-only copies before restructuring source columns.

Recommended next steps

  • Convert source data to an Excel Table before creating a Pivot so new rows appear on refresh.
  • Use SUMIFS to reconcile Pivot totals when numbers are stored as text or filters differ between views.
  • Calculated fields vs calculated items — use fields for row-level math, items for Pivot-internal subtotals.
  • Read pivot-table-mistakes blog post for the twelve most common audit failures in reporting workbooks.

Pivot audit checklist

  • Refresh All after source update; confirm row count matches Table before presenting totals.
  • Check Values field summarization — SUM vs COUNT — when numbers look too small or too large.
  • Verify filters and slicers match the SUMIFS criteria used in parallel audit formulas.
  • Expand/collapse groups before printing; hidden rows still affect totals but confuse readers.

People also ask

  • Why does Pivot total not match SUM? Usually text numbers, stale source range, or different filters.
  • Calculated field or item? Fields for row math on source columns; items for operations inside the Pivot layout.
  • Pivot not updating? Source not a Table, or refresh not run — see pivot-table-not-updating problem guide.

Deep-dive function map for Excel Pivot Tables Guide

  • SUBTOTAL (/functions/subtotal/): Returns a subtotal in a list or database, with options to include or exclude hidden values. Example: =SUBTOTAL(9, A1:A100).
  • SUMIFS (/functions/sumifs/): Adds cells that meet multiple criteria across multiple ranges. Example: =SUMIFS(C:C, A:A, "East", B:B, ">1000").
  • COUNTIFS (/functions/countifs/): Counts cells that meet multiple criteria across multiple ranges. Example: =COUNTIFS(A:A, "East", B:B, ">1000").
  • UNIQUE (/functions/unique/): Returns a list of unique values from a range or array. Example: =UNIQUE(A2:A100).
  • SORT (/functions/sort/): Sorts the contents of a range or array in ascending or descending order. Example: =SORT(A2:B10).

Two-week study plan

  • Week 1 — Read each related function page and copy one example per function into a practice Table.
  • Week 2 — Apply this guide to a real export from your job; document fixes in a changelog tab.
  • Explore categories: Math & Trig, Statistical.
  • Run the fix-excel-formula-errors checklist when any formula shows #N/A, #VALUE!, #REF!, or #SPILL!.
  • Compare similar functions (VLOOKUP vs XLOOKUP, SUMIF vs SUMIFS) before standardizing team templates.
  • Export a PDF snapshot of your completed practice workbook for future reference and onboarding.

Error and troubleshooting cross-links

  • Fix #REF! (/errors/ref/) — step-by-step causes and solutions in the error directory.
  • Fix #NAME? (/errors/name?/) — step-by-step causes and solutions in the error directory.