Pivot Tables in Excel: Fast, Practical Guide

What You’ll Do

• Prepare data
• Insert a Pivot Table
• Drag fields into Rows, Columns, Values, Filters
• Change how numbers are summarized
• Sort, filter, and format
• Add charts and slicers
• Refresh when data changes

---

Prepare Data for a Pivot Table

Your data should:

• Be in a clean table format (no blank rows/columns)
• Have headers in the first row
• Contain consistent data types per column

Optional but recommended: Convert to a Table
Insert → Table

This prevents range issues when new data is added.

---

Insert a Pivot Table

Insert → PivotTable

In the dialog:

• Select the table/range
• Choose New Worksheet or Existing Worksheet

Click OK.

---

Understand the PivotTable Fields Pane

• Appears on the right when you click inside the pivot area
• Disappears when you click outside
• Comes back when you click inside again

You’ll drag fields into:

• Rows
• Columns
• Values
• Filters

---

Analyze Data with Rows, Columns, and Values

Drag columns from your table into the areas.

Rule of thumb:
Anything you want calculated → goes into Values

By default, numeric fields become Sum of …

---

Change How Values Are Calculated

Right-click any value cell → Summarize Values By

Options:

• Sum
• Count
• Average
• Max / Min
• Product

---

Sort Data

Right-click a number (e.g., Sum of Sales) → Sort

• Smallest to Largest
• Largest to Smallest

---

Use Rows vs Columns for Dimensions (e.g., Region)

Drag a field like Region into:

• Columns → creates separate columns per region
• Rows → creates sub-rows under the main row

Reorder fields in Rows to change hierarchy.

To remove a field → drag it out.

---

Use Filters

Drag a field into Filters.

A dropdown appears above the pivot table.

• Enable the checkbox at the bottom to select multiple items.

---

Show Values As (Percentages, etc.)

Drag the same field into Values again (so it appears twice).

Right-click the second one → Show Values As

Example:

• % of Column Total

Now you have both Sum and %.

---

Adjust Look and Feel

• Change style/colors: Design tab
• Remove grand totals:
  Design → Grand Totals → Off for Rows and Columns
• Rename column headers directly

---

Pivot Charts and Slicers

Create chart: Insert → Chart
Charts stay linked to the pivot table.

Add slicer: PivotChart Analyze → Insert Slicer

---

Refresh When Data Changes

Pivot tables do not auto-update.

After adding data:
PivotChart Analyze → Refresh

If you used an Excel Table, the new rows are included after refresh.