Prevent #REF! Errors in Excel External Workbook Formulas
Question details
The user needs to keep Excel formulas intact when an external source workbook or server is unavailable.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook uses formulas that refer to data in another workbook stored on a server.
- Observed behavior
- External formulas can display #REF! when the source connection is unavailable or the workbook is saved while links cannot be resolved.
Connect to the server and confirm that you can open the source workbook. Check that the source data is arranged in a table or a consistently structured range before importing it.
Import the source workbook into a local Excel table
Use Power Query to load source data into the current workbook, then point formulas to the local table instead of external workbook references.
A failed refresh generally leaves the last successfully loaded data in place and reports a connection error. This approach reduces reliance on live external formulas, but the local data may be outdated until the connection is restored.
An unavailable source does not always cause #REF!; Excel may display cached values or a link warning instead. Local formulas can still break if referenced sheets or table columns are deleted or renamed.
In the destination workbook, select Data > Get Data > From File > From Workbook. Browse to the source workbook and select Import.
In the Navigator, select the source table or sheet and choose Load to place the data in a worksheet table. Use Transform Data first if you need to select or clean columns.
Select Data > Queries & Connections, right-click the query, and open Properties. On the Usage tab, enable Refresh data when opening the file and select OK.
Edit formulas that refer to the external workbook so they use the imported table instead. For example, if the table is named SourceData and has an Amount column, use =SUM(SourceData[Amount]).
Save the workbook and reopen it to verify the refresh and formulas. If the source is unavailable, check Data > Queries & Connections for refresh errors and confirm whether the last loaded values remain visible.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move existing files between office suites. It does not repair Excel's Power Query connections.

Frequently Asked Questions
Will a failed refresh delete my Excel formulas?
Formulas that reference a local imported table generally remain intact when a refresh fails. Check Data > Queries & Connections for the error and confirm that the last loaded data is still available.
How can I refresh the imported data manually?
Reconnect to the source workbook or server, then select Data > Refresh All. Check Queries & Connections if the refresh reports an error.
Does a broken server connection always cause #REF!?
No. Excel may show cached values or a link warning when a source is unavailable. #REF! can also occur when a referenced sheet, cell, or column no longer exists.
Can I make the imported table refresh automatically?
In Data > Queries & Connections, right-click the query and open Properties. On the Usage tab, enable Refresh data when opening the file; an interval-based refresh option may also be available.




