If you have data stored on a worksheet, and you want to share the data with others on a Microsoft Windows SharePoint Services site, you can export the data to a SharePoint list and share it with others who use the site. To export data by using this procedure, you must have have a datasheet control installed that is compatible with Windows SharePoint Services. A compatible datasheet control is installed with Microsoft Office Professional or Microsoft Office Access.In this article

Overview

When you export data from Microsoft Office Excel to a SharePoint site, people can continue to update the list on the SharePoint site as they do any other SharePoint list.

You can create a one-way connection to the data, so that when the data changes on the SharePoint site, the data can be updated in Excel. When you update your data in Excel, the latest data from the SharePoint site overwrites the data on the Excel worksheet, including any changes that you made to the data. You can also choose to export the data without a connection to the SharePoint list, so that changes to the SharePoint list cannot be updated on the Excel worksheet.

To use an Office program to manipulate data from a SharePoint list and have those changes reflected on the SharePoint site, you can use Office Access or a solution that uses Visual Basic for Applications (VBA).

You can also manipulate data from a SharePoint list in a workbook that is saved as an Excel 97-2003 workbook (.xls format). However, if you save an Excel 2003 workbook as an Office Excel workbook (.xlsx format), only a read-only connection is available from the SharePoint list.

To export data to a SharePoint list, first create a table with your data in Office Excel. 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, which enables you to provide a name and description for the list on the SharePoint site. The data that you export is stored as a custom list in datasheet view on the SharePoint site.

To export data to a list on a SharePoint site, you need to have a connection to the SharePoint site where you are creating the list and the permission to create lists on the site.

Tip You can also create a list directly from a SharePoint site by using data from Microsoft Office Excel. On the SharePoint site, you create a new list by using the Import Spreadsheet command and then either select the table or named range or enter the range of cells that you want to use for the list.

Data types

Some data types from Excel cannot be exported to a list on the SharePoint site. When the data is exported, some data types are converted to data types that are compatible with SharePoint lists. Formulas that you created in Excel are converted to values in the SharePoint lists. You can create formulas for the columns on the SharePoint site after the data is converted.

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

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

Export data to a new SharePoint list

To export data to a SharePoint list, start with a table. A table is a series of rows and columns that contains related data that is managed independently from the data in other rows and columns on the worksheet. If your data isn't already defined as a table, you can create a table before you export the data.

  1. Do one of the following:
    • If the data is already in a table, click anywhere in the table.
    • If the data is not in a table, use the following procedure to create a table.

      Create a table in Excel

      1. 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.
      3. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

        In most cases, the table headers become the column names for the SharePoint list.

      4. Click OK.
  2. On the Design tab, in the External Table Data group, click Export, and then click Export Table to SharePoint List.

    The Export Table to SharePoint List Wizard appears.

  3. In the Address box, enter the Web address of the SharePoint site you want to export the list to.

    If you have visited the SharePoint site before, the address may appear in the drop-down list.

  4. To create a connection to the SharePoint list so that you can update the data on your worksheet with any changes on the SharePoint site, select the Create a read-only connection to the new SharePoint list check box.

    If you want to export the data to a list without creating a connection to it in your Excel table, do not select the check box.

  5. In the Name box, type a name for your list. The list name is required.

    The name appears at the top of the list page, becomes part of the Web address for the list page, and appears in navigational elements that help users to find and open the list.

  6. In the Description box, type a description for your list. The description is optional.

    The description appears at the top of the list in most views, underneath the name of the list.

  7. Click Next.
  8. On the next page, the Export Table to SharePoint List Wizard shows how the data types will be exported from Excel to the SharePoint list. If the data types are correct for the list, click Finish.

    If the data types are not correct for the list, click Cancel, and then confirm that the key cell can be converted into a data type that can be supported in the SharePoint list. For example, you may need to convert the data on the Excel worksheet into a simpler format, such as plain text or a number.

  9. When a message appears to confirm that your list was exported, click OK.

    The message displays a link that you can click to see your list on the SharePoint site, or you can view your list later by clicking the Open in Browser button on the External Table Data tab.

Tip Find links to more information about working with external data in the See Also section.

Update data from a SharePoint list

As people continue to update the list on the SharePoint site, you can update the data on your worksheet with the latest changes. Doing this overwrites the data that was originally exported to the SharePoint list and any changes that you made to the data in Excel.

  1. Click anywhere in the table that you want to update.
  2. In the Design group, on the External Table Data tab, click Refresh.

Note If you no longer want a connection between your worksheet and the SharePoint site, you can unlink the table. On the External Table Data tab, click Unlink.

Tip Find links to more information about updating external data in the See Also section.

See also: