Sign up to our top tips

Sign up for the latest top tips and information of the services we offer to our clients


Appetite will not share you information with any third parties.

SharePoint | How to export to Excel

blog
30th Jun 2016

How to export a SharePoint List or Library to Excel.

“Can you export this to Excel?”, you will not believe how many times we get asked this during the course of a SharePoint development project!

 
Being a Microsoft product, SharePoint has a natural association with Excel, so much so that almost any content stored within your SharePoint site can be easily exported to Excel. So here’s a brief explanation as to how this can be achieved:
 

What can I export from SharePoint to Excel?

 
Any information contained in a list can be exported to Excel, this includes the following Out of the Box web parts:
 

1.  Contacts

2.  Calendar

3.  Tasks

4.  Issues

5.  Custom List

6.  Discussion Board

7.  Document Library

 
Now the last one in the above list you might think is a little strange but yes, you can in fact export document libraries (metadata, not the actual files) to Excel, since they too are also lists.
 

How do I export from SharePoint to Excel?

 
It’s really quite easy, here’s how:
 

1.  Firstly, make sure you are using Internet Explorer Browser, export to Excel does not work from other browsers such as Google Chrome or Safari.

 

2.  Go to the list you want to export to Excel (i.e. Calendar).

 

3.  Navigate to the view you would like to export. The way it works with export to Excel is very much, what you see is what you get! In other words, if you are exporting a particular view and you only see 5 columns, SharePoint will only export those 5 columns into Excel. Therefore, if you are looking to export an entire list (database), make sure you have all the columns visible in your view (i.e. All Items View). Also make sure your view is not grouped! (Grouped columns will not get exported to Excel).

 

4.  Click on the List tab and select Export to Excel found in the middle of the ribbon, see below:

 

SharePoint Calendar View
 

Note: The List tab terminology might change depending on the type of list you are exporting from. For example, the List tab when looking at a Calendar will display the term Calendar, as shown below:
 

5.  Now you might get a warning message from the browser at this point, don’t panic – just click OK or proceed.

 

6.  Excel will open, yet again you might get another warning, if you do – just click Enable

 

 

7.  Upon completion, Excel will open up to display your data export. It really is that easy!

 
Image 3
 

Refresh the Data in Excel without doing another Export

 
One important thing to know about export to Excel is that the Excel file you export to maintains a connection to SharePoint. This means that any changes you (or anyone else) makes to the list in SharePoint will be applied to your export file, this however is not done automatically. In order to see the changes made to the SharePoint list, in Excel, you have to refresh your Excel export file. Here’s how:
 

1.  Looking at your Excel export file click on the Data Tab, then Connections:

 
Image 4
 

2.  On the dialogue box that appears, click the Refresh button, then Close:

3.  Viola, your data in Excel has been updated!

 
This refresh functionality is extremely powerful when building pivot tables, charts, etc., especially if you want to update your Excel spreadsheet to include any recent changes made to your SharePoint list. By simply clicking the Refresh button, you can refresh your Excel spreadsheet to include the latest information without having to worry about losing any formatting applied to your Excel spreadsheet.
 

Note: The connection between SharePoint and Excel is 1-way. In other words, information flows form SharePoint to Excel, not the other way around! Therefore, any changes made in Excel will not be reflected in SharePoint. Once you click on Refresh, SharePoint will overwrite whatever you did in Excel.
 

Break the connection between Excel and SharePoint

 
Unless you specifically refresh using the instructions above, your data in Excel will remain as it was at the time of the export. If, for whatever reason, you would like to permanently remove the connection to SharePoint, please follow these instructions:
 

1.  From Excel, click on the Data Tab, then Connections:

 
Image 6
 

2.  On the dialogue box that appears, click Remove:

3.  On the warning message that appears, click OK:

 
Image 8

Let’s chat, Now that you know who we are, we’d like to get to know you.

Here's what our customers think...

"Appetite delivered first class treatment from people who care and share our dream. Appetite will never rest, they are constantly looking for ways to improve and deliver a better service to the client."
Back To Top
Appetite For Business Logo
© appetite for business 2014
registration number: SC217705
illustrations by Flipside

Accreditation

Learning & Performance Institute - Accreditated Learning Provider Gold Standard Fpal

Get In Touch

If you'd like to know how Appetite can help you, why not get in touch - we'd love to help.

Call us on: +44(0)1224 824111
or email: hello@appetiteforbusiness.com

Connect With Us