How to merge data from closed workbooks in Excel
By the Analistable team · Updated · 5 min read
Direct external references like ='C:\Reports\[North.xlsx]Sheet1'!B10 and lookups such as XLOOKUP or SUMPRODUCT with plain ranges can read from a closed workbook. INDIRECT, SUMIF/SUMIFS and COUNTIF/COUNTIFS return #REF! or #VALUE! when the source is closed. For anything more than a few cells, use Power Query, which reads closed files and refreshes on demand.
Part of our guide: How to merge Excel files: every method compared
What works with closed files
| Approach | Works when source is closed? | Notes |
|---|---|---|
| Direct reference ='[File.xlsx]Sheet1'!B10 | Yes (last saved values) | Full path stored once the file closes |
| XLOOKUP / VLOOKUP / INDEX-MATCH to another file | Yes | Values update on refresh or open |
| SUM / SUMPRODUCT over a closed range | Yes | SUMPRODUCT can replace SUMIFS |
| SUMIF(S), COUNTIF(S), AVERAGEIF(S) | No: #VALUE! | They need the source open |
| INDIRECT built from text | No: #REF! | Can't resolve closed files |
| Power Query | Yes | Best for whole tables or many files |
Excel caches the last values it read, so a workbook with links can show numbers even when the sources are offline. When you open it, Excel may ask whether to update links; choose Update to re-read the sources. Details of cross-workbook lookups are in XLOOKUP from another workbook.
Example: totals from three closed regional files
Each regional workbook has its total in B10 of a sheet named Summary. In the master file:
='C:\Reports\[North.xlsx]Summary'!$B$10
='C:\Reports\[South.xlsx]Summary'!$B$10
='C:\Reports\[West.xlsx]Summary'!$B$10| Region | Total |
|---|---|
| North | 48200 |
| South | 61950 |
| West | 39400 |
| All regions | 149550 |
The easiest way to create a reference is to open the source, type = in the master, click the cell in the source, then close the source; Excel rewrites the formula with the full path.
Replacing SUMIFS for closed files
SUMIFS needs the source open. SUMPRODUCT does the same job and works on closed workbooks because it takes arrays rather than range-only arguments:
=SUMPRODUCT(('C:\Reports\[North.xlsx]Orders'!$C$2:$C$5000="Mugs")
*'C:\Reports\[North.xlsx]Orders'!$E$2:$E$5000)Use a fixed range rather than whole columns: whole-column SUMPRODUCT over an external file is slow. In Microsoft 365, SUM(FILTER(...)) referencing the closed file also works.
Why INDIRECT fails
INDIRECT turns text into a reference, so it's tempting for building file names from a list ("["&A2&".xlsx]"). But it only resolves workbooks that are open, so every row shows #REF! as soon as you close them. It's also volatile, so it recalculates often. There's no built-in fix; old add-ins worked around it, but Power Query is the supported approach.
Power Query: the reliable route
- Data > Get Data > From File > From Workbook for one file, or From Folder for many.
- Pick the sheet or table and transform as needed (filter, keep columns, group).
- For many files, use Combine & Transform; Power Query applies the same steps to each.
- Close & Load to a table or PivotTable. Data > Refresh All re-reads the closed files.
Power Query never needs the source files open, handles full tables rather than single cells, and doesn't break when you add rows. Keeping the sources in a fixed folder (or a SharePoint/OneDrive folder) makes refresh painless. For wider context see the Excel file merging guide and combining files from a folder.
Managing links
- Data > Edit Links (or Workbook Links in newer Microsoft 365 builds) lists every source, lets you update values, change the source to a different file, or break links to keep values only.
- Moving or renaming source files breaks links; use Change Source to repoint them.
- External links in the cloud: references between files in OneDrive or SharePoint use URLs and generally work in Excel for the web, though behaviour depends on permissions.
Analistable never reads files from your disk on its own; you choose the workbooks to load into your browser, and it queries them together without linking or modifying them. That avoids broken links entirely, but it's a separate tool rather than a formula inside your master workbook.
Which approach for which job
For a handful of headline numbers that rarely change position, direct references are fine and easy for others to follow. For lookups against a list kept in another file, XLOOKUP or INDEX-MATCH to the closed file works, but keep the source's layout stable. For conditional totals, SUMPRODUCT works where SUMIFS doesn't. For whole tables, many files, or anything that will grow, use Power Query: it is designed for reading files that aren't open and won't break when rows are added.
Avoid mixing approaches in one master file where possible. Links and queries refresh differently (links on open or via Edit Links, queries via Refresh All), and someone will refresh one and not the other.
Sharing a master workbook
Links store file paths. If you email the master or colleagues open it from a different drive letter, every reference points to a location that doesn't exist on their machine and Excel shows the cached values or errors. Keep sources on a shared location everyone sees with the same path, or use Power Query with a parameter for the folder so it can be changed in one place.
Frequently asked questions
- Do external links work in Excel for the web?
- Links between workbooks stored in OneDrive or SharePoint are supported in Excel for the web, but links to files on a local drive can't be refreshed there.
- Is Power Query available on Mac?
- Yes, recent Microsoft 365 versions of Excel for Mac include Power Query with file and folder connectors, though some sources available on Windows are missing.
- Will Power Query open the source files?
- No. It reads them in the background without opening them in Excel, so they don't need to be open and other people can keep working in them, though a file locked for editing may fail to read.
- Why does SUMIF return #VALUE! from another workbook?
- SUMIF, SUMIFS, COUNTIF and similar functions need range references that are open. Open the source, or switch to SUMPRODUCT.
- Can VLOOKUP work with a closed workbook?
- Yes. A VLOOKUP whose table_array points to another workbook reads the last saved values even when that file is closed.
- How do I stop the 'update links' prompt?
- In Edit Links, use Startup Prompt to choose whether to update automatically or not ask. Organisation security settings may override this.
- Is there a way to make INDIRECT work with closed files?
- Not with built-in Excel. Use Power Query, or open the sources before calculating.