Blog · How-to guides · Finance and FP&A teams
Build a financial pivot from ledger movements after mapping accounts and checking signs. Work through a balanced export, separate profit from balance-sheet movements, and prove the totals.
A pivot table for financial analysis summarizes mapped ledger movements by reporting line, financial period, and cost center. Preserve the source signs and reconcile the mapped rows before interpreting profit. A tidy pivot can still be wrong if it combines closing balances with monthly activity.
For this worked example, a US distributor exports September 2026 activity in USD. Keep posting date, entity, account, cost center, debit, and credit in separate columns. Preserve document and journal identifiers in a real export so reviewers can locate the underlying entry.
The compact extract below contains all the movements used in the example. Debits and credits are positive in their respective columns. Signed movement is debit minus credit.
| Row | Account | Cost center | Debit (USD) | Credit (USD) | Signed movement (USD) |
|---|---|---|---|---|---|
| 2 | 4000 | North | 0 | 70,000 | -70,000 |
| 3 | 4000 | South | 0 | 50,000 | -50,000 |
| 4 | 5000 | North | 42,000 | 0 | 42,000 |
| 5 | 5000 | South | 30,000 | 0 | 30,000 |
| 6 | 6000 | North | 14,000 | 0 | 14,000 |
| 7 | 6000 | South | 10,000 | 0 | 10,000 |
| 8 | 1000 | Corporate | 20,000 | 0 | 20,000 |
| 9 | 1100 | Corporate | 4,000 | 0 | 4,000 |
| Total | 120,000 | 120,000 | 0 |
All rows have the same entity and September posting period. In the worksheet, use A for posting date, B for entity, C for account, D for cost center, E for debit, and F for credit. Calculate G2 and fill down:
=E2-F2
The zero signed total checks this complete, balanced extract. It does not prove that every expected journal was exported. Match the debit and credit totals to the ledger's export controls as well.
On a sheet named Map, put this table in A1:D6. The display multiplier changes presentation without overwriting the original signed movement.
| Account | Reporting line | Statement | Display multiplier |
|---|---|---|---|
| 4000 | Revenue | P&L | -1 |
| 5000 | COGS | P&L | 1 |
| 6000 | Operating expenses | P&L | 1 |
| 1000 | Cash movement | Balance sheet | 1 |
| 1100 | Receivables movement | Balance sheet | 1 |
In the ledger sheet, H is reporting line, I is statement, and J is displayed amount. Enter these formulas in row 2 and fill down:
=VLOOKUP(C2,Map!$A$2:$D$6,2,FALSE)
=VLOOKUP(C2,Map!$A$2:$D$6,3,FALSE)
=G2*VLOOKUP(C2,Map!$A$2:$D$6,4,FALSE)
Google's VLOOKUP documentation recommends FALSE for an exact match. Keep account identifiers consistently stored as text or numbers in both places. Leave missing matches visible and resolve them; replacing an unknown account with zero silently drops financial activity.
Require one map row per account. Duplicate map keys can return a plausible reporting line while hiding a contradictory mapping. Have the controller approve changes, especially when an account moves between cost of goods sold and operating expenses.
In Google Sheets, select the full ledger range and choose Insert, Pivot table. Add reporting line to Rows, cost center to Columns, and displayed amount to Values, summarized by SUM. Filter statement to P&L and select the intended entity and posting dates.
Google documents both pivot construction and access to a cell's source rows. If you need the basic spreadsheet mechanics in Excel, use the existing Excel pivot tutorial. The pivot table definition explains the grouping concept.
The financial output is:
| Reporting line | North (USD) | South (USD) | Total (USD) |
|---|---|---|---|
| Revenue | 70,000 | 50,000 | 120,000 |
| COGS | 42,000 | 30,000 | 72,000 |
| Operating expenses | 14,000 | 10,000 | 24,000 |
| Operating profit, calculated separately | 14,000 | 10,000 | 24,000 |
Calculate profit as revenue minus COGS minus operating expenses. North is 70,000 - 42,000 - 14,000 = $14,000; South is 50,000 - 30,000 - 10,000 = $10,000. Their sum is $24,000. These figures are exact dollars, with no rounding.
Disable or disregard the pivot grand total across these displayed lines: adding positive revenue to positive expenses does not produce profit. For a sales-only view, the neighboring Google Sheets sales pivot guide owns that workflow.
First, the source debits and credits both total $120,000. Second, every account maps once. Third, compare each pivot reporting line with a direct source sum. Google's SUMIFS function sums amounts that meet multiple criteria:
=SUMIFS(J2:J9,H2:H9,"Revenue",I2:I9,"P&L")
That returns $120,000. The equivalent COGS and expense checks return $72,000 and $24,000.
Finally, the raw P&L signed movement is -$24,000, offset by $20,000 cash movement plus $4,000 receivables movement. The combined signed movement is zero. Those asset movements explain this example's balancing entries; they are not closing cash and receivables balances. Use the broader financial analysis guide when moving from reconciled totals to business interpretation.
Covirage is not affiliated with Google or Microsoft.
Bring a sample of your own ledger export or Google Sheet with the account map and control totals. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.
See Covirage for finance teams for the financial reporting context.
It can summarize mapped revenue and expense movements into reporting lines. You still need an approved account map, consistent signs, and a complete period export. Calculate profit from the summarized lines and check it against the ledger's period result.
Check whether the amount column contains numeric values and whether the value field is set to SUM. Currency symbols stored as text, blank strings, and inconsistent imports can prevent a useful amount summary. Verify the source column before changing the aggregation.
Use balances for a statement at a date. Monthly movements explain the change between opening and closing balances. A movement export can support a bridge, but it cannot establish the closing balance without the opening balance and all relevant entries.