Your Pivot Table Refreshed and Still Doesn't Tie to the Trial Balance

How-to · Updated July 2026 · ~14 min read

The trial balance says total operating expenses of 412,806.44. The pivot on the ledger tab — refreshed twice, no errors, August's rows visibly in the source — says 408,190.19. There is 4,616.25 missing, the pack is due at four, and nothing on screen looks broken.

Refreshing a pivot table is one keystroke, and it is almost never the problem. Four things produce that gap, and each one is a misstatement waiting to be signed off. The source is a fixed range that stopped a few thousand rows short of this month's re-export. A new account opened in August landed unticked in a field somebody manually filtered a year ago, so its balance is simply absent from the totals. Somebody posted a backdated journal and last month's column has quietly changed since it was reported. Or the pivot sits on a Power Query pull and refreshed against results that had not arrived yet, so it is permanently one period behind.

So this post is the refresh mechanics — Alt+F5, Refresh All, Table sources, Change Data Source, refresh-on-open, retained items — arranged around a tie-out, because that is the only context in which they are worth learning. Every setting below exists to remove one reason the pivot and the trial balance disagree. If getting the ledger out of your accounting system is the earlier half of the job, see exporting the QuickBooks general ledger to Excel.

Tie the pivot to the trial balance, every refresh

Build the control before you learn any of the mechanics, because without it you are relying on noticing a wrong number, and a wrong number in a pivot looks exactly like a right one. Three cells beside the report, filled in every period:

1. The trial balance total for whatever the pivot is supposed to cover, in a cell of its own. Typed from the TB, or linked to it — but from the accounting system's figure, never from anything derived from the same export the pivot is reading, or the check has nothing independent in it.
2. The pivot's grand total, pulled with GETPIVOTDATA rather than by pointing at the cell: =GETPIVOTDATA("Amount",$A$3), where $A$3 is a cell in the pivot and Amount is the value field. With no field/item arguments it returns the grand total. Excel writes this formula for you if you type = and then click the pivot's total cell.
3. The variance: =ROUND($C$1-$C$2,2), formatted red on anything other than zero — Home > Conditional Formatting > Highlight Cells Rules > Not Equal To, value 0.

Use GETPIVOTDATA rather than a plain =F47 reference for a specific reason: add a field to the pivot, collapse a row, or let the report gain a month, and the grand total moves down the sheet. A cell reference then quietly points at a subtotal or a blank, and your tie-out reads zero for the wrong reason. GETPIVOTDATA asks the pivot for its grand total wherever that now sits.

One condition on the comparison: the pivot must be showing the same population as the trial balance figure. Any filter, slicer or report-filter selection narrowing the pivot breaks the like-for-like, so either clear them before reading the tie-out or hold a second TB figure for the filtered population.

Alongside the tie-out, take three control totals from the source table itself on every pull, and keep them where you can see them:

Rows     =ROWS(tblGL)
Debits   =SUM(tblGL[Debit])
Credits  =SUM(tblGL[Credit])

If your export carries one signed amount column instead of two, =SUMIF(tblGL[Amount],">0") and =SUMIF(tblGL[Amount],"<0") do the same job. Check those three against the figures the accounting system reports for the same period and date range. They are what lets you split the problem in one glance: if the control totals are wrong, the export or the source table is wrong and the pivot is innocent; if the control totals agree with the ledger and the pivot still does not tie, the fault is in the pivot, and it is one of the four causes below.

The rule this whole page serves: a refresh is not finished until the variance reads zero. Pressing Alt+F5 is not the end of the job, it is the middle of it.

Refresh, Refresh All, and what neither of them does

A pivot table does not read your data. It reads a pivot cache — a snapshot of the source taken when the pivot was built or last refreshed. Refreshing rebuilds that snapshot; nothing else does, not saving, not F9.

