How to export a SharePoint document library to Excel

In the past, I have blogged about exporting a SharePoint/Master List to Excel. This makes sense and might be required in some cases if you need to build out some reporting capabilities/formulas with the data. We also happen to have the same functionality with SharePoint document libraries. You might be wondering what the potential benefit of this is. It might come in very handy if you need to evaluate the contents and structure of your document library. This is one of those posts after which you will be saying: “Wow, I did not know I could do that”!  Let me explain.

It would be best to show how this works with two different document libraries: one with folders and one with metadata since the Excel file might be used differently in those two cases.

Document Library with Folders

In the below example, I have a typical document library with several levels of folders/subfolders and documents inside of that folder.

1. Click the Export to Excel button
2. It will download the query.iqy file to your computer
3. When you open the above file, it will Open Excel document that looks like this:

Essentially what it does, it extracts the path of each and every folder and document and lists each item on a separate row.

Use Cases

• Analyze the content of the document library
• See, at a glance, document library contents
• Analyze the folder depth of the library
• Analyze file naming convention
• Make decisions about document library restructuring

Documents Library with Metadata

When you have a document library without folders, but with metadata, you probably do not need to worry about restructuring much; however, you might need to perform some analysis, reporting using formulas and charts, which requires Excel.

In this case, Export to Excel behaves the same way it does with the lists, it essentially exports the whole table (rows and columns) – the result is the table appearing in Excel instead of SharePoint.

Use Cases

• Perform advanced analysis using Excel formulas, charts, tables
• Analyze metadata use within the document library

Possible Error Message

In some cases, you might get the following message when you try to open the exported file “The server you are trying to access is using an authentication protocol not supported by this version of Office”. In this case, log in to the SharePoint site/library via the Edge Browser and not Google Chrome and export the library from there and open the newly exported query.iqy file in Excel. Should work!

That’s all I wanted to share with you in this blog post. As you can see, Export to Excel behaves quite interesting and somewhat unexpected when you export a document library with folders to Excel. There is only one thing that remains here. Please repeat after me: “Wow, I did not know I could do that.”