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

Improve this question

4 Answers 4

Reset to default
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.

Improve this answer
1
  • Ta, and thanks for the heads-up about employer :-)
    – SteveC
    Nov 7, 2017 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.

Improve this answer
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.

Improve this answer
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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