'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Refresh Excel Data from Workbooks in Teams Channels
Question details
The user needs to refresh data in multiple business documents from a central Excel workbook stored in Teams, SharePoint, or OneDrive without encountering accessibility issues for other users.
- Product
- Microsoft Excel, Teams, SharePoint
- Device & OS
- not provided
- Scenario
- Sharing connected Excel workbooks across a team where multiple documents pull data from a single central source file.
- Observed behavior
- Standard workbook links created via OneDrive, SharePoint, or Teams become inaccessible to other users, preventing dependent documents from successfully refreshing the source data.
Ensure that all users who need to refresh the data have the appropriate read permissions for the central source file and its folder location in SharePoint, OneDrive, or Teams.
Use Power Query to Connect via SharePoint or OneDrive
Connect to the central workbook using Power Query's web connector to ensure robust, shareable data links that do not rely on local file paths.
When you link workbooks directly using a local synced folder, the file path includes your specific user profile directory. When shared, this absolute path breaks for other team members. Using Power Query to connect directly to the SharePoint or Teams web URL resolves this issue by standardizing the source path for everyone.
Open the central source workbook in the Teams or SharePoint desktop app, navigate to File > Info, and click 'Copy path' to capture the web URL.
Open your destination Excel document, navigate to the Data tab on the ribbon, and select 'Get Data' > 'From File' > 'From Web'.
Paste the copied URL into the dialog box. It is crucial to remove the '?web=1' string at the very end of the URL if it is present, then click OK.
If prompted, authenticate using the 'Organizational account' option and sign in. Select the specific sheet or table you need from the Navigator window, and click 'Load'.
To confirm the fix, have a team member who does not own the file open the destination document and click 'Refresh All' on the Data tab.
Looking for a Lightweight Alternative for Your Spreadsheets?
If managing complex Power Query connections across Microsoft Teams and SharePoint becomes too cumbersome, consider WPS Office. It provides a lightweight, highly compatible spreadsheet solution with built-in cloud tools, allowing you to manage and share data effortlessly without advanced networking configurations.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files without losing formatting.
- 3. Share via WPS Cloud: Use the built-in share feature to collaborate with team members via link, avoiding complex local file path issues.
Frequently Asked Questions
Why do standard Excel links break when shared via Teams or OneDrive?
When you link workbooks using standard formulas while synced to OneDrive or SharePoint, Excel often creates an absolute path pointing to your local synced folder (e.g., C:\Users\YourUsername\...). Since other users do not have this exact path on their computers, the data connection breaks when they attempt to refresh it.
Do team members need a special license to refresh Power Query data?
No, users do not need a separate or premium Power Query license. As long as they are using a standard modern version of Excel and have appropriate read permissions to the SharePoint or Teams source file, they can successfully refresh the connections.
How do I fix the 'Credentials' error when refreshing data from SharePoint?
To fix authentication issues, go to the Data tab, select 'Get Data', and click 'Data Source Settings'. Locate the SharePoint URL in the list, click 'Edit Permissions', and ensure you are signed in using the 'Organizational account' tab rather than 'Windows' or 'Anonymous'.
