Exporting SharePoint Lists to Excel

Locked lesson.

Upgrade

About this lesson

Export the Training Contacts list from SharePoint to an Excel spreadsheet and update the records. 

Quick reference

Exporting SharePoint Lists to Excel

Export list data from SharePoint to an Excel table. 

When to use

Export list data from SharePoint to Excel or a .csv file whenever you want to harness the power of other Microsoft applications. 

Instructions

For this example, we are going to export the information in the Training Contacts list. We are going to export this list to Excel so we can perform calculations and analyze the data. 

  • From the horizontal menu, click Export. 
  • Select Excel Workbook. 

A download window will open in the corner of the screen and an internet query file will be created. This file creates a connection between the SharePoint list and Excel. 

  • From the download window, click Open file.
  • Click Enable. 

The list is now imported into Excel as a table. All column headings should match the SharePoint list headings. An additional column will show in Excel that displays the SharePoint pathname. 

Updating List Data

The internet query file creates a one-way connection. If a record is added or something changes in the list data in SharePoint, it will update in Excel once the data has been refreshed. However, it doesn't work the other way round. If a record is added to the Excel spreadsheet, it will not update in the SharePoint list. 

  • Go back to the list in SharePoint. 
  • Click New.
  • Add a new record. 
  • Click Save.
  • Go back to the Excel spreadsheet. 
  • Click in the table data. 
  • Click the Table Design tab, from the External Table Data group, click Refresh.

Hints & tips

  • Before clicking Enable, check that you trust the source of the file. 
Login to download
  • 00:04 In a previous lesson,
  • 00:05 we saw how we can import data from an application like Excel into SharePoint.
  • 00:11 But what if we want to do that the other way around,
  • 00:14 maybe we want to take a list that we have within SharePoint and
  • 00:18 export that out to an application like Excel or or Access.
  • 00:22 Well, that is exactly what we're going to explore in this lesson.
  • 00:26 Now for this example, I'm going to use my training contacts list.
  • 00:31 And it might be that I want to export this out into an Excel spreadsheet so
  • 00:35 that I can harness the full power of Excel's formulas and functionality.
  • 00:40 And fortunately, there is so
  • 00:42 much integration between all of the MS Office applications.
  • 00:46 So how would we go about exporting this list?
  • 00:49 Well, it's very straightforward.
  • 00:51 If we look at the horizontal menu bar right across the top here,
  • 00:54 right in the middle and we have a big old export button.
  • 00:56 And if I click that drop down I have two choices,
  • 01:00 I can export to an Excel workbook or to a CSV file.
  • 01:04 Now if I wanted to maybe use this data within an application like Access, I could
  • 01:09 export this data using either one of these formats and then import it into Access.
  • 01:14 But I'm going to export to Excel, so I'm going to select Excel workbook.
  • 01:19 And what you'll see is a little downloads window open up at the top.
  • 01:23 And this file has a, .iqy extension, which basically stands for internet query file.
  • 01:29 And what this file does is it creates a connection between SharePoint and Excel.
  • 01:35 And that's a really important point that I'm going to come back to in a moment.
  • 01:40 But for the time being, let's just open this file.
  • 01:43 It's going to launch Excel.
  • 01:44 And immediately Excel has recognized that this file that I'm trying to open has
  • 01:49 a connection to an external system.
  • 01:52 And so it's recognizing this as a potential security threat.
  • 01:56 Now, I know that this is just a list in SharePoint that I'm trying to export, so
  • 02:01 I'm going to say enable.
  • 02:03 And there we go, it has imported that data and put it into an Excel table by default.
  • 02:10 Sometimes when I do this, they'll ask me if I want to import
  • 02:13 this as an Excel table If I want to import it as a pivot table or a pivot chart.
  • 02:17 Now, I haven't got that this time around but just be aware that that might pop up
  • 02:22 and then you can select whichever option is suitable for you.
  • 02:26 Mine has by default gone to an Excel table.
  • 02:29 How do I know I'm in an Excel table?
  • 02:31 When I click in my data if I take a look at my Excel ribbons at the top,
  • 02:35 I can see I have one called table design.
  • 02:39 When I click outside of the table, that ribbon disappears.
  • 02:43 And you can see here that I have all of the records and all of those column
  • 02:47 headings, including an additional column just here that shows me the path so
  • 02:53 I can see exactly where this data is coming from in SharePoint.
  • 02:57 Now, I want to go back to this point about having a connection between SharePoint and
  • 03:02 Excel.
  • 03:03 Because there's something really important to mention here.
  • 03:06 The connection between the two applications is a one way connection.
  • 03:11 So what I mean by that is if I change something in this list in SharePoint,
  • 03:16 so maybe I add a new record.
  • 03:18 It will automatically update in the Excel spreadsheet because we have a connection,
  • 03:24 because the two sets of data are connected.
  • 03:27 However, if I add another record into this Excel spreadsheet,
  • 03:32 it's not going to update in the list in SharePoint.
  • 03:37 So it's a one way connection.
  • 03:39 Let's take a look at this in action.
  • 03:41 I'm going to minimize the Excel spreadsheet down and
  • 03:44 I'm going to add a new record into my training contacts list.
  • 03:48 So let's jump up to new, and let's just add in someone very quickly,
  • 03:53 let's just say Ben Smith.
  • 03:56 Full name Ben Smith and we'll just add an email address for
  • 04:00 him bsmith@smith.com, something like that.
  • 04:04 You could obviously go through and add more records.
  • 04:07 I'm going to click on save to add that into my list.
  • 04:10 I can see it sitting just there.
  • 04:12 How do I get that to update in the Excel spreadsheet?
  • 04:15 Well, let's jump back to Excel.
  • 04:18 You can see that it doesn't automatically update,
  • 04:21 we do have to click the refresh button.
  • 04:24 So if I click within my data within my table and jump up to table design,
  • 04:29 you can see in the middle here we have a group called external table data.
  • 04:34 And I have a refresh button.
  • 04:37 Now, watch very carefully.
  • 04:38 What happens when I select refresh or you should see that new record appear.
  • 04:44 Which it does it's there in row six.
  • 04:47 So when you export data from SharePoint to Excel in this way,
  • 04:51 it creates a one-way connection.
  • 04:53 You can add new records into the SharePoint list and
  • 04:56 then update them within Excel.

Lesson notes are only available for subscribers.

Gift this course