When you want to share your Excel table with your team on an ongoing basis, working with SharePoint lists can be easier for viewing and editing the data.
For more information on tables, see Create and delete an Excel table in a worksheet.
Important: Before exporting your table, make sure you have the necessary permissions to create a list on the SharePoint site (contact your server administrator if you’re unsure about your permissions), and the site address where you want to publish the table.
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.
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)
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.
Export a table to a SharePoint list
Click inside the table you want to export and click Design > Export > Export Table to SharePoint List.
Note: You’ll see the Design tab only after you click inside the table.
Tip: Keyboard shortcut: Press and hold the Alt key + J, T, X, E (one key at a time).
In the Address box, type the address, or URL of the SharePoint site.
Important: Type everything that’s in your Web address before the “/default.aspx”. For example, if the address is, http://contoso/default.aspx type http://contoso.
If you’d like to be able to view the data updates to the SharePoint list and also update your table in Excel, check the Create a read-only connection to the new SharePoint list 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.
In the Name box, type a name that’ll help others find your list.
Note: The Next and Finish options become available only after you add a Name.
Optionally, you can add information in the Description box.
Review the information for the Columns and Data Types before you click Finish, and if you need to edit anything, click Cancel.
Click OK to publish your table.
When you see a message indicating that your table is published with a link, you can either view your list right away or view it later by clicking Design >Open in Browser from your workbook.
Update a table from a SharePoint list
You can periodically update your Excel worksheet with changes from the list.
Important: When you refresh the data in your worksheet, you’ll be overwriting any changes you might have made to the worksheet after your last export.
Click anywhere in the table that you want to update.
Click Design >Refresh.
Note: Though you can’t update any changes from your Excel worksheet to an existing SharePoint list, you can export the changed table as a new list with a different name.
Unlink a table from a SharePoint list
If you no longer want a connection between the table data on the worksheet and the SharePoint list, unlink the table by clicking anywhere in the table, and then clicking Design > Unlink.