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 that 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:
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.
3. Click on Browse Button and locate the SynchronizeWSSandExcel.xlam file, on the above location.
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”.
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.
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: https://msdn.microsoft.com/en-us/library/bb462636%28v=office.11%29.aspx
18 thoughts on “Synchronize Between SharePoint Lists and Microsoft Excel”
Great, saving xls format does the trick. And even tells you when there are conflits to decide!
Hello, thank you very much for the contribution, your blog is incredible! I have been looking for this for many months. I have a question, is it possible to export the sharepoint list to excel but with filtered data, that when exporting only the filtered data is exported. I tried but I can’t, it would be very helpful.
Thank you so much.
Creating a Listview with required filters and then exporting will export Filtered data in SharePoint Online. PowerShell also works!
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!~
Here you go: Download SynchronizeWSSandExcel.xlam
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.
This is not working with Excel 2016. where to get add-in (SynchorinizeWSSandExcel) for excel 2016 to sync it with sharepoint list
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!
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.
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?
If we want to sync after close and re-open, we need to save the excel file with .xls extention.
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,
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…
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: https://www.softfluent.com/store/sharepoint-list-synchronizer
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
Thanks! I’m Glad I could help you!!
True Jon! That’s the Limitation.
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.