Share Excel lists on a SharePoint site

Applies to
Microsoft Office Excel 2003

Collaborating on data has never been easier. Excel 2003 list integration with Microsoft Windows® SharePoint™ Services allows you to create lists in Excel worksheets and publish them to Windows SharePoint Services sites for easier editing. The lists can be edited in Excel or on the Windows SharePoint Services site.

Publishing an Excel list to your SharePoint team site

Better yet, new Excel list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) functionality makes creating and working with groups of related data both quicker and easier. For example, if you keep track of sales data in an Excel workbook, and your salespeople report their numbers each week, you can designate that data as an Excel list to get instant calculation, filtering, and sorting options.

Then, you can publish the Excel list to a SharePoint site, where it becomes a SharePoint list (list, interactive 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 Microsoft Windows SharePoint Services.). This means your salespeople can enter and view their numbers on the shared site, which makes it easier for everyone to view the latest data. And, if you need to keep the data in your workbook up to date, that's fine too, because you can easily synchronize the data in the workbook with the data on the SharePoint site by linking the list to the server.

Getting started with Excel lists

What's so great about list functionality in Excel 2003? Once you've specified a range of cells as a list, you automatically get autofilter drop-downs on your headings that allow you to view data based on specific values from the list. You can also filter for values like "top 10" sales, or quickly sort your list values in ascending or descending order.

New list functionality also gives you the ability to insert or delete rows of data in your list without affecting adjacent data on your worksheet. And, a total row allows you to perform common calculations on columns of data within the list with just a few clicks.

To create a list that includes all the great new list features in Excel 2003, simply select your data, right-click the selection, and then click Create List.


Publishing lists to a SharePoint site

When you publish an Excel list to a SharePoint site, it automatically becomes a SharePoint list. SharePoint lists and Excel lists handle data in much the same way, with a couple of important differences you should be aware of:

  • Formulas     When you publish an Excel list to a SharePoint site, any formulas in the Excel list will be converted to values in the SharePoint list. If the lists are linked, this means the formulas in your Excel list will be converted to values, too.

Also, if you create a calculated column in the linked SharePoint list, you will be able to read the formulas and values for that column in the Excel list, but you will be unable to edit them in Excel.

  • Data types      When you publish a list to a SharePoint site, each column will be assigned one of the following SharePoint data types:
    • Text (single line)
    • Text (multiple lines)
    • Currency
    • Date/Time
    • Number
    • Hyperlink

If a column has cells with different data types, Excel applies the data type that is appropriate for every cell in the column. For example, if a column contains numbers and text, the data type in the SharePoint list will be text.

 Note   To ensure the integrity of SharePoint list data, Excel conforms to the data types used in the SharePoint list. If you type data into a cell of either linked list that doesn't match the SharePoint data type for that column, an error message will be displayed.

Synchronizing the lists

With lists in two places (in Excel and on a SharePoint site), it's important to synchronize the list data from time to time. To keep the data in both lists up to date, you'll want to incorporate any changes made to the SharePoint list and update that list with any changes you made to the list in Excel.

Click Synchronize List on the List toolbar in Excel to syncrhonize changes in Excel with the SharePoint list

When you publish an Excel list to a SharePoint site and link it to the server, you can easily synchronize the lists by clicking the Synchronize List button on the List toolbar. When you no longer need to collaborate on the list data, you can simply unlink the list. Both lists remain, but they can no longer be synchronized.

Resolving conflicts and errors

What could go wrong? Because both versions of a linked list can be updated simultaneously, conflicting changes could be made to the same data. But not to worry—if someone else has made changes to the same data you're working on in the local Excel list, the Resolve Conflicts and Errors dialog box will be displayed when you try to synchronize the lists.

The Resolve Conflicts and Errors dialog box

Synchronization could also hit a snag if the local list contains a cell with invalid data. In cases like this, a different dialog box or warning message may appear, with details about the conflict and suggestions to help you resolve it.

Images in this article were excerpted from Training on Microsoft Office Online.

Applies to:
Excel 2003