Connecting an Excel SpreadSheet to SharePoint 2010 list

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Figure 15 – Item Updated in the Connected Excel Workbook

9 Responses to “Connecting an Excel SpreadSheet to SharePoint 2010 list”

  1. Everything is ok, but how to synchronize excel 2010 and SharePoint lists like was in 2003 Excel :/

  2. @ 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?

  3. 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

  4. 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?

  5. This is unreliable at best. The connections are easily deleted by Excel.

  6. 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?

  7. 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.

  8. 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

  9. 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!

Leave a Reply