Excel Error Codes

Prevent #REF! Errors in Excel Links to SharePoint Workbooks

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Understand and prevent #REF! errors in Excel formulas that link to workbooks stored on SharePoint.

Prevent #REF! Errors in Excel Links to SharePoint Workbooks
Product
Microsoft Excel for the web and SharePoint
Device & OS
not provided
Scenario
A report uses external workbook links or functions such as SUMIFS to reference data in a SharePoint workbook.
Observed behavior
Linked formulas may display #REF! when the source workbook is closed, unavailable, or its links have not been enabled.
Before you start

Identify the source workbook and the formulas showing #REF!, then check that you can open the source file from its SharePoint location. Confirm that the workbook has not been moved or renamed.

Solution 1Recommended

Check SharePoint access and refresh workbook links

Use this first to rule out access, location, or link-enabling issues.

1
Open the source workbook

In SharePoint, navigate to the source file and open it with the same account you use for the report. If access is denied, ask the file owner to grant permission.

2
Check workbook links

Open the report in Excel for the web and select Data > Workbook Links. Review the source links and enable or refresh them if prompted.

3
Repair a changed source

If the source workbook was moved or renamed, use the available link options in Data > Workbook Links to update the source, or edit the affected formulas to point to the correct workbook.

4
Test the formulas

With the source workbook open, return to the report and check the affected cells. Test again with the source closed to see whether your reporting setup depends on it being open.

Check SharePoint access and refresh workbook links
Refresh limitations: Opening or enabling the source may help diagnose the issue, but external links and functions such as SUMIFS may still not refresh reliably in Excel for the web when the source is closed.
Free Microsoft Office alternative

Work with Excel-compatible reports in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Microsoft Office formats, making it easier to move existing files between applications. It does not fix Excel for the web's SharePoint link or refresh limitations.

Edit spreadsheet reports in a familiar interfaceOpen and save Microsoft Excel XLSX filesUse worksheets and formulas to organize reporting dataAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint workbook link show #REF! when the source is closed?

Excel for the web may not reliably refresh some external links or functions such as SUMIFS when the source workbook is closed or unavailable. Check Data > Workbook Links, verify access, and test the formula with the source open.

Can moving or renaming the source workbook break a link?

Yes. A changed file location or name can leave the report pointing to the wrong source. Check Data > Workbook Links for options to update the source, or edit the affected formulas.

How can I make the report less dependent on an open source file?

Copy the required source data into a worksheet in the reporting workbook and update formulas to use local ranges. Alternatively, import the data with an approved Power Query connection and refresh it as needed.

Will Power Query refresh automatically in Excel for the web?

Not necessarily. Refresh support depends on the connection, Excel version, permissions, and organizational settings. Check your environment and test the query before relying on a scheduled refresh.