📊 Pivot Tables
Pivot tables are one of the most powerful features in any spreadsheet. They turn hundreds of rows of raw data into a clean, interactive summary — without writing a single formula. This module shows you exactly how.
Open the 20-row sales spreadsheet from Module 04 (or re-enter it). Pivot tables work best with clean, structured data — consistent headers, no merged cells, no blank rows in the middle. Your Module 04 dataset is already in good shape.
You'll also build a 30-row extended dataset below for the main exercise.
Imagine you have 500 rows of sales data — Date, Region, Category, Revenue. A pivot table lets you instantly answer questions like:
- "What's the total revenue for each Region?"
- "How many transactions happened in each Category per month?"
- "What's the average order value by Salesperson?"
Without a pivot table, you'd write multiple SUMIF and COUNTIF formulas — one per combination you want to analyze. With a pivot table, you drag-and-drop fields and the summary builds itself in seconds.
The four pivot table zones:
↓ ROWS
The categories you want to group by vertically. Each unique value becomes a row. Example: one row per Region.
→ COLUMNS
A second grouping across the top. Each unique value becomes a column. Example: one column per Category.
✍ VALUES
The numbers to summarize. You choose: SUM, COUNT, AVERAGE, etc. Example: SUM of Revenue.
🔍 FILTERS
An optional filter applied to the whole pivot table. Example: show only Q1 data.
| Date | Region | Category | Revenue |
|---|---|---|---|
| 2024-01-05 | North | Electronics | 1200 |
| 2024-01-08 | South | Books | 350 |
| 2024-01-12 | East | Clothing | 445 |
| 2024-01-15 | West | Electronics | 1350 |
| 2024-01-20 | North | Electronics | 960 |
| 2024-02-03 | South | Books | 540 |
| 2024-02-07 | East | Electronics | 750 |
| 2024-02-14 | West | Clothing | 660 |
| 2024-02-18 | North | Books | 245 |
| 2024-02-25 | South | Electronics | 450 |
| 2024-03-01 | East | Electronics | 790 |
| 2024-03-05 | West | Clothing | 400 |
| 2024-03-10 | North | Electronics | 388 |
| 2024-03-15 | South | Clothing | 270 |
| 2024-03-20 | East | Books | 280 |
| 2024-03-25 | West | Electronics | 360 |
| 2024-04-02 | North | Clothing | 320 |
| 2024-04-08 | South | Electronics | 1500 |
| 2024-04-14 | East | Books | 525 |
| 2024-04-20 | West | Electronics | 600 |
| 2024-05-03 | North | Electronics | 880 |
| 2024-05-08 | South | Clothing | 390 |
| 2024-05-14 | East | Electronics | 720 |
| 2024-05-20 | West | Books | 175 |
| 2024-05-25 | North | Books | 210 |
| 2024-06-01 | South | Electronics | 1100 |
| 2024-06-07 | East | Clothing | 330 |
| 2024-06-14 | West | Electronics | 970 |
| 2024-06-20 | North | Clothing | 440 |
| 2024-06-25 | South | Books | 295 |
- Click any cell inside your data table (e.g., A1).
- Go to Insert → Pivot table. A dialog appears asking where to put the pivot table.
- Choose "New sheet" — this keeps your raw data clean and your pivot table on a separate sheet. Click Create.
- A blank pivot table appears on the new sheet, with a Pivot table editor panel on the right side.
- In the editor, click "Add" next to Rows and select Region. You'll now see North, South, East, West as row labels.
- Click "Add" next to Values and select Revenue. Choose "Summarize by: SUM". Your pivot table now shows total revenue per region.
- To add a second dimension, click "Add" next to Columns and select Category. Now you see revenue broken down by both Region AND Category in a cross-tab.
What you should see:
Pivot Table: SUM of Revenue by Region × Category
| Region | Books | Clothing | Electronics | Grand Total |
|---|---|---|---|---|
| East | 805 | 775 | 2260 | 3840 |
| North | 455 | 760 | 3428 | 4643 |
| South | 1185 | 660 | 3400 | 5245 |
| West | 175 | 1060 | 2280 | 3515 |
| Grand Total | 2620 | 3255 | 11368 | 17243 |
When you add a Date field to Rows, you'll see individual dates — not very useful. You can group them by month, quarter, or year.
- In the Pivot table editor, remove Region from Rows. Add Date to Rows instead.
- In the Rows section of the editor, you'll see an option to "Group by" — set it to Month (or Quarter, Year).
- Now your pivot shows total revenue per month — a time series summary.
- In Excel: right-click any date cell in the pivot table → Group → select Months, Quarters, Years. Check multiple boxes for nested grouping (e.g., by Year then Month).
Slicers are visual filter buttons that sit on your sheet. Instead of using the pivot table's built-in filter dropdown, slicers let you click a button to instantly filter the pivot. They make your spreadsheet feel interactive — like a basic dashboard.
- Click anywhere inside your pivot table.
- Go to Data → Add a slicer.
- In the slicer panel, choose the column to filter by — for example, Category.
- A slicer widget appears on your sheet showing buttons for Electronics, Books, and Clothing.
- Click "Electronics" — the pivot table instantly filters to show only Electronics data. Click again to deselect.
- You can have multiple slicers on the same sheet. Add one for Region too.
By default, the Values area uses SUM. But you can change it:
- In the Pivot table editor, click on "Revenue" in the Values section.
- Change "Summarize by" from SUM to COUNT — now you see how many transactions per region/category, not total revenue.
- Change it to AVERAGE — now you see the average revenue per transaction.
- You can add the same field multiple times with different summarizations. Add Revenue twice: once as SUM, once as COUNT. Now you can see both totals and transaction counts side by side.
Using the 30-row dataset above:
- Create a pivot table on a new sheet showing Revenue by Region (Rows = Region, Values = SUM of Revenue). Which region has the highest total revenue?
- Add Category to Columns to see a breakdown by both Region and Category. Which Region-Category combination has the highest revenue?
- Add a slicer for Region. Click "North" — what's the total revenue for the North region alone?
- Create a second pivot table on a new sheet with: Rows = Date (grouped by Month), Values = SUM of Revenue. What month had the highest revenue?
- In the second pivot table, change the Values summarization from SUM to COUNT. What does this tell you? (How many transactions happened per month.)
The word "pivot" in pivot table comes from the idea of rotating your data — taking what was in rows and moving it to columns, or vice versa. The original concept was developed at Lotus Development in the late 1980s and brought to Excel by Pito Salas in 1994. The "Pivot Table" feature in Excel 5.0 was considered so innovative that it's cited as one of the reasons Excel surpassed Lotus 1-2-3 in market share.
Google Sheets added pivot tables in 2011. Today, analysts use them to summarize millions of rows in seconds — a task that would take hours with manual formulas.
- Pivot tables summarize large datasets instantly — no formulas required.
- The four zones are: Rows (vertical groups), Columns (horizontal groups), Values (numbers to summarize), Filters (optional whole-table filter).
- Create one via Insert → Pivot table → New sheet.
- Date fields can be grouped by month, quarter, or year for time-series analysis.
- Slicers are interactive filter buttons — great for making spreadsheets feel like dashboards.
- Change "Summarize by" to SUM, COUNT, or AVERAGE depending on what question you're answering.
- Your raw data and your pivot table should be on separate sheets — never edit the pivot table directly.