Advertisements

Export to Excel from SharePoint

Applies to: SharePoint Online, SharePoint Server 2019, SharePoint Server 2016.

Export the SharePoint list items shown in the current view to Excel to work with the data in a spreadsheet.

1. Choose Export to Excel Lists Export To Excel Icon from the command bar of the SharePoint list.

Important: Export to Excel is available only when list items are unselected. The classic SharePoint experience displays a ribbon above the list, not a command bar. Click the List tab on the ribbon, and then press Export to Excel.

SharePoint Export to Excel button on ribbon highlighted

Depending on your browser, you can save and open, or open the file directly.

Note: If you save the file, the standard file name for the first copy is query.iqy, and is downloaded to the pre-existing default download folder for your browser. Use your browser’s Save as to edit the name and location.

  1. If needed, press OK > Open after the file download begins.
  1. If asked and you trust the SharePoint site, pick Enable in Excel’s security page.
  1. In Excel’s Import Data dialogue box, select the How you want to view this data and Where do you want to put the data options.
Import data dialog box from Excel 2016
  1. Press OK when you’re finished. The list should appear in Excel.
Excel spreadsheet with imported list and Refresh All button highlighted.

Excel creates an Excel table with a one-way data connection according to a web query file. To transfer a new copy of the SharePoint list to Excel, click Refresh All on the Data tab. Changes applied to the Excel table will not be transposed to the SharePoint list.

If your SharePoint list contains folders, the folder structure is absent from the resulting Excel table. However, the Item Type and Path columns are created in the Excel table, allowing you to filter and sort the data, depending on its type and location or subfolder of the list.

Additional resources

To export data from Excel to SharePoint, see this topic:

Advertisements

Leave a Reply

%d bloggers like this: