Press Ctrl+Alt+F5 to refresh all external data connections in Excel. But if you think that’s all there is to ‘how to refresh Excel’, you’ll keep getting stale numbers in dashboards, mismatched pivot tables, and angry follow-ups from finance.
The Problem
Your sales dashboard shows Q1 revenue at $1.24M. Finance just sent an updated CSV: actuals are $1.37M. You double-click the pivot table. You hit F5. Nothing changes. The chart still says $1.24M. You close and reopen Excel. Still wrong. You restart your laptop. Still wrong.
That’s because Excel doesn’t auto-refresh — and most people confuse
redrawing the screen (F5) with
reloading live data (Ctrl+Alt+F5 or Data > Refresh All). Worse, some connections refresh on open, others don’t. Some require manual permission. Some time out silently.
Here’s what your sheet looks like *before* a proper refresh — notice the mismatch between source data and pivot summary:
| Sales Rep | Region | Q1 Forecast ($) | Q1 Actual ($) |
|---|
| Sarah Chen | APAC | $285,000 | $261,400 |
| Diego Mora | EMEA | $312,500 | $338,900 |
| Amina Patel | NA | $410,200 | $452,100 |
| Kenji Tanaka | APAC | $224,800 | $217,300 |
| Lena Dubois | EMEA | $297,600 | $301,800 |
This table (A1:D6) pulls from a Power Query connection named "Q1_Sales_Raw". But the pivot table in Sheet2 (based on that query) still uses cached values from March 12 — even though the source file was updated March 18. No warning. No error. Just wrong numbers.
The Solution
Do this — in order — every time you suspect data is stale:
1. Go to the
Data tab → click
Refresh All (or press
Ctrl+Alt+F5). This refreshes all queries, pivot caches, and external links in the workbook.
2. If you get a security warning (“Data connections have been disabled”), click
Enable Content. Then refresh again.
3. Check the status bar: it will say “Refreshing…” then “All done” — or show an error. If it fails, don’t ignore it. Right-click any affected pivot table →
Refresh, then check the query editor for red error icons.
4. For one-off connections (like a single SQL query in cell A10), right-click the output range (e.g., A10:C25) →
Refresh. This bypasses full workbook refresh and targets only that result set.
After doing those steps, here’s what the same table looks like — now synced with the live source:
| Sales Rep | Region | Q1 Forecast ($) | Q1 Actual ($) |
|---|
| Sarah Chen | APAC | $285,000 | $283,100 |
| Diego Mora | EMEA | $312,500 | $338,900 |
| Amina Patel | NA | $410,200 | $452,100 |
| Kenji Tanaka | APAC | $224,800 | $242,700 |
| Lena Dubois | EMEA | $297,600 | $301,800 |
| Total | | $1,530,100 | $1,618,600 |
Notice how Sarah Chen’s actual jumped from $261,400 → $283,100, and Kenji Tanaka’s from $217,300 → $242,700. That’s the difference between a missed forecast review and an actionable insight.
How do I refresh Excel? — The Real Answer
It depends on what you’re trying to refresh.
- To reload formulas and screen layout (no data change): press
F9. This recalculates all open workbooks — but won’t pull new data from SQL, SharePoint, or CSV files.
- To redraw the screen only (e.g., fix display glitches): press
F5 → Enter → Enter. This forces a repaint. Use it if cells look corrupted, but never for data.
- To reload all external data sources:
Ctrl+Alt+F5 — this is the only shortcut that hits *every* query, pivot cache, and linked table.
- To refresh just one pivot table: select any cell inside it →
Alt+J+R+R (Alt → Data tab → Refresh → Refresh).
Yes,
Alt+J+R+R works even if the Data tab isn’t visible. Try it now.
Going Further
Don’t wait for stale data to cause problems. Automate smart refresh behavior.
Set automatic refresh on open: Right-click any query in the Queries & Connections pane →
Properties → check
Refresh data when opening the file. Do this for every query you rely on daily — but skip it for slow or unstable connections.
Use background refresh: In the same Properties dialog, check
Enable background refresh. Excel will fetch new data without freezing your UI. Critical if your SQL query takes >12 seconds.
Force-refresh a specific connection without touching others: Go to
Data →
Queries & Connections → right-click “Sales_Forecast_2024” →
Refresh. This avoids re-running 17 other queries just to update one.
Surprising tip: If you copy-paste values from a refreshed pivot into another sheet (e.g., paste as values into G1:I10), those values won’t auto-refresh. But if you use
=GETPIVOTDATA() formulas referencing that pivot, they *will* update when the pivot refreshes — no extra action needed. Example:
=GETPIVOTDATA("Sum of Actual",Sheet2!$A$3,"Sales Rep","Sarah Chen") in cell G1 pulls live pivot values, not static snapshots.
For Power Pivot models: Press
Alt+D+P+R to refresh the entire data model — including relationships and calculated columns. This is separate from regular Power Query refresh.
When NOT to Use This
Refreshing isn’t always safe — or necessary.
Never refresh during a live presentation. You risk timeouts, blank pivot tables, or credential prompts. Pre-refresh before sharing the file — then save as Values Only (Copy → Paste Special → Values) if distribution requires immutability.
Don’t refresh if your workbook contains volatile functions like
TODAY(),
NOW(), or
RAND() and you need consistent timestamps across analysis runs. Refreshing recalculates those — changing results unpredictably.
Avoid
Ctrl+Alt+F5 if you’ve made unsaved edits to a Power Query step. Refreshing discards uncommitted query changes. Always click
Close & Load first — or use
Close & Load To… to preview before committing.
Skip refresh entirely for internal Excel tables (e.g., Table1 in Sheet1, referenced by =SUM(Table1[Amount])). Those update instantly — no refresh needed.
If your source system blocks repeated queries (e.g., SAP BW or legacy ERP), aggressive auto-refresh can trigger rate limits. Confirm with IT which connections allow hourly vs. daily refresh.
Keyboard Shortcuts
Memorize these — they’ll save 3+ minutes per day:
| Action | Shortcut | Notes |
|---|
| Refresh all queries & pivots | Ctrl+Alt+F5 | Only shortcut that covers everything |
| Recalculate formulas only | F9 | Ignores external data sources |
| Refresh active pivot table | Alt+J+R+R | Works even with ribbon hidden |
| Open Queries & Connections pane | Alt+D+Q | Then arrow keys + Enter to navigate |
| Refresh Power Pivot model | Alt+D+P+R | Different engine — needs its own refresh |
| Toggle manual/auto calculation | Alt+MX | Prevents accidental recalc during editing |
