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