Pages

30 Mar 2014

Two-way Synchronization between SharePoint List and Excel Table

Hey there folks! There is a feature that existed since SharePoint 2003 and not many people know about. This feature allows you to publish a table to a SharePoint list right from Excel. After that you can two-way sync any changes.


  • First of all download an add-in for Excel. Yes, it says "Excel 2007", but it will work for all versions of Excel including 2013.

  • After installing this add-in, In excel, you will find a new button:

Publish Sync button will appear only after installing Add-in for Excel 2007

  • In order to enable two-way sync, select a table, on the INSERT tab select TABLE and press OK:


  • The, in DESIGN tab, press Publish and allow Sync.






  • In a dialog box, enter a url of the SharePoin(2003/2007/2010/2013)  web site, specify a list name that will be created and press Publish.


  • The list will be created:

    This list was created right from Excel
    You can edit a newly created SharePoint list and then, in Excel, after selecting your table, in the context menu: Table--> Synchronize with SharePoint. And viola! The changes from SharePoint will appear in the Excel table:
    Select your Excel table and Synchronize with SharePoint in order to see the changes made in SharePoint or to send changes made from Excel to SharePoint list



    Watch a video demo from SharePoint Conference 2014 starting from 32:00 to 39:00. (credits to Dux Raymond Sy)
    Beyond Deployment: How IT Can Inspire, Motivate And Drive Sustainable Adoption?

    See also:



    5 comments:

    1. Thats nice! Thanks...Just 1 doubt. I am creating this excel via Java code. Is it possible to set the connections in the java code?

      ReplyDelete
    2. the Dialog box is not popping up after click on publish , any tips ? ( I am using MS office 2013 64 bits

      ReplyDelete
    3. If you want to do the same, but with an existing SharePoint list, try this: http://www.softfluent.com/product/sharePoint-list-synchronizer/

      ReplyDelete
    4. The formulas in my excel table are changed to text/number format after running the MS Synchronize add-in. Is there a way to sync and keep the formulas in the excel table?

      ReplyDelete
    5. Same question ^^ How do we keep the formulas?

      ReplyDelete