Export a SharePoint list to a spreadsheet

Applies to
Microsoft Office Excel 2003

If you have data stored in a list in Microsoft Windows® SharePoint™ Services or Microsoft Office SharePoint Portal Server 2003, you can easily export it to a list in Microsoft Office Excel 2003. By exporting your SharePoint list to Excel, you can take advantage of the rich set of list features and formatting options in Excel. After exporting the list to Excel, you can choose whether to keep it linked to the SharePoint list.

 Note   To export a list to Excel from Windows SharePoint Services, you must have Excel 2003 installed on the computer you are using to view the SharePoint list.

Export the list

  1. In your SharePoint site, on the top link bar, click Documents and Lists.
  2. On the Documents and Lists page, click the list that you want to export to Excel.
  3. On the page that displays the list, under Actions, click Export to spreadsheet.
  4. In the File Download dialog box, click Open.
  5. In the Opening Query dialog box in Excel, click Open.
    • If no workbook is open, Excel creates a new blank workbook and inserts the list on a new worksheet.
    • If a workbook is open, in the Import Data dialog box, select one of the following options:
      • To insert the list in the active worksheet, click Existing worksheet.

In the worksheet, click the cell where you want to place the upper-left corner of the list, and then click OK.

  • To insert the data in a new worksheet, click New worksheet and click OK.
  • To insert the data in a new workbook, click New workbook and click OK.
  • To create a PivotTable report based on the list, click Create a PivotTable report, and follow the steps in the PivotTable and PivotChart Wizard.

After exporting the list to an Excel spreadsheet, you can use the link between the Excel list and the SharePoint list:

  • To synchronize the SharePoint list with changes you make to the Excel list, use the Synchronize List command in Excel (Data menu, List submenu, Synchronize List command).
  • To discard changes to the Excel list and re-import the list from the SharePoint list, use the Discard Changes and Refresh command in Excel (Data menu, List submenu, Discard Changes and Refresh command).
  • To remove the link between the Excel list and the SharePoint list, use the Unlink List command in Excel (Data menu, List submenu, Unlink List command).

 Note   The list data types supported by Windows SharePoint Services are text, currency, dates, numbers, and hyperlinks. If the Excel list that is linked to a SharePoint list uses other data types, synchronizing the lists may create inconsistencies between the lists.

Save the query for later use

You can save the query used to export a SharePoint list to Excel. By doing this, you can run the query later and update the list data in Excel as the SharePoint list changes.

  1. In your SharePoint site, on the top link bar, click Documents and Lists.
  2. On the Documents and Lists page, click the list for which you want to save a query.
  3. On the page that displays the list, under Actions, click Export to spreadsheet.
  4. In the File Download dialog box, click Save .
  5. In the Save As dialog box, locate the folder in which you want to save the query in the Save in field, enter a file name in the File name field, and click Save.
  6. To run the query, use Windows Explorer to find the file where you saved it, and then double-click it.
 
 
Applies to:
Excel 2003