How to Import Excel Data from SharePoint to a Local Workbook
Question details
The user needs to connect to a shared Excel workbook on SharePoint and import selected data into a local network workbook using Power Query.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pulling, filtering, and transforming specific data from a cloud-hosted SharePoint spreadsheet into a local file, ensuring the local file can refresh when the source updates.
- Observed behavior
- Data must be accurately queried from SharePoint, cleaned or filtered to remove unnecessary rows and columns, loaded locally, and configured to sync manually or automatically on open.
Ensure you have the exact SharePoint URL of the source Excel file and the necessary organizational account permissions to access the data. Verify that the source table structure remains consistent to prevent Power Query refresh errors.
Use Power Query to Connect, Transform, and Load Data
Connect directly to the SharePoint file using Power Query, filter the required data, and set up automatic refreshes.
Power Query provides a robust way to link external data sources like SharePoint, filter out unnecessary information before loading it, and automatically update your local file when the cloud source changes.
Open the source Excel file in your SharePoint document library, go to 'File' > 'Info', and click 'Copy Path'. Remove the '?web=1' suffix from the copied URL so it ends in .xlsx.
Open your local Excel workbook. Navigate to the 'Data' tab, click 'Get Data' > 'From File' > 'From Web', and paste the copied URL. Alternatively, use 'From SharePoint Folder' to browse multiple files.
Once authenticated, select the specific table or worksheet you need. Click 'Transform Data' to open the Power Query Editor, where you can filter rows, remove unwanted columns, and format the data types.
Click 'Close & Load' in the top-left corner of the Power Query Editor to import the cleaned dataset into your local spreadsheet.
Right-click any cell in the imported data table, select 'Table' > 'External Data Properties', and check the box for 'Refresh data when opening the file' to keep it synchronized with SharePoint.

Need a Lightweight Office Suite for Your Spreadsheets?
While connecting directly to SharePoint via Power Query is a Microsoft-specific feature, WPS Office provides a powerful, free alternative for everyday data analysis. It offers excellent compatibility with Microsoft Excel formats and provides intuitive tools for data filtering, sorting, and pivot tables.
- 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open your files: Double-click your existing Excel files to open them seamlessly in WPS Spreadsheet.
- 3. Process your data: Utilize built-in data sorting, filtering, and pivot tables to analyze and manage your spreadsheets for free.

Frequently Asked Questions
Why does my Power Query SharePoint connection return an access error?
This usually happens due to incorrect authentication credentials. Go to 'Data' > 'Get Data' > 'Data Source Settings', find the SharePoint URL, click 'Edit Permissions', and ensure you are signed in using your 'Organizational account' rather than anonymous or Windows credentials.
Can I refresh the imported data without opening the local file?
No, in a standard local Excel workbook setup, the file must be opened for the Power Query background refresh to execute. If you need automated background refreshes without opening the file, you would need to use Power Automate or host the destination file in the cloud (e.g., SharePoint/OneDrive) and use Excel Online.
What happens if the SharePoint source file is moved or renamed?
The Power Query connection will break and return an error upon refreshing. You will need to open the Power Query Editor, go to the 'Source' step in the Applied Steps pane, and update the URL to match the new file path.
How do I only import specific columns from the SharePoint file?
During the 'Transform Data' phase in the Power Query Editor, hold the Ctrl key to click the headers of the columns you want to keep. Then, right-click one of the selected headers and choose 'Remove Other Columns' before clicking 'Close & Load'.




