Export an Excel table to a SharePoint list

Try Office 2010 In Excel 2010, you can easily share workbook data to a Microsoft SharePoint site.
Watch a video or try Office 2010!

After you create a Microsoft Office Excel table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), you may want to make the table data available to other users on a Web site. In Microsoft Office Excel 2007, you can use the Export Table to SharePoint List Wizard to export the table data to a custom list (list: A Web site component that stores and displays information that users can add to by using their browsers. Requires a Web server that is running Windows SharePoint Services.) that is stored on a Windows SharePoint Services site.

To export table data to a list on a Windows SharePoint Services site, you need to have a connection to that site as well as the permission (permission: Authorization to perform specific actions such as viewing pages, opening items, and creating subsites.) to create lists.

When users update the list on the SharePoint site, you can update the data on your worksheet with the latest changes. If you no longer want a connection between the table data on the worksheet and the SharePoint list, you can unlink the table.

What do you want to do?


Using a SharePoint list to share table data

When you export table data to a SharePoint list, you can:

  • Share table data with other users who use the SharePoint site    On the SharePoint site, other users can continue to update the list by opening the list and changing the data.
  • Create a one-way connection to the data in the SharePoint list    With a one-way connection to the data in the SharePoint list, changes that are made to the data in the SharePoint list can be incorporated in Excel. When you refresh the table data in Excel, the latest data from the SharePoint site overwrites the table data on the worksheet, including any changes that you made to the table data. In Office Excel 2007, you can no longer update a SharePoint list with changes that you make to the table data in Excel after that data has been exported.

If you don't want to keep the table data in the Excel worksheet updated with changes that are made to the SharePoint list, you can export the data without a connection to the SharePoint list.

For information about creating a one-way connection, see Export a table to a SharePoint list.

Requirements

To export table data from Office Excel 2007 to a list on a SharePoint site, you must have a datasheet control installed that is compatible with Windows SharePoint Services. A compatible datasheet control is installed with Microsoft Office Professional 2007 or Microsoft Office Access 2007. You also need to have a connection to the SharePoint site where you are creating the list and the permission to create lists on the site.

Before you export the table data, you need to create a table in Office Excel 2007 that contains the data that you want to export. A table is a series of rows and columns, also known as a range of data, that contains related data that is managed independently from the data in other rows and columns on the worksheet. When you create a table from your data, Excel can more easily manage, export, and import the data, because it is clear which rows and columns belong in the table.

Excel table

After you create a table, you can use the Export Table to SharePoint List Wizard in Office Excel 2007, which enables you to export the data to a custom list that is stored in datasheet view on the SharePoint site.

 Tip   You can also create a list directly on a SharePoint site by importing data from an Excel worksheet. On the SharePoint site, you can create a new list by using the Import Spreadsheet command and then by either selecting the table or named range or by entering the range of cells that contains the data that you want to use for the list.

Supported data types

Some Excel data types cannot be exported to a list on the SharePoint site. When unsupported data types are exported, these data types are converted to data types that are compatible with SharePoint lists. For example, formulas that you create in Excel are converted to values in a SharePoint list. After the data is converted, you can create formulas for the columns on the SharePoint site.

When you export an Excel table to a SharePoint site, each column in a SharePoint list is assigned one of the following data types:

  • Text (single line)
  • Text (multiple lines)
  • Currency
  • Date/time
  • Number
  • Hyperlink

 Note   If a column has cells with different data types, Excel applies a data type that can be used for all of the cells in the column. For example, if a column contains numbers and text, the data type in the SharePoint list will be text.

Top of Page Top of Page

Create a table

  1. On a worksheet, select the range of empty cells or data that you want to make into a table.
  2. On the Insert tab, in the Tables group, click Table.

Excel Ribbon Image

  1. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

Table headers display default names that you can change if you don't select the My table has headers check box.

 Note   After you create a table, the Table Tools and the Design tab are displayed. You can use the tools on the Design tab to customize or edit the table.

Top of Page Top of Page

Export a table to a SharePoint list

  1. In the worksheet, click anywhere in the table that you want to export.

 Tip   This displays the Table Tools, adding the Design tab.

  1. On the Design tab, in the External Table Data group, click Export, and then click Export Table to SharePoint List.
  2. In the Address box, type the Web address of the SharePoint site that you want to export the table data to.

 Tip   If you have already exported table data to this SharePoint site, the address may appear in the drop-down list, so you can select the address instead.

  1. If you want to be able to update the table data on the worksheet with any changes on the SharePoint site, select the Create a read-only connection to the new SharePoint list check box.

 Note   When you create a read-only connection, two columns are added to the table so that the folders that contain the SharePoint list can be included. An Item Type column indicates whether a row represents a SharePoint list item or a folder. A Path column displays the folder path for a list item (for example, mylist/folder). When you filter the table data, you can filter within the specific folders that are listed in these columns. You can also sort the Path column to group all rows in the same folder together.

  1. In the Name box, type the name that you want to use for the SharePoint list.

 Note   A list name is required. This name will be displayed at the top of the list page. This name is also used in the Web address for the list page and in navigational elements that help users find and open the SharePoint list.

  1. In the Description box, you can type an appropriate description for the SharePoint list.

 Note   A description is optional. If you provide a description, it will be displayed at the top of the SharePoint list in most views, just below the name of the list.

  1. Click Next.
  2. Review the list of columns and data types, and then do one of the following:
    • If the columns and data types that you are exporting are correct, click Finish.
    • If the columns and data types are not correct for the SharePoint list, click Cancel, and then verify that the column cells in Excel can be converted into a data type that can be supported in the SharePoint list.

 Tip   For example, you may need to convert complex table data on the Excel worksheet to a simpler format, such as plain text or a number. Data types that are supported are text (single or multiple lines), currency, date and time, numbers, and hyperlinks.

  1. Click OK to confirm that the table was successfully published.

 Tip   You can click the link to see your list on the SharePoint site. You can also view your list later by clicking Open in Browser in the External Table Data group on the Design tab.

Top of Page Top of Page

Update table data from a SharePoint list

 Important   Updates from a SharePoint list overwrite the original exported data as well as any changes that you made to the table data in the worksheet after you exported it to the SharePoint site.

  1. In the worksheet, click anywhere in the table that you want to update.

 Tip   This displays the Table Tools, adding the Design tab.

  1. On the Design tab, in the External Data group, click Refresh.

Top of Page Top of Page

Unlink an Excel table from a SharePoint list

  1. In the worksheet, click anywhere in the table that you want to unlink from the SharePoint list.

 Tip   This displays the Table Tools, adding the Design tab.

  1. On the Design tab, in the External Data group, click Unlink.

Top of Page Top of Page

 
 
Applies to:
Excel 2007