In this tutorial, we will discuss how we can use Excel Add-in which can publish and sync Excel tables to SharePoint Online or to SharePoint on-premise (MOSS 2007/SharePoint 2010/2013/2013) lists. We can easily publish and synchronize excel tables to SharePoint 2010/2013/2016/Online list.
We will discuss how to use WSS and Excel add-in to publish excel table to SharePoint 2010/2013/2017 to to SharePoint Online.
WSSandExcel (SynchorinizeWSSandExcel.xlam) Add-in
This is used to synchronize Excel tables with SharePoint Lists on SharePoint 2010/2013 and Office 365.
This was originally built for Excel 2007 for WSS/Moss 2007, but the good news is, it works well for SharePoint 2010, SharePoint 2013 and Office 365 SharePoint Online.
You can download Wss and Excel add-in from Microsoft Site and follow an instruction to install the plugin.
How to use this Add-in with Excel 2013?
Spin up the Excel 2013 (The usage will be similar to Excel 2007/2010.). Choose this Add-in under options -> Add-ins. You have to enable the Macros for the Add-ins to work.
How to Publish an Excel table in SharePoint 2010/2013/2016 and SharePoint Online Office 365?
Step-1: Create a table of data in a worksheet and click on the Design tab. You can see “Publish and Sync” in the menu bar appearing after installing the Add-in. This is only to create a SharePoint List.
Step-2: Click on that will bring a dialog box “Export Table to R/W SharePoint List”. For Office 365 SharePoint Online do not forget to enter “Description“, otherwise, the Add-In will fail.
Fill out the details and click on publish. For Office 365 based SharePoint lists you will be prompted for credentials.
How to use this Add-in to Sync data from SharePoint to Excel and Vice Versa?
After adding a List Item, go the excel worksheet -> right click on the Table data -> Table >Click on Synchronize with SharePoint. You can see the newly added/edited list item in your excel table.
You can also add/edit items in the excel table and synchronize with SharePoint by following the same procedure given above. This Add-in greatly helps in quickly creating a SharePoint List, but there are some limitations as well.
Saving the Excel:
The Excel sheet has to be saved as in Excel 97 – 2003 Workbook(*.xls) format. If you save in other formats, you will not be able to sync the list items as “Synchronize with SharePoint” option will not appear.
You may like the following SharePoint tutorials:
- Configure Excel Service Application in SharePoint 2013
- How to directly save Excel File into SharePoint Online Document Library
- import excel spreadsheet into SharePoint list 2013
- Export Excel Table to SharePoint Online List Step by Step Tutorial
- The workbook cannot be opened error in SharePoint 2013
- Excel cannot connect to the SharePoint list
Hope this will be helpful to publishing and synchronizing excel tables to SharePoint lists.
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Out audiences are from the United States, Canada, United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a SharePoint MVP(8 times), check out My MVP Profile. I have also worked in companies like HP, TCS, KPIT, etc.
So when I use this if I change something in excel it will update my SharePoint list and if I change something from SharePoint it will update my excel document?
If someone knows where I can download the add-in that would be great. Microsoft has removed it in the past 6 months from its website.