Exporting a SharePoint List to an Excel 2007 Spreadsheet

You can export the contents of SharePoint lists, results of a survey, or document libraries to an Excel 2007 spreadsheet. The exported list or library is a Web query that stays updated with changes to the original list in your SharePoint site. The Excel 2007 spreadsheet maintains a connection to the SharePoint list and therefore becomes a linked object.

In this exercise, you will export a list from a SharePoint site to an Excel 2007 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.

OPEN the SharePoint site on which you have a list from which to export data to an Excel 2007 spreadsheet. This exercise uses the list you created in the first exercise of this chapter. Remember to use your SharePoint site location and your list in place of http://wideworldimporters and Furniture Price List in the exercises. If prompted, type your user name and password, and click OK.

1. In the Quick Launch, under the Lists area, click Furniture Price List.

Tip 

The export process exports only the columns and rows contained in the list’s current view, which is the All Items view in this exercise. If none of the views contain the data you wish 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.

2. Click Actions. From the menu, click Export to Spreadsheet.

SharePoint generates an Excel 2007 query.

3. When the File Download dialog box appears, click the Open button.

Excel 2007 opens a new workbook that contains one worksheet named owssvr(1). A Microsoft Office Excel Security Notice dialog box is displayed warning you that data connections have been blocked.

4. Click Enable.

The Excel 2007 query results are displayed in the owssvr(1) worksheet in an Excel 2007 table. Each column in the list contains an AutoFilter arrow in the header row, and the Design contextual tab is active. Excel 2007 names your Table_owssvr_1.

Tip 

When you export a SharePoint library, Excel 2007 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 2007 spreadsheet, click the link to open the file.

Tip 

You should make a habit of renaming your tables so that you recognize the data they contain. This process helps formulas that summarize table data much easier to understand. To rename your table, first ensure that the Design contextual tab is active, and then, in the Properties group, edit the value in the Table Name field.

5. Click cell A9, type Antique, and press . Type Bi, and press again.

IntelliSense completes the word Bianca for you.

6. Type wood, and press . Type 5, and press . Type 10, and then press . Excel 2007 places a dollar ($) sign before the number 10. However, the Total Cost column will not automatically calculate the data in that column. Although the columns in Excel 2007 retain the data types from the exported SharePoint list, they do not retain the formulas of a calculated column.

7. Click the Data tab, and click the Refresh All button.

The spreadsheet is updated with a copy of the data from the Furniture Price List on the SharePoint Web site. Your changes to data in the Excel 2007 spreadsheet are lost, which differs from the behavior of Excel 2003. In Excel 2007, changes that you make to data in your Excel 2007 worksheet do not synchronize with the list on the SharePoint Web site, that is, only a one-way synchronization occurs from the SharePoint site to Excel 2007. When using Excel 2003, two-way synchronization is still available.

8. Click cell A2 and then, in the Connections group, click Properties. The External Data Properties dialog box appears.

You can use this dialog box to alter the behavior of the refresh activity.

9. Click Cancel.

Tip 

You can also initiate the exporting and linking of a SharePoint list to Excel 2007 by using the Access Web Datasheet task pane, which contains four options: Query list with Excel, Print with Excel, Chart with Excel, and Create Excel PivotTable Report.

CLOSE the browser and Excel 2007. You do not need to save the spreadsheet.