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.
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.
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 following SharePoint excel tutorials:
- Excel Service to import SSAS Cubes and Generating Pivot tables in SharePoint BI
- Excel Service to import External DB Using Secure Store connection
- Configure Excel Service Application in SharePoint 2013
- Export Excel Data to SharePoint 2013 list from Excel itself
- Export selected record to excel in SharePoint 2013 using the JavaScript client object model
- 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
Hope this will be helpful to publishing and synchronizing excel tables to SharePoint lists.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com
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.