It’s 3:12 PM. You just emailed your sales forecast to the leadership team. At 3:18, Sarah Chen in AP flags a $14,750 invoice correction in QuickBooks. By 3:22, your printed report is already outdated—and you haven’t even closed the file.
Quick Answer
You make an Excel sheet a live document by connecting it to dynamic data sources—not copying and pasting—so changes elsewhere (like SharePoint lists, SQL databases, or even another Excel file) automatically appear in your workbook when refreshed, or in real time if using Power Query with scheduled refresh or Excel for the web with co-authoring enabled.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Power Query + Data Model | Get Data > From File > From Workbook > Select range > Enable "Refresh data when opening file" | Cross-workbook reports updated daily | Doesn’t auto-refresh unless opened or manually triggered |
| Excel for the Web + Co-Authoring | Save to OneDrive/SharePoint > Open in browser > Multiple users edit simultaneously | Team dashboards updated in near real time | No VBA; limited formatting during co-editing |
| Dynamic Array Formulas + INDIRECT | =INDIRECT("'"&A1&"'!B2:C10") where A1 holds external filename | Lightweight links between same-network files | Breaks if source file is renamed/moved; volatile |
| SharePoint List Connection | Data > Get Data > From Online Services > From SharePoint Online List | HR headcount trackers or CRM syncs | Requires SharePoint license & admin permissions |
| Excel REST API via Power Query | Advanced Editor > Paste JSON API URL > Transform schema | Live stock prices, currency rates, or ERP feeds | Needs auth tokens; fails silently on timeout |
Method 1 Deep Dive
Let’s connect to another Excel file on your network—say, Sales_Q3_2024.xlsx, stored in \\fs01\finance\live_data\. You want Sheet1, columns A:C (Date, Product, Revenue), updating every time you open your dashboard.
Open your dashboard file. Go to Data > Get Data > From File > From Workbook. Browse to the Q3 file. In the Navigator, check only Sheet1. Click Transform Data. In Power Query Editor, remove any unwanted rows (like headers duplicated in the source). Then go to Home > Close & Load To… → choose Only Create Connection, then click Load To and select Table in existing worksheet (say, starting at cell F1).
Now go to Data > Queries & Connections. Right-click the new query (named something like Sheet1 (2)) → Properties. Check Refresh data when opening the file. Also check Refresh every and set to 60 minutes—if you’re using Excel for Microsoft 365.
Here’s the counterintuitive part: Don’t use =SUM() directly on the imported table. Instead, create a PivotTable or use structured references like =SUM(Table1[Revenue]). Why? Because Power Query loads into a Table object—not a static range—and formulas referencing raw cell addresses (e.g., =SUM(F2:F1000)) break when new rows arrive. (Trust me, I learned this the hard way after a board meeting where totals vanished mid-presentation.)
Sample imported data looks like this:
| Date | Product | Revenue |
|---|---|---|
| 2024-07-01 | CloudSync Pro | $24,890 |
| 2024-07-02 | Acme Corp License | $18,320 |
| 2024-07-03 | Nexus Analytics Suite | $31,600 |
| 2024-07-04 | CloudSync Pro | $22,150 |
| 2024-07-05 | Acme Corp License | $19,940 |
Method 2 Deep Dive
For true collaboration—where live means “someone else just changed cell B7 and you see it now”—use Excel for the Web + co-authoring.
First, save your workbook to OneDrive or SharePoint. Then open it in Chrome or Edge—not the desktop app. Click the Share button top-right. Invite colleagues with Edit permissions. When they open it, you’ll see their initials appear next to cells they’re editing.
Important: To preserve formulas while allowing live edits, avoid merged cells and volatile functions like =TODAY() or =INDIRECT() in shared ranges. Instead, put timestamps in a separate tab or use =NOW() only in non-shared areas.
Keyboard shortcut alert: Press Alt+R, C to quickly toggle between Review > Comments and Review > Track Changes—though note: Track Changes is disabled in co-authoring mode. Use comments instead. (Yes, it’s weird. Yes, Microsoft knows.)
Real-world example: The finance team at Brightline Logistics shares Inventory_Tracker_2024.xlsx across 7 warehouse managers. Each updates their own row in column D (Units Received). No refresh needed. No version conflicts. Just type, and everyone sees it—usually within 2–3 seconds.
Cheat Sheet
| Task | How | Shortcut / Tip |
|---|---|---|
| Force-refresh all queries | Data > Refresh All | Alt+A, R, A |
| Open Queries & Connections pane | Data > Queries & Connections | Alt+D, Q |
| Check auto-refresh status | Right-click query > Properties > Refresh control | Tick “Refresh every X minutes” only if file stays open |
| Verify live co-authoring is active | Look for “Co-authoring enabled” in top banner (web) or presence icons (desktop) | If missing, file isn’t saved to cloud or sharing is read-only |
| Fix broken external links | Data > Edit Links > Change Source | Alt+D, L, C — then browse to new location |
