Fix Excel Data Source Error Retrieving from SharePoint
Question details
The user needs to resolve an issue where an Excel file successfully retrieves data from another SharePoint file for the owner, but displays a data source error for colleagues.
- Product
- Microsoft Excel, SharePoint
- Device & OS
- not provided
- Scenario
- Sharing an Excel report connected to a SharePoint data source with team members.
- Observed behavior
- The connection works for the file owner but returns a data source error for colleagues, even when both files are located in the same SharePoint folder.
Ensure you are logged into your Microsoft account with the appropriate SharePoint permissions and verify that the source file has not been renamed or moved.
Remove and Re-add the Data Source via Excel for the Web
Reconnecting the data source directly in the web version updates the cloud path correctly, ensuring it is accessible to others rather than relying on a local file path.
When you link files using the desktop version of Excel, it sometimes captures the local synced path (e.g., C:\Users\Name\...) instead of the universal cloud URL. Excel for the web forces the use of the SharePoint URL, which resolves the issue for colleagues.
Log into your SharePoint or OneDrive account via a web browser and open the Excel file that needs the data connection in Excel for the web.
Navigate to the 'Data' tab, open 'Queries & Connections', locate the existing SharePoint connection, right-click it, and select 'Delete'.
Select 'Get Data', enter the exact SharePoint URL of the source file (copied directly from the browser), and complete the connection process.
Verify and Grant SharePoint File Permissions
Colleagues must have explicit read access to both the destination file and the underlying source data file in SharePoint to refresh the connection.
Experience Seamless Collaboration with WPS Office
While troubleshooting Microsoft Excel's SharePoint connections can be complex, WPS Office offers a free, lightweight, and easy-to-use alternative. With highly compatible spreadsheet tools and built-in cloud sharing features, WPS makes collaborating with colleagues effortless.
- 1. Download and Install: Get WPS Office from the official website and install it on your device in minutes.
- 2. Open Your Spreadsheets: Open your existing .xlsx files directly in WPS Spreadsheet without losing formatting.
- 3. Share via Cloud: Use WPS Cloud to share document links with colleagues, ensuring everyone has the correct permissions instantly.
Frequently Asked Questions
Why does an Excel link to a SharePoint file work for me but not my colleagues?
This commonly occurs when the data link was created using a locally synced OneDrive or SharePoint path (like C:\Users\YourName\Company\Folder). Because your colleagues have different local usernames and paths, the link breaks for them. Replacing the link with the direct SharePoint web URL fixes this.
Can I use Power Query to pull data from a SharePoint folder?
Yes. In the Excel desktop app, you can go to Data > Get Data > From File > From SharePoint Folder. You will need to enter the root URL of your SharePoint site and then filter down to the specific folder within Power Query.
How do I find the correct SharePoint URL for my Excel data source?
Open the source file in Excel for the web, click on File > Info, and select 'Copy Path'. Before pasting this path into your Get Data prompt, be sure to delete the '?web=1' string at the very end of the URL.
