How to import from a SharePoint site in Excel

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

How to import files from a SharePoint site in Excel
The Power Query tool in Excel makes it easy to import data from other files into a workbook. You can even import data from files stored in SharePoint, although you have to work a little harder to do it. This blog provides a step-by-step guide for importing data from a file stored in a SharePoint site.

Posted by Andrew Gould on 03 March 2026

In this blog

Importing data from files stored in SharePoint
Connecting to a SharePoint site
Editing the query
Tidying up the data
Loading the data into Excel

Importing data from files stored in SharePoint

Power Query makes it easy to import data from other files into an Excel workbook. This is especially easy to do when your files are stored locally. For example, to import another Excel file you can choose Data | Get Data | From File | From Excel Workbook from the ribbon and then select the file you want to import.

Importing data from a local Excel file in Power Query for Excel

Simply select the file you want to import and click Import.

But what if your file is stored in a SharePoint site? In this blog I'd like to import data from an Excel workbook shown in the image below:

An Excel file stored in a folder in a SharePoint site

This Excel file is stored in a folder in a SharePoint site.

Connecting to a SharePoint site

To begin importing data from a file stored in a SharePoint site, from the Excel ribbon choose Data | Get Data | From File | From SharePoint Folder

Choosing to import data from a SharePoint folder in Excel

Even though we're importing from an Excel file, choose From SharePoint Folder.

In the dialog box which appears, enter the root URL of the site which contains the file you want to import.

Specifying the URL of a SharePoint site in Power Query for Excel

Don't include the path to the specific folder which contains the file, just the root URL of the SharePoint site.

You may be asked to sign in to provide credentials to access the site. Click the Sign in button to do this.

Entering credentials when connecting to a SharePoint site in Power Query for Excel

Click Sign in and enter your credentials.

Power Query remembers your credentials so you don't have to sign in each time you connect to the SharePoint site.

After signing in, click Connect to open the connection to the SharePoint site.

Connecting to a SharePoint site after entering credentials in Power Query for Excel

After you've signed in, click Connect.

The dialog box which appears shows a preview of the files stored in the SharePoint site.

A list of files from a SharePoint site displayed in Power Query for Excel

You should see a list of files stored in the site.

The next step is to edit the query in Power Query to select the file you want to import. Click the Transform Data button on the dialog box shown above to open Power Query.

Editing the query

After clicking Transform Data, the Power Query editor will open and display the list of files stored in the site.

A list of SharePoint files displayed in a query in Power Query for Excel

If you're lucky, you'll spot the file you want to import!

If you can't see the file you want to import, you may find it helpful to filter the list using the drop down arrows at the top of each column. In the example below, I've clicked the drop down arrow at the top of the Name column to filter by the name of the file.

Applying a filter to a column in a query in Power Query for Excel

You can type part of the file name in the search box, then choose the file you want to import and click OK.

Sometimes, Power Query doesn't list all the files of the SharePoint site. If you can't find the file you want to import, this blog may help you!

When you can see the file whose contents you want to import, click the Binary link in the Content column to extract its contents.

Extracting the contents of an Excel file in Power Query for Excel

Click Binary to see the contents of the file.

Next, you can choose which item from the workbook you want to import. Click the Table link in the Data column for the item you want to import.

Choosing a worksheet to import from an Excel file in Power Query

Here we're choosing to extract the data from the Sales worksheet.

You should now see the data contained in the item you selected.

Data from an Excel worksheet displayed in Power QUery

A sample of the data in the Sales worksheet.

Tidying up the data

You'll almost certainly want to tidy up the data you've extracted before loading it into the Excel file. For example, you can use the first row of the worksheet to create column headers by choosing Home | Use First Row as Headers from the Power Query ribbon.

Promoting the first row to column headers in Power Query

Alternatively, click the icon in the top left corner of the table and choose this option.

Power Query may automatically assign data types to the columns when you do this.

Transformed data in a Power Query query in Excel

The Changed Type step was added by Power Query.

Loading the data into Excel

When you've finished making changes to the query, you can choose to load it into Excel by choosing Home | Close & Load | Close & Load To... from the Power Query ribbon.