Why does your pivot table still show Q1 sales after you updated the source sheet? Why does Alt+F5 do nothing when you’re certain the connection is live? Why does refreshing work fine in one workbook but fails silently in another?
Quick Answer
You refresh data in Excel by triggering an update for external connections, pivot tables, or Power Query queries — but which method works depends entirely on where your data lives. A right-click on a pivot table won’t refresh a SQL-linked table; pressing Ctrl+Alt+F5 won’t reload a Power Query query unless it’s set to auto-refresh on open. It’s not broken — it’s mismatched.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Right-click → Refresh (PivotTable) | Select any cell inside the pivot table → right-click → Refresh | PivotTables built from worksheet ranges or existing data models | Won’t update underlying Power Query or external database connections |
| Data tab → Refresh All | Go to Data tab → click Refresh All (or press Alt+A+R+A) | Workbooks with multiple connections (SQL, ODBC, web, etc.) | Refreshes everything — including slow connections you didn’t intend to trigger |
| Power Query Editor → Refresh | Go to Data tab → Get Data → Launch Power Query Editor → select query → Home tab → Refresh | Queries loaded to worksheet or data model (e.g., cleaned CRM exports) | Doesn’t auto-refresh pivot tables built on that query unless they’re connected to the Data Model |
| Keyboard shortcut: Ctrl+Alt+F5 | Press Ctrl+Alt+F5 anywhere in the workbook | Quick full refresh when all connections are trusted and local | Fails silently if any connection requires credentials or has background refresh disabled |
| Connection Properties → Auto-refresh | Right-click connection → Properties → check Refresh data when opening file | Reports shared with stakeholders who need up-to-date numbers on open | Can delay workbook open time — especially with large or remote datasets |
Method 1 Deep Dive
Let’s say you have a pivot table in Sheet1, built from data in Sheet2 (A1:D12), which itself pulls from a CSV export of your ERP system. You just replaced Sheet2!A1:D12 with fresh rows — but your pivot table still shows last week’s figures.
Here’s what’s really happening: Excel doesn’t auto-detect changes to the source range unless you tell it to. Even though Sheet2 now contains new data, the pivot cache hasn’t been told to reload.
Do this instead: Click any cell inside the pivot table (say, B5). Right-click → Refresh. That’s it. The pivot will now pull from Sheet2’s current values — not its cached snapshot from last Tuesday.
But here’s the surprise: If your pivot table uses Sum of Revenue and you’ve added a new column called Discount % to Sheet2, right-clicking won’t add that field to the pivot. You’ll need to go to PivotTable Analyze → Change Data Source → extend the range manually to include the new column (e.g., change Sheet2!$A$1:$D$12 to Sheet2!$A$1:$E$15). Trust me, I learned this the hard way during a quarterly review — and had to re-run the whole dashboard.
Method 2 Deep Dive
Now imagine your sales team uploads a weekly report to SharePoint: https://alibaba.sharepoint.com/sites/sales/Reports/Q3-2024-Sales.xlsx. You connect to it via Power Query — and load it into the Data Model. Then you build two pivot tables: one on Sheet3 (Sales by Region), one on Sheet4 (Top 10 Customers).
If you just refresh the pivot on Sheet3, Sheet4 stays frozen. Why? Because Power Query loads data into the model once — then pivots reference that model, not the original query. So refreshing one pivot doesn’t touch the query.
Open Power Query Editor (Alt+D+B). In the Queries pane, find Q3-2024-Sales. Click the down arrow next to its name → Refresh. Or better yet: select it and press Ctrl+R. Watch the status bar: “Loading data… 42,617 rows”. Once done, both pivot tables will reflect the update — even if you never touched them.
Pro tip: If your query has parameters (like a date filter), changing the parameter value won’t trigger refresh automatically. You must manually refresh after editing — and yes, that includes typing a new date in a parameter cell like F2 and then hitting Ctrl+R. No amount of clicking ‘Refresh All’ bypasses that step.
Cheat Sheet
| Scenario | Action | Shortcut | Notes |
|---|---|---|---|
| PivotTable built from worksheet range | Right-click any cell in pivot → Refresh | None | Only updates cache — not source data |
| Multiple external connections (SQL, web, etc.) | Data tab → Refresh All | Alt+A+R+A | Respects connection timeouts and credentials |
| Power Query query loaded to Data Model | In Power Query Editor → select query → Home → Refresh | Ctrl+R | Required before pivots referencing the model update |
| Auto-refresh on file open | Right-click connection → Properties → check box | None | Works only for trusted connections — not password-protected ones |
| Quick full refresh (all connections + pivots) | Data tab → Refresh All | Ctrl+Alt+F5 | If it fails, check Status Bar — often says “Refresh failed” quietly |
