Understanding SharePoint Exports to Excel

By:   |   Comments   |   Related: > SharePoint


I find it easier to analyze data using Excel. Is there a way to export my SharePoint List or Library metadata?



The example below shows a SharePoint list presented in a web part zone added to a site page. For aesthetics, it is presented with a cleaner look using the "Summary Toolbar", rather than the "Full Toolbar". However, to access the Excel export option, a user must open the full list. This is easy to do by clicking on the web part title, in this case "Helpdesk Progress".

helpdesk progress

The full list shows the full toolbar. Click the "Actions" menu and then select "Export to Spreadsheet".

edit in datasheet

A prompt displays asking to "Open" or "Save" the export. For the purposes of this article I will save it first, but you can do either.

file download

There is a default file name but I change it to something more meaningful. This can be useful because the export file can be saved and reused by using the export file directly in Excel rather than navigating through the SharePoint portal again..

excel export

The file is saved with the "iqy" file extension.


When opening the file in Excel 2007, I get a security prompt which must be enabled to use the file. (Excel 2003 works also...there maybe a different security prompt.)

security notice


Here's what the Excel export looks like:

microsoft excel

The iqy file is not a static file. The SharePoint data is not stored in the .iqy file. This is "Web Query File" and refreshes the SharePoint data every time you run it. It's a great feature, but don't forget, if you are not connected to the network (and if you don't have permissions) you won't get the data.

If you need to save a snapshot (a point in time) of the SharePoint data you should "Save As" an Excel Workbook (or some other static format). You would also need to "Save As" a workbook if you were sharing information with an external user (i.e. outside the organization).

  • Save the .iqy file if you want to rerun the export with fresh data without going through the "Export to Spreadsheet" procedure (described above).
  • "Save As" an Excel Workbook, if you need a static copy of the data. The workbook will NOT refresh new data. Only the web query (.iqy) file can.

Document libraries can also be exported to Excel, however, the export will not contain the document. In it's place a link to the document is available.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms