1

How can you have two-way synchronization of data between Excel and a SharePoint list?

I've read the Office support Manage large lists and libraries in SharePoint page and the limitation about Excel being one-way

Microsoft Excel You can export SharePoint lists to an Excel Table, which creates a one-way data connection between the Excel table and the SharePoint list.

Is there anyway to have a two-way sync?

... the end users want the speed and flexibility of editing, conditional formatting, freeze panes, etc. that they're used to in Excel

2

There is no way to have two-way sync out of the box (OOTB) between Excel and a SharePoint List as Microsoft deprecated this feature 10 years ago. OOTB Options include creating a new SP list from an Excel file and exporting a list to an Excel file.

To have bi-directional sync, you need a third party tool such as SharePoint List Synchronizer. Disclaimer: this product is edited by SoftFluent, my employer.

  • Ta, and thanks for the heads-up about employer :-) – SteveC Nov 7 '17 at 16:10
0

As far as I know the two-way sync is no longer available, it used to be possible in SP 2010.

'Export to Excel' is still possible, so you can set up e.g. a linked Template for a report and even make graphs that will be refreshable with new data from a Sharepoint List.

Something like 'Freeze Panes' could be achieved with a little creativity and web-parts - perhaps make linked lists in two web parts on a page.

Conditional Formatting is possible using 3rd party add-ins like Sparqube. They have an Indicator column which can highlight cells and lines based on field:value combinations. Or just stick to the Excel conditional formatting and used a linked file.

0

I’m proposing a workaround to the suggested third party solution. It is a two-parter. Part 1 addresses formatting and Part 2 addresses speed.

Part 1: Export the list to Excel and save the workbook back to SharePoint with a data connection. Format the workbook with all desired Excel features such as freeze panes, conditional formatting, etc.

  1. Export the list or library to Excel.
  2. Create a data connection to the list or library so that the workbook automatically syncs with list updates when opened.
  3. Format the workbook for printing.
  4. Save the workbook to SharePoint for users. For step-by-step instructions view my post at https://jennyssharepointtips.wordpress.com/2015/03/28/print-live-list-or-library-data-from-excel-workbooks-using-data-connections/.

Part 2: Instruct users on editing lists using Quick Edit (SharePoint 2013 and later) or Datasheet view (SharePoint 2010 and earlier).

Bonus speed option: Add the out of the box “Edit" button to the list views. It makes it quick and easy to access an item's edit form.

0

As a workaround, you can also use MS Flow to update a SharePoint List from an Excel file - https://www.youtube.com/watch?v=qxuTVjQbECo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.