Why does hitting Refresh All do nothing when your source file changed? Why does F9 recalculate but leave old numbers in your pivot table? Why does Ctrl+Alt+F5 work on one workbook but crash another?
Quick Answer
"How to reload Excel" isn’t one action — it’s three distinct operations: (1) recalculating formulas (F9), (2) refreshing external data connections (Alt+A+R+A), and (3) reloading Power Query queries (Data > Refresh All or Alt+F5). Doing just one rarely fixes everything.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Formula Recalculation | Press F9 (full recalc) or Shift+F9 (active sheet only) | Cells with TODAY(), RAND(), or volatile functions; broken SUMIFS references | Does NOT update Power Query tables, pivot caches, or linked data connections |
| External Data Refresh | Alt+A+R+A → choose connection → click Refresh | SQL Server, CSV, or web queries in range A1:D12; live sales dashboards | Fails silently if source file is moved or credentials expired; no version rollback |
| Power Query Reload | Data tab → Refresh All (Alt+F5) or right-click query → Refresh | Transformed datasets from Excel files, APIs, or SharePoint lists | Stalls if M code has errors; doesn’t auto-refresh dependent queries unless "Refresh this connection" is checked |
| Workbook Reopen | File → Close → reopen; or Ctrl+O → select same file | When cached connections refuse to refresh after edits to source files | Loses unsaved formula edits; resets undo stack; slow for 50MB+ files |
| Manual Cache Clear | File → Options → Data → uncheck "Enable background refresh", then Refresh All | Stuck 'Refreshing...' status bars; ghost data from deleted rows | Requires restarting Excel to re-enable background refresh; affects all workbooks |
Method 1 Deep Dive
Let’s say you pulled Q1 sales data from a shared folder: C:\Reports\Q1_Sales_2024.xlsx. Your workbook has a connection named "Sales_Q1" pulling into Sheet2, cells A1:F287. Yesterday, the source file got updated with 12 new orders — but your pivot table still shows $428,610 instead of $441,230.
You press F9. Nothing changes. That’s because F9 only touches formulas — not the cached result set sitting in A1:F287. You need to refresh the connection.
Here’s what actually works: Go to the Data tab → click Connections (in the Queries & Connections group) → select "Sales_Q1" → click Refresh. Or faster: Alt+A+R+A, then pick "Sales_Q1" from the list.
But here’s what most people miss: Right-click the connection in the Workbook Connections dialog → Properties → check "Refresh data when opening the file". Now next time Sarah Chen opens this report before her 9 a.m. sync, it’ll pull fresh numbers automatically — no manual refresh needed.
Sample data in Sheet2 after refresh:
A1: OrderID B1: Customer C1: Date D1: Amount
A2: ORD-8821 B2: Acme Corp C2: 2024-03-15 D2: $12,620
A3: ORD-8822 B3: NexaTech Ltd C3: 2024-03-16 D3: $8,940
…
A288: ORD-8932 B288: Lumina Group C288: 2024-03-22 D288: $15,320
Method 2 Deep Dive
Now imagine your finance team uses Power Query to merge three regional Excel files into one master table. The query is called "Master_Sales_Full" and loads to Sheet3 starting at cell A1. You edited the source file APAC_Sales.xlsx, but clicking Refresh All does nothing — or worse, gives "Expression.Error: The key didn't match any rows in the table."
This happens because Power Query caches the *schema* (column names, data types) — not just values. If someone added a new column "Discount_Code" to APAC_Sales.xlsx but didn’t update the query’s expected columns, it breaks.
Fix it like this: Go to Data tab → Queries & Connections → expand the Queries pane → right-click "Master_Sales_Full" → Edit. In Power Query Editor, go to Home tab → Advanced Editor. Look for this line:Source = Excel.Workbook(File.Contents("C:\Data\APAC_Sales.xlsx"), null, true)
Add , null, true) at the end — yes, it’s already there. The real fix? Click Close & Load To… → choose Only Create Connection → uncheck "Add this data to the Data Model" → click OK. Then right-click the query again → Refresh. This forces a full schema re-read.
Surprising tip: If you see "Loading..." forever, don’t close Excel. Press Ctrl+Break (or Esc on some keyboards) — it cancels the current load step without killing the whole process. Then go to View tab → Query Diagnostics → check "Enable diagnostics" to see which step hung.
After successful reload, Sheet3 shows:
A1: Region B1: Rep C1: Product D1: Units E1: Revenue
A2: APAC B2: Lena Park C2: CloudSuite Pro D2: 14 E2: $32,760
A3: EMEA B3: Tomas Ribeiro C3: CloudSuite Pro D3: 9 E3: $20,970
…
A412: AMER B412: Maya Torres C412: CloudSuite Lite D412: 22 E412: $17,380
Cheat Sheet
| Action | Keyboard Shortcut | Where It Works | When to Use It |
|---|---|---|---|
| Recalculate all formulas | F9 | All worksheets; includes volatile functions | After editing a formula in C5 that references TODAY() or OFFSET() |
| Refresh all data connections | Alt+A+R+A | ODBC, OLE DB, web, text, and Excel connections | When source files were updated outside Excel |
| Reload Power Query queries | Alt+F5 | Only queries loaded via Get & Transform | After editing source files used by PQ — especially when column count changed |
| Cancel stuck refresh | Ctrl+Break | During Power Query or connection refresh | When Excel hangs on "Loading..." for >30 seconds |
| Force full cache clear | File → Options → Data → uncheck "Enable background refresh" → Refresh All | All external connections and Power Query results | When refresh completes but data remains unchanged |
| Reopen with fresh cache | Ctrl+O → select same file → Open | All cached connections, pivot caches, and formula dependencies | As last resort — especially after changing file paths or renaming source sheets |
