Tuesday, April 3, 2012

SharePoint List Synchronization with Excel

A question which is often asked is how you can synchronize a SharePoint list with Excel. The answer differs on what version of Excel you use.

Excel 2003 with SharePoint 2007/2010

With Excel 2003 the synchronization works in both directions. This means that you can create a SharePoint list connect to Excel, make changes in Excel and write back to Excel. The single restriction is that you can't create new items in Excel but modify existing ones.

Excel 2003 has a bug in which an alert pops up and says that the field is read-only, when you try to modify a field. There is a patch for this bug found here: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=952608

To synch the lists go to the list you want to synch and click on "Export to Excel" button in the ribbon.

The list will be opened in Excel and automatically connected to the SharePoint list. Now you can make changes within the Excel sheet. To synch back click on "Data" >> "Lists" >> "Synchronize Lists". Ensure yourself that a cell within the table is clicked. Otherwise the "Synch Lists" menu item is disabled.

Excel 2007 with SharePoint 2007/2010

With Office 2007 Microsoft has choosen Access to be the tool for synchronzation with SharePoint. You can still create a connection from Excel to a SharePoint list, but the synchronization is one-way from SharePoint to Excel.

If you still want a two-way synchronization you can download a free Add-In from Microsoft. With this tool you have to create a list in Excel, publish it to a SharePoint list and synchronize with it. But it not addresses the issue to synchronize with an existing SharePoint list

There is also a Add-In from third party organization: http://www.softfluent.com/products/sharepoint-list-synchronizer. With this tool you can also synchronize with existing SharePoint lists.

Excel 2010 with SharePoint 2007/2010

Excel 2010 offers like Excel 2007 a one-way synchronization. You have the use Add-Ins to synchronize two-way with SharePoint. The better way is to use Access 2010 which can be synchronized with a SharePoint-List and then exported to a Excel list or the way back.

It seems that the MS Add-In works also fine with SharePoint 2010.  (See:  http://sp.meetdux.com/archive/2010/03/30/Sync-Excel-SharePoint-2010.aspx )


Richard said...

Your blog about the "SharePoint List Synchronization with Excel" helped me a lot. Thank you! Baris

klp said...

If you want to update or synchronize your Excel document from within a SharePoint workflow, then you may look at ready to use third-party actions to set or get cell values: http://www.harepoint.com/Products/HarePointWorkflowExtensions/Office-SharePoint-Workflow-Action.aspx