Fix Excel XLOOKUP Not Refreshing for Other Users in SharePoint
Question details
Users need to enable XLOOKUP formulas to refresh in shared Excel reports without exposing a confidential master data file to colleagues.

- Product
- Microsoft Excel / SharePoint
- Device & OS
- not provided
- Scenario
- Sharing Excel reports hosted on SharePoint that contain XLOOKUP formulas linked to a restricted, master source workbook.
- Observed behavior
- The file owner sees current, refreshed values, but colleagues see outdated data or errors because they lack the required permissions to access the referenced source workbook.
Verify that you have 'Owner' or 'Full Control' access to the SharePoint document libraries hosting both the master source workbook and the shared destination reports.
Create a Controlled Intermediary Output Workbook
Use this method to safely share specific data subsets so formulas can refresh without exposing confidential employee information.
Since SharePoint formulas like XLOOKUP require the user to have read access to the source file, directly linking to a highly confidential master file will break for restricted users. Instead, you can create a safe 'middle' file.
In your SharePoint document library, create a new Excel workbook (e.g., 'Safe_Data_Source.xlsx').
Link or copy only the non-confidential columns from your master file into this new intermediary workbook.
Right-click the intermediary file in SharePoint, select 'Manage access', and grant your colleagues 'Can view' (Read-only) permissions.
Open the final shared report and update your XLOOKUP formulas to reference the new intermediary workbook instead of the confidential master file.

Grant Read-Only Access to the Master Source File
If the master workbook does not contain sensitive or confidential data, granting direct read access is the fastest way to resolve the refresh issue.
Try WPS Office for Seamless Spreadsheet Management
If you frequently struggle with complex SharePoint permissions and Microsoft Excel linkage issues, consider using WPS Office for your standalone data processing needs. It offers a lightweight, high-performance spreadsheet tool that is fully compatible with advanced formulas.
- 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Spreadsheets: Double-click your existing .xlsx files to open them directly in WPS Spreadsheets without losing any formatting.
- 3. Use Advanced Formulas: Continue using functions like XLOOKUP exactly as you always have, with zero learning curve.

Frequently Asked Questions
Why does XLOOKUP work for me but show errors for my colleagues?
Formulas that link to external workbooks run using the permissions of the person currently viewing the file. Because you are the file owner, you have access to the master source file, allowing the data to refresh. Your colleagues lack permissions to the source file, causing the external links to fail and display errors.
Can I hide confidential columns instead of making a new workbook?
Hiding columns in Excel does not provide real security. If a user has permission to open the file to allow their XLOOKUP to refresh, they can easily unhide the columns and view the confidential information. An intermediary workbook is the safest method.
Does VLOOKUP have the same permission restrictions as XLOOKUP in SharePoint?
Yes. Any formula that pulls data from an external workbook (including VLOOKUP, INDEX/MATCH, and XLOOKUP) requires the active user to have at least read-only access to the source file and its host document library.




