Thursday, May 17, 2012

Synchronize Between SharePoint Lists and Microsoft Excel

Requirement: End-User has a Excel file, wants to Create a SharePoint list with data from Excel file and Synchronize between SharePoint Lists and Microsoft Excel. Fairly Simple,The very first option comes to our mind is: Import Spreadsheet,  isn't it? nope, That can be used to Create List and import data but not when it comes to sync. Here the requirement is to synchronize SharePoint list excel 2007.

Solution: Use the "Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists" to synchronize SharePoint list with excel.

1. Download and install the Add-on from . This will create SynchronizeWSSandExcel.xlam under: C:\2007 Office System Developer Resources\Code Samples\XL2007SynchronizeWSSandExcel

2. Open Excel, Go to File >> Options >> Add-Ins and click on Go button next to "Manage: Excel Add-ins Drop down.
synchronize sharepoint list excel 2007
3. Click on Browse Button and locate the SynchronizeWSSandExcel.xlam file, on the above location.synchronize excel sheet with sharepoint list 

4. Now in Excel, Convert the data to table (If its not table already) by selecting the data, click on Insert Tab, and choose "Table".
synchronize sharepoint list excel 2010

5. Now, Place the cursor on the your table. In design tab will see "Publish and allow Sync" button and fill SharePoint site details as below:

6. That's all! You can now update data either from Excel or from SharePoint list. Once you update data, You have to manually sync by right-clicking within the Excel table and select Table >> Synchronize with SharePoint. This will synchronize SharePoint list and excel data.

synchronize excel list with sharepoint 2010 list

I verified that it works with MS Excel 2010 and SharePoint Server 2010 as well.

Existing SharePoint list cannot be used! Doesn't work when you close and Re-open. Oops!!
The above method works only when you create List from scratch and update/sync it further.  If you've a List already in SharePoint and want to Import to Excel, to do some update, This method will not help. Also, This will work until you close the excel. If you re-open, it won't work!!!

Want to keep One way sync from SharePoint List to Excel Sheet? Yes! its possible even when you close and reopen. How? Just export the List "Export to Spreadsheet" option from SharePoint list. Then from Excel, click on "Refresh" under data tab to get the latest data from SharePoint list.

More Info:

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Check out these SharePoint products:


  1. Works great until you close or try to save the workbook, then the "Synchronize with SharePoint" option is gone and it becomes just a connected workbook again and all you can do is refresh, not sync.

  2. Hi salaudeen,

    You r blog is so interesting its more than a SharePoint library keep posting all the interesting stuff we are learnig a lot and everything free of cost ! Excellent blog

    1. Hi Avinash,

      Thanks! I'm Glad I could help you!!


  3. Hi Salaudeen,
    thank you for the great post, but my client want to change excel sheet every day and they want to sync that changes to the sharepoint list.
    can you please advise me what to do in this case...

    1. Hi Nanny,

      If you want the other way (Update SharePoint. Excel sheet will get updated) is possible.

      But, I don't find any other way to make it in the reverse.

      Found this add-on, However, I haven't try yet:

  4. Hi,

    I want to update the excel sheet using mails, and then that excel sheet is in sync with sharepoint site. Is there any chance to wrap this up?

    Thanks in advance,

  5. Hi

    If we want to sync after close and re-open, we need to save the excel file with .xls extention.


  6. Hi, Thanks for the post, my question is - Can we embed the xlam file in excel?
    I want to export a list and format it in color etc. then I can sync that file from server to PC; if I sent to my manager it doesnt work because he has not installed this "SynchronizeWSSandExcel.xlam" file.

    Can we do something to have that file pre-installed/embedded in our required excel file?


Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...