Excel

Pivot Table

An interactive tool that summarizes large datasets by reorganizing and aggregating data based on selected fields, allowing users to analyze information without writing formulas.

Examples

Summarizing total sales by region and product category
Counting orders by month and customer segment
Calculating average order value by sales representative

Pivot Table

A Pivot Table is a powerful data summarization tool in Excel and Google Sheets that allows you to reorganize and aggregate data without writing formulas. It's one of the most important features for data analysis.

What Pivot Tables Do

Pivot tables take large, detailed datasets and summarize them by:

  • Grouping data by categories (rows and columns)
  • Calculating totals, averages, counts, and other aggregations
  • Allowing interactive filtering and drilling down
  • Reorganizing data views on the fly

Anatomy of a Pivot Table

Four Field Areas

Area Purpose Example
Rows Categories down the left side Regions, Products, Dates
Columns Categories across the top Months, Departments
Values Numbers being calculated Sum of Sales, Count of Orders
Filters Filter the entire report Year, Status

Creating a Pivot Table

Excel

  1. Select your data (including headers)
  2. Insert → PivotTable
  3. Choose where to place it
  4. Drag fields to the areas

Google Sheets

  1. Select your data
  2. Insert → Pivot table
  3. Choose new sheet or existing
  4. Use the Pivot table editor panel

Common Operations

Change Calculation Type

Right-click a value → Value Field Settings → Choose Sum, Count, Average, Max, Min, etc.

Group Dates

Right-click a date field → Group → Choose Months, Quarters, Years

Sort and Filter

Use the dropdown arrows on row/column labels

Show Values As

Display as % of total, difference from, running total, etc.

Example Use Cases

  1. Sales Analysis: Total sales by region, broken down by product category
  2. HR Reporting: Employee count by department and job level
  3. Financial Summary: Monthly expenses by category
  4. Customer Analysis: Average order value by customer segment

Pivot Table Tips

  1. Source data requirements: Headers in first row, no blank rows/columns
  2. Refresh: Right-click → Refresh when source data changes
  3. Slicers: Add visual filters for easier interaction
  4. Pivot Charts: Visualize pivot table data automatically
  5. Calculated fields: Create custom calculations within the pivot

Limitations

  • Cannot directly edit values (they're calculated)
  • Source data must be clean and structured
  • Large datasets may be slow to refresh
  • Requires re-creation if source structure changes significantly

Best Practices

  1. Use Tables (Ctrl+T) as your source—they auto-expand
  2. Name your pivot tables descriptively
  3. Create on a new sheet for cleaner layouts
  4. Use slicers instead of filters for dashboards
  5. Document your pivot table settings