'%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)
Fix SharePoint List Authentication Errors in Excel Power Query
Question details
The user needs to resolve an authentication error in Excel Power Query that prevents connecting to a SharePoint list.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Connecting to a SharePoint list using Excel Power Query to import data.
- Observed behavior
- An error displays stating 'We were unable to authenticate you with the credentials provided', even when using valid Organizational Account credentials.
Ensure you are using the correct SharePoint site URL (not the specific list URL) and verify that your organizational account has appropriate access permissions for the target list.
Use the Dedicated SharePoint List Connector
Avoid using the generic 'From Web' option and instead use the specific SharePoint connectors designed to handle organizational authentication properly.
When connecting to SharePoint data, using the standard 'From Web' connector can cause authentication token mismatches. Using the built-in SharePoint List connector ensures that Microsoft 365 Organizational Account credentials are processed correctly.
In Excel, navigate to the Data tab on the top ribbon and click on Get Data.
Go to From Other Sources and select From SharePoint List. Alternatively, go to From Online Services and select From SharePoint Online List.
Paste the root URL of your SharePoint site (do not include the path to the specific list or view) and click OK.
On the authentication screen, select Organizational Account from the left menu and click Sign in. Enter your Microsoft credentials, then click Connect.
Contact Your Administrator for Service Issues
If the error occurs consistently across different devices and tenants, it may be a backend service issue requiring official Microsoft support.
Discover a Simpler Way to Manage Spreadsheets with WPS Office
If you frequently encounter complex backend errors or performance issues with Excel data connections, consider switching to WPS Office. It provides a lightweight, robust, and highly compatible alternative for analyzing your spreadsheet data without the hassle.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install and Launch: Run the installer and open WPS Spreadsheets after installation is complete.
- 3. Open Your Data: Click Open and select your existing .xlsx or .csv files to continue your data analysis immediately.
Frequently Asked Questions
Why does Excel reject my SharePoint credentials when using From Web?
This commonly happens because the 'From Web' connector is designed for standard web page data scraping and often fails to route Microsoft 365 organizational authentication tokens correctly. Always use the dedicated SharePoint List connector instead.
How do I clear my cached Power Query credentials in Excel?
Go to the Data tab, select Get Data, and click on Data Source Settings. Locate your SharePoint site URL in the list, click Clear Permissions, and attempt to connect again to force a fresh login prompt.
What should I do if my Excel lacks the SharePoint List option?
The SharePoint List connector is native to Microsoft 365 subscriptions and newer perpetual versions (like Excel 2021). If it's missing, you may need to update your Excel version, or your IT administrator might have restricted certain data connectors.
