Exporting Excel Tables to SharePoint

If you have access to a SharePoint site, you can distribute Excel tables by publishing them on the site. When you export an Excel table to the SharePoint site, you can share table data with other users who use the SharePoint site, letting site users update the SharePoint list by opening the list and editing the data. This is a useful strategy for tables that are updated frequently by a number of users, particularly if other users need read-only access to the table data.

You can create a connection from the SharePoint list to the Excel table, or separate the two, depending on whether you want to propagate changes made to the list to the Excel table. If you create a one-way connection to the data in the SharePoint list, changes that are made to the SharePoint list are also made to the Excel table. When you refresh the table data in Excel, the latest data from the SharePoint site overwrites the table data on the worksheet. (Be careful here: This includes any changes you made to the table data.)

If you don't want to keep the Excel table updated with changes from the SharePoint list, you can export the data without any connection to the SharePoint list.

Note 

Unlike in Excel 2003, in Excel 2007 you can no longer create a one-way connection from the Excel workbook to the SharePoint list.

To publish an Excel list in SharePoint, you must first convert the list to a table. Select any cell in the list, and press Ctrl+T or click Table in the Tables group on the Insert tab on the Ribbon. In the Create Table dialog box, verify that the correct range is defined for the table. If your table has headers, select the My Table Has Headers check box, and then click OK. In the External Table Data group on the Design tab under Table Tools, click the arrow below the Export button, and click Export Table To SharePoint List to open the Export Table To SharePoint List dialog box shown in Figure 25-7.

Figure 25-7: Type the URL for your SharePoint site to publish your Excel table as a SharePoint list.

Type your SharePoint site's URL, a name for the SharePoint list, and a brief description. If you want to be able to update the Excel table with changes made on the SharePoint site, select the Create A Read-Only Connection To The New SharePoint List check box. Click Next. The second step of the export, shown in Figure 25-8, shows the column names and data types of the columns that Excel will export to the list. Review the list to make sure the data types are correct.

Figure 25-8: If the data type for a column is incorrect, note the Key Cell address, and correct the contents of the cell.

If a column contains any text values, Excel exports the entire column as text. If a column contains numbers or dates but includes one or more text entries (for example, N/A instead of a number or date), this is your opportunity to clean the data in your table before exporting it. The Key Cell listed is the first cell in a column that contains a text value. For example, in Figure 25-8, the Aug and Updated columns are marked for Excel to export as text because cells 119 and O7 contain text entries. If you need to correct the Excel table, click Cancel to stop the export, fix your data, and then start the export again. When the data types are correct, click Finish to export the Excel table as a SharePoint list. Figure 25-9 shows the example SharePoint list.

Figure 25-9: User changes to this SharePoint list will be reflected in the Excel table.