Permission & Access Issues

Fix Excel XLOOKUP Not Refreshing for Other Users in SharePoint

WPS Content ManagerWPS Content Manager Oct 7, 2026 870 views

Question details

Users need to enable XLOOKUP formulas to refresh in shared Excel reports without exposing a confidential master data file to colleagues.

How to Fix Excel XLOOKUP Not Refreshing for Other Users in SharePoint
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.
Before you start

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.

Solution 1Recommended

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.

1
Create an Intermediary File

In your SharePoint document library, create a new Excel workbook (e.g., 'Safe_Data_Source.xlsx').

2
Link Authorized Data

Link or copy only the non-confidential columns from your master file into this new intermediary workbook.

3
Grant Read Permissions

Right-click the intermediary file in SharePoint, select 'Manage access', and grant your colleagues 'Can view' (Read-only) permissions.

4
Update XLOOKUP Formulas

Open the final shared report and update your XLOOKUP formulas to reference the new intermediary workbook instead of the confidential master file.

Create a Controlled Intermediary Output Workbook
Data Security Maintained: Colleagues can now refresh their XLOOKUP results successfully while your master file remains completely restricted and secure.
Free Microsoft Office alternative

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. 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
  2. 2. Open Your Spreadsheets: Double-click your existing .xlsx files to open them directly in WPS Spreadsheets without losing any formatting.
  3. 3. Use Advanced Formulas: Continue using functions like XLOOKUP exactly as you always have, with zero learning curve.
Fully compatible with Microsoft Excel formats (.xlsx, .xls)Supports advanced lookup formulas including XLOOKUP and VLOOKUP nativelyLightweight installation with fast document load timesFree to use with a familiar, highly intuitive user interface
QA img-9

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.