Microsoft Excel - Pivot Tables

1. Understanding Excel PivotTables

A PivotTable is a tool that summarizes, analyzes, and presents large amounts of data quickly.
It helps you group, filter, total, count, and compare data without writing formulas.

---

2. Creating an Excel PivotTable

1. Select your data range (with headers).
2. Go to Insert → PivotTable.
3. Choose where to place the PivotTable (new or existing worksheet).
4. Drag fields into:
• Rows → categories
• Columns → comparison groups
• Values → totals/counts
• Filters → top-level filter

---

3. Modifying Excel PivotTable Calculations

You can change the calculation in Values:

• Click the field → Value Field Settings
• Choose Sum, Count, Average, Max, Min, % of total, etc.

Useful for switching from SUM to COUNT or AVERAGE easily.

---

4. Grouping PivotTable Data

You can group:

• Dates → months, quarters, years
• Numbers → group into ranges (e.g., 0–100, 101–200)
• Text → manually group selected items

Right-click → Group.

---

5. Formatting PivotTable Data

You can format:

• Numbers (currency, percentage, comma style)
• Layout (Compact/Outline/Tabular)
• Design (PivotTable Styles)
• Remove/Show Grand Totals & Subtotals

Go to PivotTable Design and Layout options.

---

6. Modifying PivotTable Calculations (Repeated Topic)

This includes:

• Changing the summary function
• Calculating % of row, % of column, % of grand total
• Showing difference from another value
• Adding calculated fields (PivotTable Analyze → Fields → Calculated Field)

---

7. Drilling Down into PivotTable Data

To see detailed records:

• Double-click any number in the PivotTable
  Excel creates a new sheet showing the underlying rows that make that number.

---

8. Creating PivotCharts

1. Click anywhere inside the PivotTable
2. Go to Insert → PivotChart
3. Choose chart type (Column, Line, Pie, Bar, etc.)

PivotCharts update automatically when the PivotTable is refreshed.

---

9. Filtering PivotTable Data

You can filter using:

• Filters area (top-level filter)
• Row/Column labels filter (label, value filters)
• Search filters
• Date filters

---

10. Filtering with the Slicer Tool

A Slicer is a visual filter button set.

To add a slicer:

1. Click PivotTable
2. Go to PivotTable Analyze → Insert Slicer
3. Select fields for filtering
4. Click the slicer buttons to filter instantly