Pivot Tables from Basic to Advanced

What a pivot table does

A pivot table groups rows by one or more fields and summarises a number for each group (sum, count, average…). It is the fastest way to answer "how much, by what?" questions, with no formulas.

Creating a pivot table

The pivot cache, Refresh and Refresh All

A pivot table does not read your sheet directly. When you create it, Excel copies the data into a hidden pivot cache, and the pivot reads from that cache. This is why pivots are fast, and also why they do not update automatically when the data changes.

Value field settings

Click a field in Values → Value Field Settings (or right-click a number).

Summarize values by

Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var. Text fields in Values are counted automatically. Distinct Count appears only when the pivot is added to the Data Model (tick "Add this data to the Data Model" when creating it; Lesson 12).

Show values as

This tab turns a plain sum into an analytical view without writing formulas.

Here are three of these views on the Superstore data (computed with pandas to show the exact numbers a pivot would give):

import pandas as pd
s = pd.read_csv("superstore_sales.csv", parse_dates=["order_date"])
pv = s.pivot_table(index="region", columns="category", values="sales", aggfunc="sum")
pct_total = (pv / pv.values.sum() * 100).round(1)
pct_row = (pv.div(pv.sum(axis=1), axis=0) * 100).round(1)
print("Sum of sales (₹):"); print(pv.round(0).astype(int).to_string())
print("\n% of grand total:"); print(pct_total.to_string())
print("\n% of row total (category mix inside each region):"); print(pct_row.to_string())

Sum of sales (₹):
category  Furniture  Office Supplies  Technology
region                                          
East        7942092          2719513    35822514
North       7975868          2219542    38822750
South       9593302          2468888    41890579
West        9330590          2725360    39902570

% of grand total:
category  Furniture  Office Supplies  Technology
region                                          
East            3.9              1.4        17.8
North           4.0              1.1        19.3
South           4.8              1.2        20.8
West            4.6              1.4        19.8

% of row total (category mix inside each region):
category  Furniture  Office Supplies  Technology
region                                          
East           17.1              5.9        77.1
North          16.3              4.5        79.2
South          17.8              4.6        77.6
West           18.0              5.2        76.8

MoM and YoY

Month-on-month (MoM) compares each month with the previous month; year-on-year (YoY) compares a period with the same period last year (which removes seasonality, such as the festive Q4 peak).

m = s.set_index("order_date").resample("MS")["sales"].sum().to_frame("sales")
m["MoM %"] = (m.sales.pct_change() * 100).round(1)
m["YoY %"] = (m.sales.pct_change(12) * 100).round(1)
m.index = m.index.strftime("%b-%Y")
m.tail(8)