Sharepoint 2010 has a feature of connecting an Excel workbook
Whenever an item is added/updated/deleted in SharPoint list the record is also added/updated/deleted in the connected Excel workbook
In this example we will see how to connect an Excel workbook to SharePoint list
Initially I have created a simple SharePoint custom list with few records in that, the list contains four columns Title (default column), Employee Name (single line text), Designation (choice filed), Department (choice field)
(See in Figure 1)
Figure 1 – Simple SharePoint Custom List
Now, we are ready to connect, click on List then click Export to Excel option (see Figure 2)
Figure 2 – Click on Export to Excel
A file download window pops, click on Save (see Figure 3)
Figure 3 – File Download Window, Click Save
Save the file in some location (in this example I have saved in E:\Employee location) (see Figure 4)
Figure 4 – Saving Excel Web Query File
Then open the saved Excel Web Query file from the location where you saved or click on the Open button in download dialog window (see Figure 5)
Figure 5 – Open the downloaded file
While the Excel Web Query File is opening you can view the Microsoft Excel Security Notice (the dialog window says warning about the data connection) click on Enable (see Figure 6)
Figure 6 – Enable Data Connections
As soon as you click on Enable button you can view the SharePoint list data in the Excel Web Query File (see Figure 7)
Figure 7 – Excel Web Query File, Shows SharePoint List Data
Now we have to set the data connection to this workbook, to do that select Data menu and click on Connections (see Figure 8 )
Figure 8 – Data Connections for the workbook
A Workbook Connections window opens, select the workbook name and click on Properties (see Figure 9)
Figure 9 – Workbook Connections
Then in Connection Properties dialog box select Enable background refresh and Refresh data when opening the file, then click OK (see Figure 10)
Figure 10 – Settings the Connection Properties
Now we have to save the Excel file in a trusted location, so before saving the Excel file first we have to specify a trusted location, to do that Select File menu and click on Options (see Figure 11)
Figure 11 – Specifying Trusted Location
In Excel Options window select Trust Center and click on Trust Center Settings (see Figure 12)
Figure 12 – Specifying Trusted Location
In Trust Center dialog window select Trusted Locations and click on Add new locations, specify a path for trusted source, then click OK (see Figure 13)
Figure 13 – Specifying Trusted Location
Click OK on Trust Center window, click OK on Excel Options window, then save the Excel workbook in some location, in this example I’m saving the Excel file in the same location (E:\Employees) where Excel Web Query File was saved
Now I will try to add the new Item in the SharePoint list, so we will see whether item is added in the Excel spread sheet (see Figure 14)
Figure 14 – Adding Item in Sharepoint List
Now I can see the newly item added SharePoint list is also updated in the Excel workbook (see Figure 14)
Figure 15 – Item Updated in the Connected Excel Workbook
Everything is ok, but how to synchronize excel 2010 and SharePoint lists like was in 2003 Excel :/
@ Zbigniew: The article explain the sync between SharePoint 2010 list and Office 2010 SpreadSheet, so are you looking for sync between SharePoint list and Excel 2001?
great article.
What happen if i want to do the reverse?
let’s say i downloaded some data from a sharepoint list, i do some homework offline that change the data, then i want the new data to update the list ones
is this possible?
thank you in advance
Delfo
It’s working great. But I tried to do same but stored the excel file in a sharepoint documet library. In this case I did not got an update when the list updates, any solution?
This is unreliable at best. The connections are easily deleted by Excel.
Excellent!!
My question is: will it be possible to do vice-versa i.e if i update in excel spread sheet, it should also automatically get updated in sharepoint too?
I have similar issue where the data does not show up in Office Web Apps. if I open the file in Excel, the data will refresh. If I save the file it still does not show up when viewing with Office Web Apps but if I completely exit out of the browswer ad come back in, the file is updates.
If you need to access Excel document from within a SharePoint workflow, then you may look at ready to use third-party actions to set or get cell values: http://www.harepoint.com/Products/HarePointWorkflowExtensions/Office-SharePoint-Workflow-Action.aspx
Overly complicated, this is one feature Excel 2003 did better.
This only tells me how to update the Excel file based on what is put into SharePoint. I need to do the opposite – update the Excel file and push that into Sharepoint. So how do I do that ?
I can’t go back to using Excel 2003 to update my lists and with the volume of data to enter each month working directly in the SharePoint list is a pain. If anyone knows how to push from Excel to Sharepoint I appreciate any help!