Pivot Tables for Data Analysis | Free Data Analytics Course

What you'll learn

What a pivot table does and the four areas that control it: rows, columns, values and filters.
How to build one step by step in Excel and in Google Sheets.
How to switch between sum, count and average, and show values as a percentage of the total, row or column.
How to group dates into quarters and months, and add slicers and calculated fields.
The pivot table mistakes that produce wrong numbers without any error message.

📥 Practice this lesson

Build every pivot table in this lesson from the Sales Clean sheet. Compare your totals with the Summary sheet, which calculates the same breakdowns with SUMIFS.

sales-practice-data.xlsx · sheet Sales Clean

A pivot table summarizes a long table by groups, with drag and drop instead of formulas. In ten seconds it turns 200 order rows into "revenue by region and channel," and in another ten seconds into "number of orders by category and quarter." It's the fastest way to explore a dataset in Excel or Google Sheets, and it's a skill almost every analyst job posting expects. In this lesson you'll build the pivots an analyst would make on day one with the course dataset.

How a pivot table thinks

Every pivot table is built from four areas. Once you understand them, you can build any summary:

Rows

The categories listed down the left side, such as region or product.

Rule: use a categorical column (or dates grouped by month or quarter).

Columns

A second set of categories across the top, such as channel. Optional.

Rule: keep it to a few values (3–12) or the table gets too wide to read.

Values

The numbers to calculate for each combination: sum of revenue, count of orders, average order.

Rule: check the function. Sum, Count and Average answer very different questions.

Filters

Restrict the whole table, for example to the Online channel or to Q4 only.

Rule: always show which filter is active so readers don't misread the totals.

The source data must be tidy (Lesson 3): one header row, one row per record, no blank rows and no total rows. Our Sales Clean sheet already meets that standard.

Build your first pivot table

In Excel

Click any cell inside the data (ideally formatted as a table with Ctrl+T).
Go to Insert → PivotTable → From Table/Range, choose New Worksheet and click OK.
In the PivotTable Fields pane, drag region to Rows and revenue to Values. Excel shows "Sum of revenue."
Drag channel to Columns.
Right-click any number → Number Format → Currency with 0 decimals.

In Google Sheets

Select the data and go to Insert → Pivot table → New sheet → Create.
In the Pivot table editor, click Add next to Rows and pick region.
Add channel under Columns and revenue under Values (summarized by SUM).

Either way, you get this cross-tab of revenue by region and channel:

Sum of revenue	Online	Retail	Wholesale	Grand Total
Midwest	$2,429	$1,743	$9,467	$13,639
Northeast	$3,505	$3,253	$14,028	$20,786
South	$4,334	$4,497	$9,853	$18,684
West	$5,593	$2,902	$28,131	$36,626
Grand Total	$15,861	$12,395	$61,479	$89,735

Twenty cells, zero formulas. And there's already a story in it: the single biggest cell, West wholesale ($28,131), is larger than the entire Midwest, Northeast or South.

1. You want the number of orders per region instead of revenue. What do you put in Values?

revenue, summarized by Sum
region, summarized by Sum
order_id, summarized by Count

Counting a column that's filled in for every row (like order_id) gives the number of orders: West 64, South 59, Northeast 45, Midwest 32. Summing revenue gives dollars, and you can't sum a text column like region.

Sum, count, average: same pivot, different questions

Click the value field → Value Field Settings (Excel) or the "Summarize by" dropdown (Sheets) to change the calculation. Using the same rows and columns:

Region	Count of orders	Average online order	Average wholesale order
Midwest	32	$161.93	$1,577.83
Northeast	45	$166.90	$1,753.50
South	59	$166.69	$1,094.78
West	64	$186.43	$2,009.36

Average online orders are similar everywhere (about $162 to $186). The big regional differences come from wholesale: the West's average wholesale order ($2,009.36) is almost twice the South's ($1,094.78). That's a much sharper insight than "the West sells more."

Show values as percentages

Raw dollars make comparisons hard. In Excel, right-click a value → Show Values As; in Sheets, use "Show as" in the Values section. Three options matter most:

% of Grand Total: each cell's share of all revenue. West wholesale alone is 31.3% of the company's revenue.
% of Row Total: the channel mix within each region. Wholesale is 76.8% of the West's revenue but only 52.7% of the South's.
% of Column Total: how each channel's revenue splits across regions. The West takes 45.8% of all wholesale revenue ($28,131 ÷ $61,479).

Wholesale as a share of each region's revenue (% of Row Total)

West76.8%

Midwest69.4%

Northeast67.5%

South52.7%

The South is the region least dependent on wholesale: it has the most balanced mix of online ($4,334), retail ($4,497) and wholesale ($9,853) revenue.

2. In the region × channel pivot set to "% of Row Total," what does the value for South / Retail represent?

Retail's share of the South's revenue ($4,497 ÷ $18,684 ≈ 24.1%)
Retail's share of the whole company's revenue
The South's share of all retail revenue

Row Total means each row adds up to 100%. For the South row, retail is $4,497 of $18,684, about 24.1%. The South's share of all retail revenue would be "% of Column Total" ($4,497 ÷ $12,395 ≈ 36.3%).

