Fix Excel Cannot Open the Source File for Consolidation
Question details
The user needs to resolve an Excel error indicating that a selected source workbook cannot be opened during data consolidation.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is combining ranges from one or more source workbooks by using Excel's Consolidate feature.
- Observed behavior
- Excel cannot open a source file selected for consolidation, preventing the consolidation from completing.
Keep the source workbook names and folder locations available before updating the consolidation references. If the files are on a shared drive, confirm that the drive is currently connected and accessible in File Explorer.
Verify the source workbook path, access, and availability
Use this first when Excel cannot locate or read a workbook selected as a consolidation source.
Consolidation can fail when a source workbook was renamed, moved, deleted, opened exclusively by another program, or stored on an unavailable network location.
Open File Explorer and browse to the folder containing the source workbook. Confirm that the file name and extension, such as .xlsx or .xls, match the workbook you selected in Excel.
Double-click the source workbook in File Explorer, or select File > Open in Excel and open it directly. If it does not open, resolve the file access or file damage issue before consolidating.
Ask other users to close the source workbook and close any application that may be using it. For a shared location, right-click the file, select Properties, and confirm that your account has permission to read the file.
If the workbook is on a network share, reconnect to the network location in File Explorer. Then copy the source workbook to a local folder such as Documents and select that local copy in Data > Consolidate.
In Excel, open the destination workbook, select Data > Consolidate, choose the required function, click Browse or use the Reference box to select the accessible source range, and then click Add.
Check the source workbook format and repair Excel
Use this when the file opens directly but Excel still cannot use it for consolidation.
Consolidate Multiple Workbook Ranges in WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet environment for combining data from multiple ranges. Open accessible source workbooks, use the Consolidate command, and select local or shared ranges to create a summarized result in a destination sheet.
- 1. Open the destination workbook: Launch WPS Spreadsheets and open the workbook where you want the consolidated results to appear.
- 2. Make the source files accessible: Open each source workbook once from File > Open, or place the source workbooks in an available local or shared folder.
- 3. Choose the consolidation command: Select the destination cell, then go to the Data tab and choose Consolidate.
- 4. Add source ranges: Select a consolidation function, enter or select each source range, click Add for each reference, and choose labels if your ranges include headers.
- 5. Create the consolidated result: Click OK to write the combined values to the destination worksheet.
Frequently Asked Questions
Why does Excel say it cannot open a source file during consolidation?
Excel may be unable to find the file, access its folder, connect to its network location, read its format, or open it because the workbook is locked or damaged. Open the source directly and add its range again through Data > Consolidate.
Can I consolidate data from a network drive in Excel?
Yes, provided the network drive is connected, the path is available, and you have read permission for the workbook. If the error continues, copy the source workbook to a local folder and repeat Data > Consolidate.
Do all consolidation source files need to use the same format?
The source workbooks should be valid spreadsheet files that Excel can open. Confirm that each extension matches the actual format, then open and resave questionable files before adding them as sources.
What should I do if the source workbook opens but consolidation still fails?
Remove the old source reference in Data > Consolidate, add the source range again, and save a clean local copy of the source workbook. Then update Excel through File > Account > Update Options > Update Now.
How can I tell whether another user has locked the source workbook?
Try opening the workbook from File Explorer. If Excel reports that it is locked for editing or opens it as read-only, ask the other user to close it and then retry the consolidation.