The contextual tab is PivotTable Analyze in Microsoft 365 and Excel 2019 as of 2026, Analyze in 2013–2016, Options in 2010 — Microsoft renames ribbon tabs between releases, so check yours if the label differs. Its Refresh button carries a dropdown with Refresh All, Refresh Status and Cancel Refresh — the last two matter only when the source is an external connection taking its time.

One thing about scope: a pivot created by copying an existing one shares that pivot's cache, so refreshing either updates both, while pivots built independently against the same range may sit on separate caches. If a workbook has more than one pivot, make Ctrl+Alt+F5 the habit. And note the negative case — refreshing does not recalculate worksheet formulas, and recalculating does not refresh pivots. Separate engines.

Which command you pressed is therefore the first question when one page of the pack ties and another does not. A pack built by copying last month's sheets can hold half a dozen pivots on three different caches; Refresh on the sheet you are looking at updates that cache and leaves the others as they were, which produces a set of pages that disagree with each other and each look internally consistent.

Cause 1: the source range stopped short of your re-export

This is the cause of most "pivot table not updating" reports, and it has nothing to do with the refresh command.

When a pivot is built on a fixed range — GL!$A$1:$G$5000 — that address is baked in. The monthly job makes this worse than it sounds, because most people do not append a month, they re-export the year to date: this month's general ledger pull is 4,100 rows longer than the one the pivot was built on, it lands in rows 1 to 9,100, and the pivot reads the first 5,000 of them. Refresh faithfully re-reads a range that has not changed and reports numbers that are neither last month's nor this month's, but a truncation of the year. The pivot is behaving correctly; the definition is wrong.

The variance gives this one away before you have opened a dialog. If the gap equals a clean, contiguous block of transactions — everything after a particular date, or the whole of a ledger that sorts last alphabetically — the rows are falling off the end of a fixed range rather than being mis-summed inside it.

Confirm it in five seconds: click in the pivot, then PivotTable Analyze > Data group > Change Data Source. The dialog opens with the current address in the Table/Range box and Excel jumps to the source sheet with the range selected. If that selection stops above your new rows, you have found your bug.

The fix: make the source an Excel Table

An Excel Table (the Ctrl+T object, not a "table" in the loose sense) grows when data is added to it, and a pivot built on one refers to it by name — so the source expands with the ledger and a plain refresh picks up the new month.

1. Click any cell in the ledger data.
2. Press Ctrl+T. In the Create Table dialog, check the range and tick My table has headers. Click OK.
3. With the cursor still inside, go to the Table Design tab > Properties group > Table Name and give it something you will recognise: tblGL.
4. Build the pivot: Insert > PivotTable, and type tblGL into Table/Range instead of dragging a range.
5. Each month, paste the new transactions into the row immediately below the last row of the table. The table absorbs them and its border extends.
6. Press Alt+F5. The new month is in.

Three conditions catch people out. Every column needs a non-blank, unique header — an empty header cell produces "The PivotTable field name is not valid", and duplicates get silently renamed. The table must have no fully blank rows inside it, or auto-expansion stops there. And expansion depends on File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type > "Include new rows and columns in table" being ticked; it is on by default but is one of the first things a locked-down corporate template turns off. If the border does not extend, drag the resize handle at the table's bottom-right corner or use Table Design > Resize Table.

Pointing an existing pivot at the Table

You do not have to rebuild anything. Convert the source range with Ctrl+T as above, then click in the pivot, choose PivotTable Analyze > Change Data Source, clear the Table/Range box, type tblGL, and click OK. Layout, formatting, calculated fields and slicers all survive. Refresh once and the pivot is permanently connected to a range that grows. Do this to every pivot reading that data, then never touch Change Data Source again.

When the source is a system report you cannot convert

Some sources genuinely cannot be a Table, and accountants get handed all of them. A trial balance export with a merged three-line header band above the columns and subtotal rows sitting inside the data. A report a colleague's macro rewrites from scratch every month, so anything you add to the sheet is gone by the 5th. A ledger extract that arrives with the entity name and the run date in the first two rows. For those, Change Data Source is the manual equivalent of what a Table does automatically: click in the pivot > PivotTable Analyze > Change Data Source > Change Data Source..., then drag or type the new range.

