[Guide] How to Export From SharePoint to Microsoft Excel

Last Updated on April 17, 2022

Do you need to export your list from your SharePoint Online site to Microsoft Excel?

Fortunately, with the recent updates from SharePoint Online, exporting a list is easy. In fact, you may have already seen its button in the command bar.

In this article, I’ll show you how to easily export from SharePoint Online to Microsoft Excel, how to refresh the data, and even break the connection.

Let’s get started.

How exporting from SharePoint to Microsoft Excel works

Basically, you can export any data inside a list in SharePoint to Microsoft Excel. This also includes:

• Any list created from a template
• Any app you created that’s basically a list
• Metadata in document libraries (not including the actual files)

Document libraries are basically lists with the added functionality of being able to handle documents quite easily.

Now, when exporting a list, you’re given two choices:

1. Excel workbook: Has a one-way connection to the original list and the data can be refreshed
2. CSV file: Can be opened with Notepad and Excel but has no connection to the original list

Note: You can also do the opposite — import from Excel to SharePoint. Check out this article on how to import an Excel table to SharePoint Online.

How to export from SharePoint Online to Microsoft Excel

It’s really easy to export a list from SharePoint to Excel. Simply navigate to the list that you want to export and focus on the command bar.

You can then see an “Export” button. When you click on it, you will have a choice on how you want to export the list.

Note: Make sure to configure the list in the view that you want to export. The same columns that you see now will be the ones that will be present in the exported file.

Whichever you choose, you will then be able to either save and open the file or open the file directly (though the options depend on your browser).

Now, if you choose “Export to Excel”, the file will be named “query.iqy”. But don’t worry, the file type stands for “Internet Query” created by Microsoft.

Open it as usual. You might be greeted with a security notice from Excel. Click the “Enable” button if you trust the source.

That’s it! You can now see the contents of the list right on the Excel file you exported.

If you don’t see some of the columns, then you need to go back to the list and make sure all the columns are visible in your view.

Note: If you want to see all the columns, make sure the list view is not grouped before exporting the list as grouped columns don’t get exported.

How to refresh the data in an exported Microsoft Excel from SharePoint

The good thing with exporting a list to Excel (not as CSV) is that whenever you make any changes in the list, you can reflect those changes in the exported file.

To refresh the data on the exported Microsoft Excel file, go to the “Table Design” tab and click on the “Refresh” button.

Note: You can also achieve the same results by clicking on the “Refresh All” button found on the “Data” tab in Excel.

The connection is only one way:

• You can refresh the data on the exported file to reflect the changes from the original list.
• If you change something on the exported file, those changes won’t reflect on the original list.

This feature is handy especially when you have Excel charts and graphics based on the data and you need to update those data on SharePoint.

How to break the connection of an exported Microsoft Excel to SharePoint

Even if you change something on the original list, those changes won’t reflect automatically on the exported file.

But if you really want to sever the connection permanently between the Microsoft Excel exported file and its source SharePoint list, then do the following:

• Navigate to the “Table Design” tab
• Click on the “Unlink” button

Note: You can also achieve the same results by going to the “Data” tab and clicking on the “Queries and Connections” button. Right-click on the connection on the right panel and select the “Delete” option.

A warning will appear to confirm to you that this action will permanently remove the query definition from the sheet.

Click on the “OK” button to continue and the connection between the Excel file and its original SharePoint list will be broken.

Exporting from SharePoint to Excel

The ability to export from SharePoint to Excel is really helpful especially if you need to make a chart or report based on Excel.

In addition, since you can refresh the data on the Excel file when you make changes on the list, you don’t need to worry about wasting time updating the data on the Excel file.

Anyway, do you have questions regarding how to export from SharePoint to Excel? If so, kindly leave a comment below.

For inquiries and concerns, please use the site’s contact form to reach out and I’ll get back to you as soon as possible.