Today's Goals

  • Insert a pivot table from a detail sheet
  • Understand Rows, Columns, Values and Filters
  • Switch a value field between Sum, Count and Average
  • Refresh after the source data changes

Key Concepts

1. What problem it solves

A thousand rows of sales details, wanted by region, by salesperson, by month. Formulas would need SUMIF chains; a pivot table needs none — drag fields, get summaries on the spot. Together with VLOOKUP it forms the backbone of Excel data work: one fetches, one aggregates.

2. Three rules for the source

  • Row 1 holds headers; one field per column, no duplicate names
  • No merged cells, no fully blank rows inside the range
  • One record per row; no subtotal rows (they double the totals)

Clean the source first — that single habit prevents most beginner trouble.

3. The four areas

Select the data → Insert → PivotTable, then drag fields in the pane:

  • Rows: the category, e.g. Region
  • Columns: a second dimension, e.g. Quarter (may stay empty)
  • Values: the number, e.g. Sales, summed by default
  • Filters: an overall filter, e.g. Year

One field may serve several areas. Click "Value Field Settings" on a value field to switch to Count, Average or Max.

4. Refresh and extend

Editing the source does not move the pivot: right-click → Refresh (Alt+F5). After appending rows, use Change Data Source — or format the source as a table with Ctrl+T first, so new rows join the range automatically.

Today's Code

No formulas here; the layout in one diagram:

Source: 1000 rows (Date/Region/Rep/Sales)
        │ Insert PivotTable
        ├─ Rows: Region
        ├─ Columns: (empty)
        ├─ Values: Sales (Sum)  → one total per region
        └─ Filter: Year

Swap Region for Rep and it sums by person; drag Rep into both Rows and Values (as Count) and you have order counts per rep. Changing the layout costs one drag — that is why pivots beat formula chains.

Exercises

  1. Total sales per region.
  2. Add Quarter as columns for a region × quarter cross table.
  3. Count orders per salesperson — not amounts.

Exercise Solutions

  1. Rows: Region; Values: Sales. Default Sum is the total.
  2. Drag Quarter into Columns; keep Rows and Values as before.
  3. Rows: Rep; Values: OrderID with the field set to Count — counting text counts non-empty cells.

Real-World Scenario

Operations receives a 3000-row channel log monthly and owes the boss one page: sales per channel, orders per channel, the top channel. Three pivots on one sheet: ① Rows=Channel, Values=Sales; ② Rows=Channel, Values=OrderID as Count; ③ a copy of ① sorted descending, read the first row. Monthly routine: paste the new log, press Alt+F5 three times, page done — faster to build and easier to maintain than thirty SUMIFs.

Common Errors and Troubleshooting

Symptom Cause Fix
"Invalid field name" Blank headers or merged cells in source Fill the headers, unmerge
New rows missing Source range not extended Change Data Source, or Ctrl+T the source
Totals oddly doubled Subtotal rows inside the source Delete them, then refresh
Counted instead of summed Text mixed into the value column Clean the column, set the field back to Sum
Field list gone Clicked outside the pivot Click any cell inside it

Extension Exercises

  1. Drag Sales into Values twice — one Sum, one Average — and compare what each column tells you.
  2. Design → Report Layout → Show in Tabular Form; compare with Compact for printing and screenshots.

Summary

Three moves: clean the source, insert and drag, refresh after changes. Rows categorize, Columns cross, Values aggregate, Filters scope. Text sneaking into a value field flips Sum into Count — the most common surprise. Next lesson: automatic color alarms with conditional formatting.

From Online Toolbox (www.vba.net)