Two habits make it safer. Over-select deliberately — GL!$A$1:$G$20000 on 5,000 rows leaves headroom for years, at the cost of a (blank) item in every field, which you filter out once and it stays filtered. Or use a dynamic named range: Formulas > Name Manager > New, name it GLData, Refers to =OFFSET(GL!$A$1,0,0,COUNTA(GL!$A:$A),7), then put GLData in Table/Range. It works, but it is a volatile formula and one more thing to explain to whoever inherits the file.

The same dialog is where you go when the source moves — a new file, a renamed sheet — and it offers Choose Connection if the source is external. One error worth naming because it wastes hours: "Data source reference is not valid." Nine times out of ten the workbook's file name contains square brackets, TB [final] Aug.xlsx. Excel uses brackets in its own external-reference syntax and chokes. Rename the file and the pivot behaves.

Refresh on open — and whether to keep the cache in the file

The cleanest monthly workflow does not involve pressing anything. Click in the pivot > PivotTable Analyze > PivotTable group > Options to open the PivotTable Options dialog > Data tab, and tick Refresh data when opening the file. Repeat for each pivot that sits on its own cache.

On the same tab is Save source data with file. Untick it and the workbook stores no cache — file size drops sharply on a large ledger, but the pivot arrives empty and must refresh to show anything, so only pair it with refresh-on-open.

Presented as a size setting, it is really a records decision, and it is worth making deliberately. With the cache saved, the file you issued still contains the numbers it showed on the day you issued it, and you can open it in six months and see them. With the cache dropped, opening that same file reads whatever the source says now — and if the source has moved, been re-exported, or picked up a backdated journal, the figures on screen are not the figures you reported. If the source was an external file or a connection that no longer resolves, you get nothing at all.

So: keep the cache in anything issued, signed off or archived — the management pack, the file supporting a set of accounts, anything a client or an auditor might come back to. Drop the cache in the working file that you rebuild from a live pull every month, where nobody will ever want last month's state back out of it and the file is unwieldy. And if what you actually want is a frozen record rather than a live report, the more reliable answer is neither setting: issue a PDF, or a values-only copy of the pack, and keep the workbook as the working tool.

Timed refresh is a different setting, and it exists only for connections and queries, not for a pivot reading a worksheet range: Data > Queries & Connections, right-click the query, Properties, Usage tab, Refresh every [n] minutes. If your pivot sits on a Power Query load, that is where its schedule lives — Power Query for beginners covers getting the data there in the first place.

Cause 2: dead accounts and closed periods still in the filter

You removed 2023 from the ledger months ago, or an account code was retired at year end, or two customer records were merged. You refresh, the pivot's numbers are right — and the filter dropdown still lists 2023, while the slicer still shows the retired account, greyed but tickable.

Those are retained items: the cache deliberately keeps a history of every item it has ever seen in each field, so manual filter selections survive a refresh. Useful in principle, dangerous in a management pack, because on screen a ghost is indistinguishable from a live one. Nothing marks the merged customer or the retired code as historic; they sit in the list in alphabetical order with everything else. Somebody preparing next month ticks one, gets a column of zeroes, assumes the entity had no activity, and the pack goes out with a blank where an explanation should be — or worse, ticks the dead half of a merged customer and reports that customer's spend as nil. The fix is one dropdown:

1. Click in the pivot > PivotTable Analyze > Options > Data tab.
2. Under Retain Items Deleted from the Data Source, set Number of items to retain per field to None.
3. Click OK, then refresh (Alt+F5). The setting takes effect on the next refresh, not on OK — which is why people set it, see no change, and conclude it does not work.
4. Leave it on None. On a monthly close pack that is the right default.

