About publishing lists
When you create a list in Microsoft Excel, you have the option to publish the list so that others can view and even edit the list data. You can share the information contained in a list by publishing it to a server that is running Microsoft Windows SharePoint Services. Additionally, when you publish a list to a SharePoint site, you have the option of linking the list to the server. This linking allows you to keep list data synchronized between Excel and Windows SharePoint Services.
If you choose to publish the list, you must have permissions to create a list on the server that is running Windows SharePoint Services.
SharePoint lists use calculated values instead of the formulas used in Excel. If you link a list that has formulas in it to a SharePoint list, existing formulas in Excel will be converted to calculated values during publishing and synchronization. Likewise, you will see a formula for cells in a list column in Excel if a user has created a calculated column in the linked SharePoint list. The column will be read-only in Excel.
Data types supported by Windows SharePoint Services
When you publish a list to a SharePoint site, the data type for each column will be designated in the SharePoint list as one of the following data types:
- Text (single line)
- Text (multiple lines)
- Currency
- Date
- Number
- Hyperlink
When you publish a list, the data type determined for each column will be displayed in Step 2 of the Publish List to SharePoint Site wizard. For each data cell in a column, Excel tries to determine the appropriate data type. If all the cells in a column have the same data type, Excel will make that the data type for the column, as long as the data type is supported by Windows SharePoint Services. If a column has cells with different data types or different number formats, Excel applies a data type that is appropriate for every cell within the column. For example, if you have a column that contains numbers and text, the data type in the SharePoint list will be text.
If you want to determine for yourself what the resulting data type will be, you can cancel the wizard and make sure the data type is the same for all the cells in the column. You can then run the wizard again and, as long as the data type is supported by the SharePoint list, the wizard will apply that data type to the column in the SharePoint list.
Data type validation with linked lists
For lists that are linked to a SharePoint site, Excel will recognize the data type assigned to each column and validate the data against that type. In order to promote the integrity of SharePoint list data, Excel enforces data type conformity when data is entered into cells in a linked list.
For example, if you attempt to enter text in a column that contains only numbers (excluding the header row), you are prevented from entering that data and notified of the reason. Excel also provides an error-checking smart tag to indicate the data type mismatch when invalid data is detected by error checking. In addition to the standard error-handling provided by most error-related smart tags, the Data Validation smart tag provides the Display Type Information command. Clicking this command displays a dialog box that contains information about the data type associated with the column in the list you are editing.
If you attempt to synchronize a linked list when a cell in the local list contains invalid data, Excel will display a message that tells you the data type for the column. Data validation errors must be resolved before you can synchronize the list.