Synchronize Between SharePoint Lists and Microsoft Excel

Requirement: End-User has an 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 the SharePoint list excel 2007.

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

1. Download and install the Add-on SynchronizeWSSandExcel.xlam . 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 the Go button next to "Manage: Excel Add-ins Dropdown.
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 the table (If it's 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 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 a 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 the 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:


  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?

  7. If you want to keep connection active even if you close excel just save excel file in Excel 97-2003 Workbook (*.xls) format.
    When you re-open excel file hit refresh button and list will be updated.

  8. Hi! When I publish mu excel file using the SynchronizeWSSandExcel add-in all the formulas in the excel file are converted to text/Number formats. Is there a way to be able to keep the formulas in Excel and sync with SP? Thanks!

  9. This is not working with Excel 2016. where to get add-in (SynchorinizeWSSandExcel) for excel 2016 to sync it with sharepoint list

  10. Thanks for the article, though is very disappointing how poorly sharepoint lists are supported by Microsoft and mediocre functionalities like Editing an existing list in excel or maintaining a sync when file is saved is not available.

  11. Just an FYI, Microsoft has removed this add-in from its website...if you know where it can be downloaded now, that would be greatly appreciated!~


Please Login and comment to get your questions answered!

Powered by Blogger.