Pivot Tables and Summarization

The Most Powerful Feature

Pivot tables are the most powerful data analysis feature in Excel. They summarize, analyze, explore, and present your data — all without writing formulas.

What takes hours with formulas takes minutes with pivot tables.

What Pivot Tables Do

From Raw Data to Summary

Raw data: 10,000 rows of sales transactions

Pivot table: Total sales by region, by product, by month — instantly

Dynamic Analysis

Change what you're analyzing with drag-and-drop. Want sales by region? Drag Region to Rows. Want it by product instead? Drag Product to Rows. Want both? Drag both.

Speed

Reorganize your analysis in seconds. Explore your data from multiple angles without rebuilding anything.

Creating Your First Pivot Table

Step 1: Prepare Your Data

Your data needs:

  • Headers in the first row
  • No blank rows or columns
  • Consistent data types in each column
  • No merged cells

Step 2: Create the Pivot Table

  1. Click anywhere in your data
  2. Insert → PivotTable
  3. Choose where to place it (new worksheet recommended)
  4. Click OK

Step 3: Build the Pivot Table

You'll see a blank pivot table and a field list. Drag fields to four areas:

Rows: Categories to show as rows (e.g., Region, Product)

Columns: Categories to show as columns (e.g., Quarter, Status)

Values: Numbers to summarize (e.g., Sum of Sales, Count of Orders)

Filters: Fields to filter the entire pivot table

Example

Data: Sales transactions with Date, Region, Product, Salesperson, Amount

To see sales by region:

  • Drag "Region" to Rows
  • Drag "Amount" to Values

Instantly: Total sales for each region.

To see sales by region AND product:

  • Keep "Region" in Rows
  • Drag "Product" also to Rows (below Region)

Now: Sales broken down by region, then by product within each region.

Pivot Table Techniques

Changing Calculations

By default, numbers are summed. To change:

  1. Click the field in Values
  2. Value Field Settings
  3. Choose: Sum, Count, Average, Max, Min, etc.

Showing Percentages

Value Field Settings → Show Values As:

  • % of Grand Total
  • % of Column Total
  • % of Row Total
  • % Difference from previous

Grouping Dates

Right-click a date in the pivot table → Group:

  • By months, quarters, years
  • Custom date ranges

Filtering

Field filters: Click the dropdown arrow next to a row/column field

Report filter: Drag a field to the Filters area for a dropdown that filters everything

Slicers: Insert → Slicer — visual, clickable filters

Sorting

Click on a row label → Sort options

  • A-Z, Z-A
  • By values (largest first, smallest first)

Refreshing Data

When source data changes, right-click pivot table → Refresh

Or: PivotTable Analyze → Refresh

Pivot Table Design

Layout Options

PivotTable Analyze → Design tab:

  • Compact, Outline, or Tabular layout
  • Subtotals position
  • Grand totals on/off
  • Blank rows between groups

Formatting Numbers

Right-click a value → Number Format

  • Apply currency, percentages, decimal places

Styles

Design tab → PivotTable Styles:

  • Apply pre-built formatting
  • Banded rows/columns

Calculated Fields

Add custom calculations within the pivot table:

PivotTable Analyze → Fields, Items & Sets → Calculated Field

Example: Name: Profit Formula: =Revenue-Cost

Now "Profit" is available as a Values field.

Pivot Charts

Create a chart from your pivot table: PivotTable Analyze → PivotChart

The chart updates when you change the pivot table — dynamic visualization.

Common Pivot Table Issues

"Cannot group that selection"

Probably have text or blanks in your date/number column. Clean your data first.

Numbers not summing

If numbers are stored as text, they'll count instead of sum. Fix the source data.

Duplicate field names

Each field name must be unique. Check your headers.

Slow performance

Very large datasets can be slow. Consider using Power Pivot for millions of rows.

Beyond Basic Pivot Tables

Multiple Consolidation Ranges

Combine data from multiple tables into one pivot table.

Power Pivot

For very large datasets or data from multiple sources. Enables relationships between tables.

GETPIVOTDATA

Extract specific values from a pivot table with a formula: =GETPIVOTDATA("Sales", $A$3, "Region", "East")

AI Prompt: Pivot Table Help

I have data and want to analyze it with a pivot table.

My data has these columns:
[List your columns]

I want to see:
[Describe what summary you want — e.g., "total sales by region by month"]

Help me:
1. Set up the pivot table correctly
2. What to put in Rows, Columns, Values, Filters
3. Any additional settings I should change

What's Next

You can summarize data. Now let's visualize it.

Next chapter: Charts and visualization — present your data clearly and persuasively.

Lookups and ReferencesCharts and Visualization