[Guide] How to Export From SharePoint to Microsoft Excel

[Guide] How to Export From SharePoint to Microsoft Excel

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.

Sign up for exclusive updates, tips, and strategies

    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.

    Click on the export button and choose how you want to export the list

    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.

    Click the enable button if you trust the SharePoint site

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

    Exported SharePoint list on Excel

    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.

    Click on the refresh button found on the table design tab

    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
    Click on the unlink button found on the table design tab

    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.

    About Ryan Clark

    As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage Modern Workplace and Digital Process Automation investments. I am also a Microsoft Most Valued Professional (MVP) for Office Apps & Services.

    Subscribe
    Notify of
    guest
    0 Comments
    Inline Feedbacks
    View all comments
    0
    Would love your thoughts, please comment.x
    ()
    x
    Scroll to Top
    Tweet
    Share
    Share
    Pin