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

Download the AtlasParts dataset and the Episode 4 starter / completed workbooks — follow along in your own copy of Excel: github.com/GoCelesteAI/excel-powerbi-for-finance.

In Episode 3 we built a one-page P&L by category and customer country with a SUMIFS formula and a pair of XLOOKUPs. The result was correct. The mechanics were correct. But every cell of that grid was a formula, manually placed, with mixed absolute references that we copy-pasted across rows and columns. If we wanted a different breakdown — say, by month instead of country, or by region instead of country — we’d have rewritten the whole grid.

Pivot tables solve that problem. The aggregation is the same. The mental model is the same. The difference is that you build the report by dragging fields, not by writing formulas. And once it’s built, you can rearrange dimensions in seconds, not in half-day rebuilds.

This episode walks the same P&L panel from Episode 3, but with a pivot. We’ll see what pivots do that SUMIFS can’t easily, and three classic gotchas that bite every analyst eventually: blank rows, refresh anxiety, and GETPIVOTDATA.

Building the same panel

Start with the GL extract from Episode 3, including the customer_country helper column we built with chained XLOOKUP. Click anywhere in the data, then Insert → PivotTable. Excel asks where the data is and where to place the pivot. Confirm both, and a new sheet appears with two things: an empty placeholder for the pivot, and a PivotTable Field List on the right.

The field list shows every column from your source data — journal_id, posting_date, account_id, debit, credit, description, source_doc, customer_country. Below the field list are four drop zones: Filters, Columns, Rows, and Values.

To rebuild the Episode 3 P&L:

• Drag account_id into Rows. Each unique account gets its own row.
• Drag customer_country into Columns. Each unique country gets its own column.
• Drag credit into Values. Excel auto-aggregates as Sum, which is what we want.

The pivot materializes immediately. The same panel — six revenue accounts as rows, five countries as columns, sums of credit at each intersection. Subtotals on the right and bottom. Grand total in the corner.

That’s it. No formulas. No $A2 / B$1 mixed references. No drag-fill. Two minutes of dragging and we’ve reproduced what took half a session of formula work.

What pivots do that SUMIFS can’t, easily

A pivot table is more than a faster SUMIFS. There are operations that are trivial in a pivot and painful with formulas:

Date grouping. Drop posting_date into Rows and right-click → Group → Months and Years. Excel automatically buckets every date into its calendar month. Doing the same with SUMIFS requires EOMONTH, helper columns, and careful date-range criteria. With a pivot it’s a context-menu click.

Show values as. Right-click any value cell → Show Values As → % of Column Total. Every cell becomes a percentage of its column’s grand total. Try doing that with formulas — you’d need a parallel grid of =value/column_total formulas. Pivot does it as a display setting.

Slicers. A slicer is a button-bar filter that controls the pivot. Add a slicer for posting_date (year), and clicking 2025 instantly refilters the entire panel. Multiple slicers stack, so you can filter by year and by category at once with two clicks.

Calculated fields. Need gross_margin = credit - cost_of_goods_sold (which doesn’t exist in your source)? Analyze → Fields, Items & Sets → Calculated Field, write the formula once, and it appears in the field list. Drag it to Values and it works across the whole pivot.

This is why every finance team’s reporting workflow eventually moves to pivots — even for analyses that started as SUMIFS panels. The dimensional view is more flexible than the formula view, and the marginal cost of adding a new dimension is one drag.

Gotcha 1: blank rows in the source

Pivots take your source data at face value. If any row has account_id = "" or customer_country = "", the pivot creates a row labelled (blank). The dollar amount in that row sums all the journal entries with missing account codes — which is almost always wrong, because some of them belong to real accounts and were just exported badly.

Before you build a pivot, scan the source for blanks. The fastest way: select the column, look at the bottom-right of the Excel window where the status bar shows Count: N. Compare that count to the row count. If they don’t match, you have blanks. Filter to find them, fix them, then refresh the pivot.

In the AtlasParts dataset, every GL row has a populated account_id. But if you’re working with a real-world export — especially from SAP, where some line items can be posted to “balance accounts” with no natural account code — expect to find them. Resolve before pivoting, or your (blank) row will hide a real number.

Gotcha 2: refresh anxiety

When you build a pivot, Excel takes a snapshot of the source data at that moment. If you add a row to the source — say, a December 31 adjustment that came in late — the pivot doesn’t know about it. The new row sits there in the source, and the pivot keeps reporting yesterday’s numbers.

You have to refresh. Right-click any pivot cell → Refresh, or Analyze → Refresh. Or Alt+F5. Now the pivot rescans the source and updates.

Worse, when you add a row below the original data range, the pivot may not include it even after a refresh — because the source range was defined by row count when you created the pivot. The fix: make your source data a Table (Ctrl+T) before building the pivot. Tables auto-expand as you add rows, and pivots built from tables auto-pick up new rows on refresh. Don’t skip the Ctrl+T. It’s the difference between “refreshes work” and “I can’t figure out why my December numbers are wrong.”

In real finance teams, this gotcha causes more “the report is wrong” support tickets than any other single thing. Always Ctrl+T the source. Always.

Gotcha 3: GETPIVOTDATA

Build a pivot. Click any cell inside the pivot — say, the cell showing “$1.32M” for Brakes × US. Now go to a different cell, somewhere outside the pivot, and try to reference that pivot cell with a regular formula like =Pivot!C3.

Excel won’t let you. The moment you click into the pivot from outside, Excel inserts a GETPIVOTDATA formula instead:

=GETPIVOTDATA("credit",Pivot!$A$1,"account_id",4010,"customer_country","US")

That’s a label-based reference: “from the pivot at A1, get the value of credit where account_id = 4010 and customer_country = "US".” It’s powerful — if you reorder your pivot, the GETPIVOTDATA still finds the right cell because it references by labels, not by location. But it’s also confusing the first time you see it, and many analysts try to delete it and write =Pivot!C3 instead.

Here’s what to know:

• GETPIVOTDATA works correctly. Don’t be afraid of it. If you’re building a dashboard that pulls specific pivot values into another sheet, it’s the right answer.
• If you find it noisy, turn it off: PivotTable Analyze → Options → Generate GetPivotData (uncheck). Then references will be plain =Pivot!C3 style.
• The argument structure is: GETPIVOTDATA(field_name, pivot_anchor_cell, [pivot_field_1, pivot_value_1], ...). You can construct it manually if you ever need to.

Most analysts hate GETPIVOTDATA on first encounter and learn to love it after the third time their dashboard breaks because they used a position-based reference and someone reordered the pivot.

What pivots can’t do

Pivots are snapshots. They don’t update unless you refresh. They don’t run on a schedule, they don’t pull from external sources by themselves, and they break if the structure of your source data changes (add a column, the pivot’s source range silently misses it; rename a column, the field list shows (blank)).

For workflow that needs to be repeatable — same shape of data every Monday morning, same set of pivots that need to refresh from the latest export without you babysitting them — pivots aren’t enough. That’s where Power Query comes in: a layer between source files and your pivot that does the cleanup automatically, handling messy column names, dirty country codes, and structural drift.

That’s Episode 5.

Coming up next

Episode 5: Power Query for Finance. We finally clean up the messy customer master from Episode 3 — the “US” / “USA” / “U.S.” / “United States of America” mess that fragmented our P&L by country. We’ll also build a refresh pipeline so next month’s report rebuilds itself when you drop in the new GL extract. This is where the “Monday morning workflow” gets a permanent solution.

See you there.