The three options mean: Automatic (Excel's default number of unique items per field), None (keep nothing that is not in the current data), Max (up to 1,048,576 items per field — effectively forever, and a real cause of bloated files).

Slicers draw from the same cache, so the ghosts vanish at the same refresh; if a live item legitimately has no rows this period, right-click the slicer > Slicer Settings > tick Hide items with no data.

Cause 3: the new account that arrived unticked

This is the one to read twice. It is the mirror image of the ghost items, it is far more dangerous, and almost nothing on screen tells you it is happening.

Someone, at some point, applied a manual filter to a field. Perhaps they unticked two cost centres that had closed, eighteen months ago, for one meeting. That single act changes how the field behaves from then on: in a manually filtered field, items that appear in the source for the first time arrive unticked. A new expense account opened this August is therefore in the source, in the cache, and excluded from the totals — and the pivot looks perfectly healthy, because nothing is broken. The grand total is simply smaller than the ledger, by exactly the balance of an account nobody knew was missing.

The setting lives at PivotTable Analyze > Options > Totals & Filters tab: Include new items in manual filter. Tick it. Then go and look at the filter on every field that has one, because ticking the box governs items arriving from now on and does not go back and find the account that was excluded last September.

Two ways to catch it. The direct one is a count: compare the number of accounts the pivot is showing against the number in the source.

=COUNTA(UNIQUE(tblGL[Account]))

UNIQUE needs Microsoft 365 or Excel 2021 and later; on a perpetual 2016 or 2019 build, =SUMPRODUCT(1/COUNTIF(tblGL[Account],tblGL[Account])) gives the same distinct count as long as the column has no blanks in it. If that number exceeds the account rows in the pivot, something is filtered out, and the filter dropdown will show you which one in a few seconds of scrolling for the unticked box.

The indirect one is the tie-out you built at the top. A variance that matches a single account's balance to the penny — not a round number, not a clean block of dates — is very often one excluded item rather than anything structural. Search the trial balance for the variance figure and you will usually find it sitting against one account name.

Say the consequence plainly, because it decides how much care this deserves: an account silently excluded from a management pack is a misstatement, not an inconvenience. It survives review precisely because there is nothing to review — no error, no blank, no warning, just a total that is quietly too small.

Cause 4: last month's column changed after sign-off

The three causes above make the current period wrong. This one makes a period you already reported wrong, after you reported it, and it is the reason a refresh deserves a control at all.

Somebody posts a backdated journal into July — a correction, a late accrual, an adjustment an adviser asked for. Nothing on your side changes. But the next time you refresh the pack, the July column re-reads a source that now contains that entry, and the figure you circulated in a signed pack three weeks ago is no longer the figure in the file. Nobody is told. The file does not flag it. The first sign is usually someone asking why your July number does not agree with the one in their board minutes.

The workbook control is a snapshot. Once a period is reported, copy its column out of the pivot and Paste Special > Values into a column headed As reported, beside the live report. Add a difference column against the live figures — column N here — and one count above it that has to read zero:

=SUMPRODUCT(--(ROUND($N$2:$N$200,2)<>0))

A non-zero count is not a fault to clear — it is information you now have an obligation to act on. Find the entry behind it, decide whether it belongs in the reported period or should have gone to the current one, and tell whoever received the original pack. Correcting the number quietly and reissuing without a word is how a small posting error becomes a credibility problem.

The better control is upstream, in the accounting system. Both QuickBooks Online and Xero can lock a period once it is reported — QuickBooks with its close-the-books date and an optional password, Xero with lock dates that can be set separately for ordinary users and advisers — and a locked period is the only thing that actually stops a backdated posting rather than detecting it afterwards. Where locking is not practical, the weaker fallback is visibility: if your ledger export can carry the date an entry was created as well as its transaction date — and not every report offers both — pull that column too, and anything created after the period was reported is immediately visible instead of silently absorbed.

Refresh order when the pivot sits on a live ledger pull

If the pack is fed by Power Query or a connection to the accounting system, the order you do things in matters as much as the settings, because a query and a pivot refresh independently and the pivot does not wait to be told the query has finished.

1. Untick background refresh on the query — Data > Queries & Connections, right-click the query, Properties, Usage tab. With it on, the refresh returns control to Excel immediately and the pivots rebuild against results that have not landed.
2. Refresh the query and let it finish. With background refresh off, Refresh All (Ctrl+Alt+F5) will run the queries to completion before the pivots. If you cannot change the setting, refresh the query on its own and wait for the Queries & Connections pane to stop showing it as loading.
3. Check the control totals — rows, debits, credits — against the figures the accounting system reports for the same period. This is the step people skip, and it is the only one that tells you the data is right before you start reading conclusions off it.
4. Refresh the pivots if they did not refresh with the queries.
5. Read the tie-out. Zero, or stop.
6. Then distribute.

Refresh All is a command, not a process. It updates everything and verifies nothing, and on a pack fed by a live pull the gap between those two is exactly where an untied set of numbers gets emailed out.

Variance checklist: what each difference usually means

The tie-out tells you there is a difference; the shape of the difference tells you where to look. Work down this list.

That last row is the one people fight for months. With background refresh on, Refresh All kicks off the query and then refreshes the pivot against results that have not arrived yet, so the pivot is always exactly one refresh behind. Turning it off forces the sequence to run in order.

How HISAB 360 helps

The mechanics above are cheap to learn and expensive to maintain: every new pivot is another Change Data Source, another Options dialog, another retained-items setting, across every workbook in the close folder. HISAB 360 is a paid Excel add-in with an AI chat panel docked inside Excel that works on the live workbook rather than handing you instructions. Ask for "a pivot of the GL by account and month, sourced from the ledger table" and it builds the report in the sheet; ask it to refresh the reports on that sheet and it does that too. When the pack outgrows a single pivot — several ledgers, prior-year comparatives, measures a pivot cannot express — it can build a Power Pivot data model with DAX measures from a plain description, and Power Query queries the same way. The one-off Options settings above — refresh-on-open, retain-items-None, include-new-items — are still yours to tick once per pivot.

It also connects to five accounting systems with read and write access: QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks. That closes the loop on the first half of this page — pull the current period's invoices, bills and GL activity into the sheet, refresh, and the pack reports on today's ledger rather than a fortnight-old export. What it does not do is decide that the variance is acceptable; the tie-out is still a number you read.

Honest limits: Windows desktop Excel only (Microsoft 365 or Excel 2016+ on 64-bit Windows), not Mac and not Excel on the web, and it is paid — the trial runs 15 days with 50 AI credits and no card. A generated pivot deserves the review you would give a colleague's: check the source range and tie one total back to the ledger by hand.

Automating the monthly refresh

Refresh-on-open covers most monthly packs and needs no code. When the source changes while the file is open, a one-line macro in the pivot sheet's code module (right-click the sheet tab > View Code) rebuilds the cache each time you click onto the report tab:

Private Sub Worksheet_Activate()
    Me.PivotTables("ptGL").PivotCache.Refresh
End Sub

Refreshing on every edit instead — a Worksheet_Change handler calling RefreshAll — is tempting and usually a mistake: on a 50,000-row ledger it fires on each keystroke and the workbook crawls. Any of this needs an .xlsm file; what a macro actually is covers the format and trust-centre side. Scheduled refresh across a folder of workbooks is a different discipline again, covered in Excel automation for finance teams.

One condition on all of it: automate the refresh and the tie-out check together, or you have simply automated the production of an untied pack. A macro that refreshes eleven pivots on activation and says nothing about the variance has made the wrong number arrive faster.

Keeping the formatting through a refresh

One last irritation, because it makes people avoid refreshing at all: column widths reset and number formats vanish. Both live on PivotTable Options > Layout & Format. Untick "Autofit column widths on update", or every refresh re-widths the columns and moves your print area, and tick "Preserve cell formatting on update" to keep the formats you applied.

Set those once per pivot when you build it, alongside refresh-on-open and retain-items-None, and the report is maintenance-free — a combination worth a standing line on your month-end close checklist so it reaches every new pack.

The settings on one page

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.