Prevent #REF! Errors in Excel Links to SharePoint Workbooks
Question details
Understand and prevent #REF! errors in Excel formulas that link to workbooks stored on SharePoint.

- 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.
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.
Check SharePoint access and refresh workbook links
Use this first to rule out access, location, or link-enabling issues.
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.
Open the report in Excel for the web and select Data > Workbook Links. Review the source links and enable or refresh them if prompted.
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.
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.

Consolidate source data into the reporting workbook
Use this approach when the report must calculate without relying on a separate workbook being open.
Import SharePoint data with Power Query
Use an approved data connection when you need a repeatable way to bring source data into the report.
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.

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.




