How to Refresh a Pivot Table Automatically: Every Method That Actually Works

Excel does not have a setting called “auto-refresh.” People search for it constantly, and it doesn’t exist as a single toggle anywhere in the interface. What exists instead is a scattered set of five separate mechanisms — a checkbox buried in PivotTable Options, a VBA event tied to workbook activity, a Power Query connection property, an OS-level Task Scheduler entry, and an Office Script triggered by Power Automate — and each one solves a completely different flavor of “my pivot table is stale.”

Pick the wrong mechanism and you’ll spend an afternoon configuring something that technically works but doesn’t fix the actual problem you have. This post is organized as a troubleshooting checklist: identify your symptom, trace it to its cause, and apply the specific fix that matches.

Symptom: The Pivot Table Shows Old Numbers Every Time You Open the File

Cause: Excel only refreshes a pivot table when it’s explicitly told to — either by a person clicking Refresh, or by a setting that fires that click automatically on open. Nothing refreshes just because you opened the workbook, unless you’ve configured it to.

Fix: Right-click anywhere inside the pivot table, choose PivotTable Options, and go to the Data tab. Check the box labeled “Refresh data when opening the file.” Click OK, save the workbook, and close it completely before reopening to confirm the setting held.

This is the correct fix specifically for the “stale on open” symptom, and it’s the one people reach for most often because it requires no code and no scheduled task. But it only fires once, at the moment the file opens — if the underlying data changes while the file is already sitting open, this setting does nothing for you. That’s a different symptom, covered next.

Symptom: The Data Changes While the Workbook Is Already Open, and the Pivot Table Doesn’t Catch Up

Cause: The “refresh on open” checkbox only triggers at file-open time. If someone edits the source range, or a linked table updates, after the file has already loaded, there’s no built-in event that reacts to that change on its own.

Fix: This requires a small VBA macro tied to a workbook event rather than a manual click. Open the VBA editor (Alt+F11), double-click “ThisWorkbook” in the Project pane, and add code inside the Workbook_SheetChange event so that any edit to the source sheet triggers a refresh of every pivot table in the workbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim pt As PivotTable
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

Save the workbook as a macro-enabled file (.xlsm) — this step trips people up constantly, since saving as a regular .xlsx silently strips the code out with no warning. If you want the refresh tied to a timer instead of an edit, Workbook_Open combined with Application.OnTime will re-fire the refresh at a fixed interval rather than waiting for a change event.

Symptom: The Pivot Table Is Fine, but It’s Pulling From an External Source That Updates on Its Own Schedule

Cause: When your pivot table is built on a Power Query connection, a SQL database, or an external workbook rather than a local range, the refresh problem shifts location entirely — it’s no longer about the pivot table itself, but about how often the connection behind it checks for new data.

Fix: Click the pivot table, go to the Data tab on the ribbon, and open Queries & Connections. Right-click the relevant connection, choose Properties, and look for “Refresh every X minutes” along with “Refresh data when opening the file.” Set the interval you need — five minutes for something near-live, longer for a report that just needs to be reasonably current.

Keep in mind this refreshes the connection’s cache, not necessarily the pivot table sitting on top of it automatically in every version of Excel. In older builds, you may still need “Refresh all PivotTables connected to this connection” checked in the same properties dialog, otherwise the underlying query updates while the pivot table display lags one refresh behind it.

Symptom: You Need the Workbook to Refresh Even When Nobody Has It Open

Cause: Every method above depends on the file being open in Excel at the moment the refresh needs to happen — a VBA event, a connection timer, and the open-on-launch checkbox are all useless if no one launches the file. If your requirement is closer to “this report should be current every morning at 7am regardless of whether anyone opens it,” none of those mechanisms apply.

Fix: This calls for Windows Task Scheduler paired with a short VBA macro that runs on open, refreshes everything, saves, and closes. Build a macro like this in the workbook:

Sub AutoRefreshAndClose()
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    ThisWorkbook.Save
    Application.Quit
End Sub

Then create a Windows batch file that opens Excel, points it at the workbook, and triggers that macro, and schedule the batch file to run daily through Task Scheduler. This is the least elegant option on this list — it depends on a machine being powered on at the scheduled time, and debugging a scheduled task that fails silently overnight is not fun — but it’s the only approach that doesn’t require a human to open the file first.

Symptom: You’re on Excel for the Web or Microsoft 365, and VBA Isn’t an Option

Cause: VBA doesn’t run in the browser version of Excel, and increasingly, organizations are shifting workflows toward cloud-based files where a desktop macro simply isn’t available at all.

Fix: Office Scripts, combined with Power Automate, cover this gap. Record or write an Office Script that calls workbook.getPivotTables() and refreshes each one, save that script against the workbook, and then build a Power Automate flow that runs the script on a schedule — hourly, daily, or triggered by an event like a new file landing in a SharePoint folder. This setup lives entirely in the cloud, so no local machine needs to stay powered on, unlike the Task Scheduler method above.

Symptom: The Pivot Table Refreshes, but the Column Widths and Number Formatting Reset Every Time

Cause: This one isn’t a refresh failure — it’s a side effect of the “Autofit column widths on update” and “Preserve cell formatting on update” settings inside PivotTable Options, both of which control what happens to layout every time a refresh runs, not whether the refresh itself succeeds.

Fix: Right-click the pivot table, open PivotTable Options, and on the Layout & Format tab, uncheck “Autofit column widths on update” and confirm “Preserve cell formatting on update” is checked. Refresh once to apply the change, and your column widths and formatting should hold steady through every future refresh instead of snapping back to default sizing.

Choosing Between These Five Methods

None of these methods substitute for another — a connection refresh interval won’t help if your real problem is that the file only ever gets opened once a week and needs fresh data at that moment, and a VBA event won’t do anything for a workbook that’s never open when the update needs to happen. Match the fix to where in the chain your data is actually going stale, and the right mechanism is usually obvious once you’ve named the symptom correctly.

Which of these five situations matches what you’re dealing with? That’s the fastest way to skip straight to the fix that will hold.