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
  • 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