Group dates into quarters or months

Drag order_date to Rows. Recent versions of Excel group dates automatically; otherwise right-click a date → Group → select Quarters (and Years if your data spans more than one). In Google Sheets, right-click a date in the pivot → Create pivot date group → Quarter. With channel in Columns you get:

Quarter 2025	Online	Retail	Wholesale	Total	% of year
Q1	$2,874	$2,160	$4,053	$9,087	10.1%
Q2	$4,524	$2,121	$22,282	$28,927	32.2%
Q3	$4,179	$5,411	$12,672	$22,262	24.8%
Q4	$4,284	$2,703	$22,472	$29,459	32.8%

Q1 was by far the weakest quarter ($9,087, only 10.1% of the year), mostly because wholesale barely started. Q2 and Q4 were almost tied, each driven by more than $22,000 of wholesale orders.

Filters, slicers and calculated fields

Slicers are clickable filter buttons. In Excel: PivotTable Analyze → Insert Slicer → choose channel. Click "Online" and the whole pivot (and any connected pivot chart) updates. They're the first step toward a dashboard (Lesson 11). Google Sheets offers slicers too (Data → Add a slicer).

A calculated field creates a new value from existing fields. In Excel: PivotTable Analyze → Fields, Items & Sets → Calculated Field, name it price_per_unit and enter = revenue / units. Pivot calculated fields divide the sums, which is exactly what you want: the weighted average price per unit sold. Result by channel: Online $110.92, Retail $111.67, Wholesale $106.55. Wholesale buyers don't get special prices in this data; they simply buy a slightly cheaper product mix, in much larger quantities.

Here's how the year's revenue splits by category, the kind of pivot you'd turn into a quick chart:

Electronics (49.1%) $44,068Smart watches, wireless earbuds and Bluetooth speakers.
Home & Kitchen (19.9%) $17,875Air fryers, coffee makers and blenders.
Office (17.3%) $15,507Office chairs and monitor stands.
Fitness (13.7%) $12,285Adjustable dumbbells and yoga mats.

Pivot: category in Rows, Sum of revenue in Values, shown as % of Grand Total.

3. You add 20 new orders to the bottom of the source data, but the pivot table totals don't change. What's the most likely fix?

Rebuild the pivot from scratch
Refresh the pivot (and make sure the source is a Table so its range includes the new rows)
Change Sum to Count

Pivot tables don't recalculate automatically like formulas. Right-click → Refresh (or Data → Refresh All). If the source was a fixed range like A1:J201, new rows fall outside it; an Excel Table expands automatically.

Pivot mistakes that give wrong answers silently

Forgetting to refresh after the data changes.

Refresh before every report, and use a Table as the source.

Building the pivot on raw, uncleaned data.

Clean first: in the raw sheet, "Texas", "TX" and "Texas " can show up as separate rows, splitting Texas's total.

"Count of revenue" appearing instead of "Sum".

That usually means some values are text or blank. Fix the column, then switch to Sum.

Including a total row from the source data.

Keep totals out of the source; the pivot creates its own.

Sharing a filtered pivot without saying so.

Put active filters in the title: "Revenue by region, Online only."

4. Which pivot layout best answers "Which product category sells most in each region?"

Rows: category · Columns: region · Values: Sum of revenue
Rows: order_id · Values: Sum of revenue
Rows: region · Values: Count of category

Category down the side and region across the top creates one cell per category-region pair, so you can read the top category in each region column. Counting categories would give the number of orders, not sales.

Practice exercise

Build a pivot table from Sales Clean with category in Rows, region in Columns and Sum of revenue in Values. Then answer:

Which region sells the most Fitness products, and how much?
Which category is the biggest in the South?
Which single category-region cell is the smallest?
In which category is the West not the top region?

See solution

Sum of revenue	Midwest	Northeast	South	West	Total
Electronics	$5,589	$12,676	$8,692	$17,111	$44,068
Fitness	$474	$3,728	$2,230	$5,853	$12,285
Home & Kitchen	$3,616	$1,742	$6,547	$5,970	$17,875
Office	$3,960	$2,640	$1,215	$7,692	$15,507
Total	$13,639	$20,786	$18,684	$36,626	$89,735

The West: $5,853 of Fitness revenue.
Electronics: $8,692 of the South's $18,684.
Midwest Fitness: just $474.
Home & Kitchen: the South leads with $6,547, ahead of the West's $5,970. That's a useful lead for the South's regional manager.

Summary

A pivot table summarizes data by groups using four areas: rows, columns, values and filters.
Change the value calculation (sum, count, average) to answer different questions from the same layout.
"Show values as" percentages reveal mixes: wholesale is 76.8% of West revenue but 52.7% of the South's.
Group dates into quarters or months; Q1 2025 brought in only 10.1% of the year's revenue.
Slicers add clickable filters, and calculated fields like revenue ÷ units give weighted ratios.
Refresh after data changes and always pivot clean data.

Next lesson: Introduction to SQL: SELECT, WHERE and ORDER BY. You'll load the dataset into a real database and write your first queries.