Data Import & Export

How to Import Excel Data from SharePoint to a Local Workbook

Maira MehtabMaira Mehtab Nov 11, 2026 869 views

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.

How to Import Excel Data from SharePoint to a Local Workbook
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.
Before you start

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.

Solution 1Recommended

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.

1
Get the SharePoint file path

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.

2
Connect via Power Query

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.

3
Transform and filter data

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.

4
Load data to local workbook

Click 'Close & Load' in the top-left corner of the Power Query Editor to import the cleaned dataset into your local spreadsheet.

5
Configure refresh settings

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.

Use Power Query to Connect, Transform, and Load Data
Authentication Requirement: When connecting for the first time, Excel will prompt you to sign in. Ensure you select the 'Organizational account' tab on the left side of the prompt to authenticate properly against your SharePoint environment.
Free Microsoft Office alternative

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. 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
  2. 2. Open your files: Double-click your existing Excel files to open them seamlessly in WPS Spreadsheet.
  3. 3. Process your data: Utilize built-in data sorting, filtering, and pivot tables to analyze and manage your spreadsheets for free.
Seamless compatibility with Microsoft Excel (.xlsx, .xls, .csv) formats.Free, lightweight, and fast installation without subscription fees.Built-in advanced data processing tools for an efficient workflow.Clean, familiar tabbed interface to manage multiple workbooks effortlessly.
microsoft office alternative - wps office

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'.