How to Refresh a Pivot Table in Excel — and Stop Old Months Haunting Your Filters

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

August closes, you paste 4,100 new ledger rows under July's, click into the pivot that feeds the management pack, and the grand total is exactly what it was last month. Or the total does move, but the Month filter still offers three periods that were deleted from the source back in March — and somebody ticks one before the pack goes out.

Refreshing a pivot table is one keystroke. Almost every "my pivot won't update" problem is not the refresh at all: it is a source range that never grew, an item list cached inside the file, or a checkbox nobody has opened since the workbook was built. This post covers the refresh commands first, then the four settings that turn a monthly pivot from a chore into something that is simply correct when you open the file — framed throughout on the job most accountants are doing, a trial balance or general ledger export that gains a month of transactions every period. If getting the ledger out of your accounting system is the earlier half of that job, see exporting the QuickBooks general ledger to Excel.

Refreshing: the four ways, and which one to use

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.

Why the refresh did nothing: the source range never grew

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. Paste August into rows 5001 onward and those rows are simply not part of the source; refresh faithfully re-reads rows 1 to 5000, which have not changed, and reports the same numbers. The pivot is behaving correctly; the definition is wrong.

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.

Change Data Source: for the ranges you cannot convert

Some sources genuinely cannot be a Table — merged header bands, a system report with subtotal rows inside it, a range someone else's macro rewrites every month. 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 when the file opens

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.

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.

The haunted filter: old months that will not leave

Here is the symptom that names this post. You removed 2023 from the ledger months ago, or an account code was retired, or a customer was 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 a stale item is indistinguishable from a live one until someone ticks it and gets a blank column. 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. The mirror-image problem lives on the Totals & Filters tab: Include new items in manual filter. If a field has been manually filtered — you unticked two cost centres once — new items arriving in later months land unticked and quietly drop out of the totals. Tick the box. A silently excluded new account is a misstatement, not an inconvenience.

When the pivot still is not updating

If the source is a Table, the setting is on, and the numbers are still wrong, 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. The one-off Options settings above — refresh-on-open, retain-items-None — are still yours to tick once per pivot. When the summary 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.

It also connects to six accounting systems with read and write access: QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting. That closes the loop — pull the new period's invoices, bills and GL activity into the sheet, refresh, and the pivot reports on current ledger data rather than a fortnight-old export.

Honest limits: Windows desktop Excel only (Microsoft 365 or 2016+, 64-bit), 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.

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, FreshBooks and Sage. The 15-day trial is the full product, no card required.