Using SharePoint 2013 with Excel and Access

Exporting a SharePoint list to an Excel spreadsheet

You can export the contents of SharePoint lists, the results of a survey, or document libraries to an Excel spreadsheet. In Excel, changes that you make to data in your Excel worksheet do not synchronize with the list on the SharePoint website; that is, only a one-way synchronization occurs from the SharePoint site to Excel. The exported list or library is connected to a web query, which when run, updates the spreadsheet with changes made to the original list on your SharePoint site. The Excel spreadsheet maintains this connection to the SharePoint list, and therefore becomes a linked object.

The export process exports only the columns and rows contained in the list’s current view. If none of the views contain the data that you want to export, then you must create a new view to meet your needs. Alternatively, you can choose one of the existing views, export the list to a spreadsheet, and then delete the unwanted data.

When you export a SharePoint library to an Excel spreadsheet, Excel represents the documents in the list with hyperlinks that point to the documents on the SharePoint site. Similarly, attachments on list items are replaced with a hyperlink. In the Excel spreadsheet, click this link to open the file.

In this exercise, you will export a list from a SharePoint site to an Excel 2013 spreadsheet. You will add data to the spreadsheet, and then synchronize the data in the spreadsheet with the contents of the list on the SharePoint site.

1. On the Quick Launch, click Furniture Price. Click the List tab, and then click Export to Excel in the Connect & Export group.

2. If you get a browser message asking whether you want to open or save owssvr.iqy, click Open.

   Excel 2013 opens a new workbook that contains one worksheet, named owssvr. A Microsoft Excel Security Notice dialog box appears, warning you that data connections have been blocked.

   

3. Click Enable to display the results of the Excel query. Each column in Excel contains an AutoFilter arrow in the header row, and the Design contextual tab is active.

   

   Click to view larger image

4. Click cell A10, type Antique, and then press Tab. Type Bi, and then press Tab so that IntelliSense completes the word Bianca for you.

5. Type wood, and then press Tab. Type 5, and then press Tab. Type 10, and then press Enter. Excel places a dollar sign ($) before the number 10.

6. On the Data tab, in the Connections group, click Refresh All.

   The spreadsheet is updated with a copy of the data from the Furniture Price list on the SharePoint website. Your changes to data in the Excel spreadsheet are lost.

7. Click cell A2, and then, in the Connections group on the Data tab, click Properties to display the External Data Properties dialog box. You can use this dialog box to alter the behavior of the refreshed activity.

   

8. Click Cancel.