What Most People Miss About How to Change Data Source in Excel

By Tom Bradley
Yes, you can change the data source in Excel. But if you do it without updating connection properties *and* checking external references, your dashboard will silently misreport Q3 revenue by $217,400.

The Setup

You’re supporting a regional sales dashboard for Alibaba Cloud’s APAC partners. It pulls from a live Excel file named Q3_Sales_Raw_2024.xlsx, stored on SharePoint at https://alibabacloud.sharepoint.com/sites/finance/APAC/Sales/. The original source range is '[Q3_Sales_Raw_2024.xlsx]APAC'!$A$1:$F$892 — 892 rows of partner transactions. Here’s a realistic slice (rows 1–9 of that sheet):
Partner ID Partner Name Region Deal Size ($) Close Date Status
P-7821 TechNova Solutions Japan $142,600 2024-07-12 Closed
P-7822 CloudBridge HK Hong Kong $89,300 2024-07-15 Closed
P-7823 DataSphere SG Singapore $204,150 2024-07-18 Closed
P-7824 Aether Systems Australia $67,800 2024-07-20 Pending
P-7825 Nexus Tech PH Philippines $32,500 2024-07-22 Closed
P-7826 Veridian Labs South Korea $176,900 2024-07-24 Closed
P-7827 Orion Cloud JP Japan $112,400 2024-07-26 Pending
P-7828 StellarNet MY Malaysia $58,700 2024-07-28 Closed
Your dashboard uses this as the source for:
  • A PivotTable in Sheet2, anchored at B3
  • A dynamic array formula in Sheet1: =FILTER('Q3_Sales_Raw_2024.xlsx'!A2:F892, 'Q3_Sales_Raw_2024.xlsx'!F2:F892="Closed") (in C10)
  • A named range SalesRaw pointing to '[Q3_Sales_Raw_2024.xlsx]APAC'!$A$1:$F$892

The Challenge

Finance just moved the source file to a new location: https://alibabacloud.sharepoint.com/sites/finance/APAC/Sales/Q3_Sales_Cleaned_2024.xlsx. It has the same structure—but now includes two extra columns (‘Renewal Flag’ and ‘SLA Tier’) and 1,024 rows. You need to update all three dependencies without breaking anything. The trap? Most people just edit the formula or pivot table range—and stop there. They don’t realize Excel caches connection metadata separately from cell formulas. So even after changing C10’s FILTER range to point to the new file, the PivotTable still queries the old file (which no longer exists), and SalesRaw stays frozen in memory until manually refreshed. What makes this elegant is that Excel actually stores *two* layers of source info: one visible (formulas), and one invisible (connection objects). You must update both—or you’ll get #REF! errors *or worse*, silent stale data.

Walking Through It

Start by opening the new source file first. That prevents Excel from auto-correcting paths to the old file during edits. Step 1: Update the FILTER formula (C10)
Edit cell C10. Change: =FILTER('[Q3_Sales_Raw_2024.xlsx]APAC'!A2:F892, '[Q3_Sales_Raw_2024.xlsx]APAC'!F2:F892="Closed")
to: =FILTER('[Q3_Sales_Cleaned_2024.xlsx]APAC'!A2:H1024, '[Q3_Sales_Cleaned_2024.xlsx]APAC'!H2:H1024="Closed") Note the column shift: Status moved from F to H, and row count increased to 1024. Press Enter. Before:
Partner Name Deal Size ($) Close Date
TechNova Solutions $142,600 2024-07-12
CloudBridge HK $89,300 2024-07-15
After:
Partner Name Deal Size ($) Close Date SLA Tier
TechNova Solutions $142,600 2024-07-12 Gold
CloudBridge HK $89,300 2024-07-15 Silver
Step 2: Update the PivotTable (Sheet2!B3)
Click any cell inside the PivotTable. Go to PivotTable Analyze → Data → Change Data Source. In the dialog, type or paste the new range:
'[Q3_Sales_Cleaned_2024.xlsx]APAC'!$A$1:$H$1024 Click OK. Step 3: Refresh the named range SalesRaw
Press Ctrl+F3 to open Name Manager. Select SalesRaw. In the “Refers to” box, replace the old path with: '[Q3_Sales_Cleaned_2024.xlsx]APAC'!$A$1:$H$1024 Click OK → Close. Now hit Alt+F5 to refresh all connections at once.

The Result

All three components now pull from the updated source. Your PivotTable shows correct counts across SLA Tiers. The FILTER spills cleanly into new columns. And =ROWS(SalesRaw) returns 1024—not 892.
SLA Tier Count Total Revenue ($) Avg Deal Size ($)
Gold 142 $22,418,500 $157,876
Silver 287 $18,934,200 $65,973
Bronze 312 $9,721,300 $31,158
Platinum 48 $15,203,800 $316,746

What Could Go Wrong

Here are the three mistakes we see most often in finance ops teams — each with exact symptoms and fixes:
  • Mistake 1: Editing the formula but not refreshing the connection object
    Result: PivotTable throws “External table is not accessible” on refresh, even though the formula works fine. Why? Excel keeps a cached ODC file behind the scenes. Fix: Go to Data → Queries & Connections → right-click the connection → Properties → Connection String → Edit path manually.
  • Mistake 2: Forgetting column shifts when expanding the source
    Result: FILTER returns #VALUE! because status is now in column H, not F — but you updated only the range, not the condition logic. Always verify column positions using =CELL("address", [new_file.xlsx]APAC!H1) before editing.
  • Mistake 3: Using relative paths in named ranges
    Result: SalesRaw resolves correctly on your machine but fails for colleagues because [Q3_Sales_Cleaned_2024.xlsx] opens from their Downloads folder, not SharePoint. Fix: Replace bracketed filenames with full URLs like 'https://alibabacloud.sharepoint.com/.../Q3_Sales_Cleaned_2024.xlsx'!A1:H1024.

Quick Reference: Key Shortcuts & Checks

Action Shortcut When to Use
Open Name Manager Ctrl+F3 Updating named ranges referencing external files
Refresh All Connections Alt+F5 After editing formulas, pivot sources, or connection strings
View External Links Alt+E, K Audit which files your workbook depends on
Edit Connection Properties Right-click in Queries & Connections pane → Properties Fix broken links or update authentication
Tom Bradley

Tom Bradley

Tom has 15 years of experience in office management and supply chain optimization. He shares practical tips for running efficient workplaces.