Sync between list and excel

2016-11-10

Question

_{Thursday, November 10, 2016 10:36 AM}

I would like to know if it is possible to sync data between sharepoint and excel file

The excel file is in sharepoint library.

I like to update the excel data automatically once I have the sharepoint list.

I am using SharePoint 2010

All replies (4)

_{Thursday, November 10, 2016 2:13 PM ✅Answered | 1 vote}

So which list is being automatically updated and which list is being populated?

Try this and see if it helps

Introduction to synchronizing a list with Excel

There are several ways to synchronize data between a SharePoint list and Excel. Whichever way you choose, this synchronization is one-way, or unidirectional. Changes made in the SharePoint list can be written to Excel, but any changes that you make in Excel are not automatically written to the SharePoint list.

For more information on creating and editing lists in SharePoint, see these topics:

Synchronize SharePoint list data by using the Export to Excel command

NOTE: The following instructions use Excel as an example. If you are using a different spreadsheet program, consult the Help information for that program.

Navigate to the SharePoint site that contains the list you want to synchronize with Excel.
Click the name of the SharePoint list on the Quick Launch, or click Settings, click Site Content, and then locate the list.
Click the List tab on the ribbon, and then click Export to Excel.
If you are prompted to confirm the operation, click OK.
In the File Download dialog box in your browser, click Open.
If you are prompted to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.
In the Import Data dialog box, select the How you want to view this data and Where do you want to put the data options.
Optionally, you can click Properties and set the connection properties.
Click OK when you're done. The list should appear in Excel.

Excel creates an Excel Table with a data connection based on a web query file. Changes are not made automatically. To see further changes made to the SharePoint list in Excel, you must manually update by clicking Refresh All on the Data tab.

If your SharePoint list contains folders, the folder structure does not appear in the resulting Excel table. However, the Item Type and Pathcolumns are added to the Excel table so you can filter and sort the data, based on its type and location or subfolder of the list.

</section>

Thanks, Danny Hickman IT Support Specialist

_{Friday, November 11, 2016 2:49 AM | 1 vote}

Hi Iftikar H,

According to your description, my understanding is that you wanted to sync Excel sheet with SharePoint.

To accomplish a two-way synchronization, you can download a free Add-In from Microsoft. You can download it:

http://www.microsoft.com/en-us/download/details.aspx?id=9345

With this tool you have to create a table in Excel, publish it to a SharePoint list and synchronize with it.

You can refer to the following articles:

Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists:

http://msdn.microsoft.com/en-us/library/office/bb462636(v=office.11).aspx

Connecting an Excel Sheet to SharePoint:

http://sharepointrhapsody.com/2013/04/02/connecting-an-excel-sheet-to-sharepoint/

even this is 2007 add in but works in 2010. You may need to restart you PC after add-in installation.

Best Regards,

Lisa Chen

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected]

_{Monday, November 21, 2016 3:11 AM}

Hi Iftikar,

Is there anything update?

Please remember to mark the reply as an answer if it helps.

Have a nice day!

Best Regards,

Lisa Chen

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected]

_{Wednesday, February 6, 2019 11:43 PM}

Hi there Danny Hickman - and anybody else who can help :),

I am using this method to try and sync a SharePoint list with an Excel sheet. It only syncs from SP --> Excel, not the other way. I have tried this after saving the file as both XLS and XLSX.

Any changes made in the Excel sheet get blown away when I refresh the connection.

Is this the expected behaviour?

thanks

Colin

Share via

Sync between list and excel

Question

All replies (4)

Introduction to synchronizing a list with Excel

Additional resources