Pivot Tables for Finance: Drag, Drop, Done | Excel & Power BI S1 Ep4

📂 Download the AtlasParts dataset and the Episode 4 starter / completed workbooks: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 4 of *Excel & Power BI for Finance*. In Episode 3 we built a one-page P&L by category and customer country with SUMIFS plus a pair of XLOOKUPs. The result was correct, but every cell of that grid was a formula — and changing dimensions meant rebuilding the whole grid. Pivot tables solve that. Same aggregation, same panel, no formulas. You drag fields. Excel does the math. This episode rebuilds the Episode 3 panel with a pivot, then walks the four pivot superpowers that SUMIFS can't match easily — date grouping, % of column total, slicers, and calculated fields. We finish with the three classic gotchas every finance analyst eventually hits: blank rows in the source, refresh anxiety, and GETPIVOTDATA. What You'll Learn: - Building a pivot end-to-end. Insert PivotTable, choose the source range, drop account_id into Rows, customer_country into Columns, credit into Values. The Episode 3 panel materializes in two minutes of dragging - The PivotTable Field List anatomy. Top half lists every source column. Bottom half has four drop zones: Filters, Columns, Rows, Values. The mental model maps directly to SUMIFS' criteria pairs - Date grouping. Drop posting_date in Rows, right-click → Group → Months and Years. Doing the same with SUMIFS requires EOMONTH plus helper columns - Show Values As → % of Column Total. One menu click turns dollar amounts into column-relative percentages — same data, different lens - Slicers. Click-button filters that everyone on the team can use. Click 2025, the whole panel refilters. Click another slicer to combine - Calculated fields. Define gross_margin = credit − cost_of_goods_sold once, use it across the entire pivot. The field appears in the field list permanently - Gotcha 1 — blank rows. If any source row has account_id missing, the pivot creates a (blank) row and sums the orphans. Almost always wrong. Fix: scan, fix at source, refresh - Gotcha 2 — refresh anxiety. Pivots take a snapshot when built. Add a row to the source and the pivot doesn't know. Right-click → Refresh, or Alt+F5. Worse: rows past the original range may be missed entirely - The Ctrl+T fix for refresh anxiety. Convert the source to a Table first. Tables auto-expand to include new rows; pivots from Tables auto-pick them up on Refresh - Gotcha 3 — GETPIVOTDATA. Click a pivot cell from outside the pivot and Excel inserts =GETPIVOTDATA(...) instead of a plain cell reference. It's label-based: the formula keeps finding the right cell even after you reorder the pivot. Many analysts hate it; this episode shows why you should learn to love it (or how to turn it off) - The ceiling. Pivots are snapshots. They don't run on a schedule, they don't auto-update, and they break if the source structure changes. That's the motivation for Episode 5: Power Query Timestamps: 0:00 - Intro — Pivot Tables 0:24 - What's in this episode 0:56 - Episode 3 recap — every cell was a formula 1:38 - Building the pivot — drag, drop, done 2:53 - Pivot superpowers — date group, % column, slicers, calc fields 3:53 - Gotcha 1 — blank rows in the source 4:43 - Gotcha 2 — refresh anxiety, fixed by Ctrl+T 5:43 - Gotcha 3 — GETPIVOTDATA explained 6:48 - What pivots can't do — the Power Query teaser 7:35 - Recap — three takeaways 8:10 - Up next · Episode 5 · Power Query for Finance Key Takeaways: 1. Pivots build the same panel as SUMIFS, faster, with no formulas. Drop account_id in Rows, customer_country in Columns, credit in Values. Two minutes of dragging. 2. Pivots have four superpowers SUMIFS can't match easily: date grouping (right-click → Group), Show Values As → % of Column Total, slicers as click-button filters, and calculated fields you define once. 3. Three gotchas every analyst hits: (blank) rows from missing IDs in the source, refresh anxiety from snapshot semantics, and GETPIVOTDATA's label-based formulas. All three have known fixes. 4. Always Ctrl+T the source before building the pivot. Tables auto-expand and pivots from Tables auto-pick up new rows on Refresh — kills 90% of refresh-anxiety problems. 5. Pivots are snapshots, not pipes. They don't auto-refresh, they don't run on a schedule, and they break if the source structure changes. For repeatable, schema-resilient workflows, you need Power Query — Episode 5. Next episode: *Power Query for Finance*. We finally clean up the messy customer master. Taught by CelesteAI. #exceltutorial #powerbi #financetraining #financeprofessionals #pivottables #pivottable #getpivotdata #excelpivot #financialreporting #excelforaccountants #cfo #excelandpowerbi #excelforfinance #financialanalysis #atlasparts