Connected Excel Spreadsheet to SharePoint List

Question

Here is what I have done and what I am trying to do. I have a SharePoint list that I exported to Excel and then created a connection. When I add an item in the SharePoint list the new items are added to the excel spreadsheet automatically or after I click "Refresh" in excel.

The problem is that if I add items in the excel spreadsheet they do not appear in the SharePoint list as they had done going to the list first. Do I have to create a second connection for items that are added to the excel spreadsheet to appear in the SharePoint list?

Can anyone guide me?

Answer 1

This feature is not currently out-of-the-box and never was. The natural flow of update/refresh is from SharePoint to a local excel spreadsheet born out of the natural export feature and not the other way around. The closest thing was available a long time ago by adding an add-in to Excel 2007 (tested and verified in sharepoint on prem 2007 and 2010 by me).

The item is still available to download at Microsoft so I guess it is still valid https://www.microsoft.com/en-gb/download/details.aspx?id=9345 . It also says it is for Excel 2007 so I do not know if it works on more recent Excel versions

However I do remember that it worked only if the file was saved as a XLS (old file format) and not XLSX (current file format).

If you are not satisfied there are on the market third party tools that claim to do the same. I am pasting one here

http://www.boostsolutions.com/blog/update-sharepoint-list-excel/

Answer 2

The round-trip synchronization was offered by Microsoft a while ago and they stopped it with Office 2010.

The Microsoft Excel 2007 add-in mentioned by Susan works even with recent versions of Excel, but it requires to start from an Excel file, and it creates a new SharePoint list. You can't use it to sync with Excel an existing SharePoint list.

If you want to be able to edit an existing SharePoint list from Excel, you need either a custom development or a 3rd party tool.

The company I work for develops such a tool: Synchronizer for Excel and SharePoint, that works with any version of Excel and SharePoint (on premises or online). You can try it for free. One of the main differences with BoostSolutions tool mentioned by Susan (upside or downside, I'll let you decide) is that it requires a client install, but no server install.