Module 3 · Analysis and Reporting · PivotTables and PivotCharts

PivotTables, cleaning, reconciliation and variance analysis

Summarise data interactively, clean it systematically, reconcile two sources and present plan-vs-actual variance correctly.

Intermediate60 min

Recommended first

This lesson builds on: Conditional functions and lookups — IF, SUMIFS, COUNTIFS, XLOOKUP and INDEX/MATCH.

Learning objectives

Build PivotTables and PivotCharts with correct value settings
Highlight exceptions with conditional formatting
Detect and remove duplicates and clean common data problems
Reconcile two lists and explain differences
Calculate and present variance with a correct sign convention

PivotTables

A PivotTable summarises a tidy table by dragging fields into four areas:

Area	Purpose	Example
Rows	Categories down the side	Store
Columns	Categories across	Month
Values	Aggregated numbers	Sum of Revenue
Filters	Restrict the whole pivot	Status = completed

Steps: click inside your Table → Insert → PivotTable → New worksheet → drag fields.

Using the 8-row Sales table from the previous lesson, Rows = Store, Columns = Category, Values = Sum of Revenue gives:

Store	Beauty	Electronics	Grocery	Total
Baku	75	180	366	621
Ganja		730	64	794
Total	75	910	430	1,415

The totals match the SUMIFS results from the previous lesson — reconcile your pivot against an independent formula the first time you build it.

Value settings that matter

Summarise values by: Sum, Count, Average, Distinct Count (Distinct Count requires Add this data to the Data Model when creating the pivot).
Show values as: % of Grand Total, % of Row Total, Difference From, % Difference From (previous month), Running Total. Ganja Electronics as % of Grand Total = 730 / 1,415 = 51.6%.
Group dates by Months/Quarters/Years; numbers into bands.
Refresh (Alt + F5 / Data → Refresh All) — pivots do not update automatically when the source changes.

Counting text as numbers

If Revenue was imported as text, the pivot defaults to Count instead of Sum. When a pivot says "Count of Revenue", fix the source data type.

PivotCharts and slicers

Insert → PivotChart creates a chart linked to the pivot. Slicers (PivotTable Analyze → Insert Slicer) are clickable filters; one slicer can control several pivots (Report Connections) — a lightweight dashboard.

Conditional formatting

Home → Conditional Formatting highlights exceptions so managers see them instantly:

Highlight cell rules: values below 0.95 in a "% of plan" column in red.
Top/Bottom: top 10 products.
Data bars, colour scales, icon sets: magnitude at a glance — use sparingly.
Formula-based: =$F2<0.95 applied to the whole row highlights entire rows of under-performing stores. The $ locks the column so every cell checks column F.

Duplicates

Find: Conditional Formatting → Highlight Cell Rules → Duplicate Values, or =COUNTIFS($A$2:$A$1000, A2) > 1.
Remove: Data → Remove Duplicates — choose which columns define a duplicate. Selecting only customer_id keeps the first row per ID and drops others even if they differ in other columns.
Excel 365: =UNIQUE(range) returns distinct values dynamically.

Remove Duplicates is destructive

Work on a copy or in Power Query, and record row counts before and after. customers_raw has 645 rows but only 600 distinct customer_ids. Removing duplicates across all columns leaves 639 rows, not 600: most duplicated customers differ in city spelling or date format, so they are not exact duplicates. Standardise first (TRIM, PROPER, date conversion), then remove duplicates on customer_id — and the count should be 600.

A cleaning checklist

Problem	Excel fix
Extra spaces	`TRIM`, `CLEAN` (non-printing characters)
Inconsistent case	`PROPER` / `UPPER`
Numbers stored as text	`VALUE`, or Data → Text to Columns → Finish
Mixed date formats	`DATEVALUE` with care, or Power Query with locale
Inconsistent categories ("Baku", "BAKU")	Mapping table + XLOOKUP
Missing values	Decide: leave blank, impute, or exclude — and document

Keep raw data untouched on its own sheet; do cleaning in new columns or in Power Query so every step is visible.

Reconciliation

Two lists should agree — CRM customer IDs vs billing customer IDs, or bank statement vs ledger. A reconciliation answers: what is in both, only in A, only in B, and in both with different values?

ID	In A?	In B?	Amount A	Amount B	Status
101	✓	✓	500	500	Matched
102	✓	✓	300	280	Amount differs (−20)
103	✓	✗	150		Missing in B
104	✗	✓		90	Missing in A

Formulas: =IF(COUNTIFS(B_IDs, A2)>0, "✓", "✗") and =XLOOKUP(A2, B_IDs, B_Amounts, ""). Totals: A = 950, B = 870; difference −80 = −20 (102) − 150 (103) + 90 (104). Every unit of difference is explained — that is what "reconciled" means.

Variance analysis

Variance compares actual (fact) with plan (budget) or prior period.

Absolute variance = Actual − Plan
Variance % = (Actual − Plan) / Plan

Sign convention: for revenue, positive = favourable. For costs, Actual − Plan positive means overspending = unfavourable. Professional reports either flip the sign for costs or add a Fav/Unfav column — and state the convention.

Line	Plan	Actual	Var	Var %	Fav/Unfav
Revenue	100,000	96,000	−4,000	−4.0%	Unfav
Marketing cost	8,000	9,200	+1,200	+15.0%	Unfav
Rent	6,000	6,000	0	0.0%	—

=IF(type="Revenue", IF(var>=0,"Fav","Unfav"), IF(var<=0,"Fav","Unfav"))

Explain the biggest variances, not every variance

Rank variances by absolute size and comment on those that move the decision. A 15% overspend on a small line may matter less than a 4% revenue miss.

Practice

Your PivotTable shows Count of Revenue instead of Sum. The most likely reason?

Marketing cost: plan 8,000, actual 9,200. What is the variance % ((actual − plan) ÷ plan × 100)? Enter the number only.

System A total = 950, system B total = 870. One record exists only in A (150), one only in B (90), and one matched record differs (A 300, B 280). After explaining these three items, how much of the difference remains unexplained?

Summary

PivotTables summarise tidy data; check value settings (Sum vs Count), use Show Values As, and refresh.
Conditional formatting highlights exceptions; formula rules with $ format whole rows.
Remove duplicates deliberately, record row counts, keep raw data intact.
Reconcile by classifying every record (matched, differs, only in A, only in B) until the total difference is explained.
Variance = Actual − Plan; state the sign convention, especially for costs.

← Previous

Conditional functions and lookups — IF, SUMIFS, COUNTIFS, XLOOKUP and INDEX/MATCH

Power Query and Google Sheets collaboration