Connecting an Excel Sheet to SharePoint

If you were lucky enough to attend the Microsoft SharePoint Heartland conference last month, then you were able to see the keynote speaker Dux Raymond Sy present. He was very entertaining and insightful regarding how a business uses SharePoint. During his presentation, he showed how easy it is to create an integrated Excel file that has values updated automatically from a SharePoint list or vice versa. This is a great tool to take advantage of because it bridges the gap between users who are hesitant to use SharePoint and users who prefer SharePoint. The steps below outline how easy it is to get this started. This feature is easy to set up but unless you know where to look it may be hard to get started. There is one caveat to this example, you will need an Excel add in to do this. It is a free add in that was made available in 2007. Hooray!

Setting Up the Excel Add In (SynchronizeWSSandExcel)

First thing is first, go to the link below to download the add in. http://www.microsoft.com/en-us/download/details.aspx?id=9345 Once that is done, run the executable file that gets downloaded from your downloads folder.

Click Run in the pop up window

Click Yes on the next pop up

Click Yes to agree the licensing agreement.

You may have to extract/unzip the files. Use the default file path location and click Unzip or Extract.

The next thing we need to do is run the Excel Add in file that we just downloaded and unzipped. Within Winzip go to the text box with the label Unzip to folder (see image above). Copy and paste the file path into the location of a windows explorer window. Then double click the icon named SynchronizeWSSandExcel.

The Excel Add in will open up Excel and display a pop up that enables Macros in the worksheet. Click Enable Macros.

When you go into your Excel file, you should now see under the Design tab a SharePoint section with a button called Publish and allow sync. Please note, you may have to restart Excel in order for the installation to work correctly.

Connecting your Excel Spreadsheet to SharePoint

Open up a new Excel file and put some information into it.

Next, make that information into a table. Highlight the information and under the Insert tab, click Table.

A pop up window will display asking about the header columns, click Ok.

Click on the table that you created and go to the Design tab. Click Publish and allow Sync.

Next, a pop up window will display. Provide the URL of the SharePoint site you want to create a list in the Address text box. Provide the name of the list and a description for the list then click Publish.

The table will update in the Excel file with an ID column now.

Also, our list has been created in SharePoint.

Lets add a new row in our Excel file. Note: Be sure not to provide the ID value. For my example, I would provide a value in a new row for columns Department and Amount. Let the table auto increment the ID number. You can see if you did this right or not by looking for the little blue icon in the bottom right of the last record. Hopefully you can see that in the screen shot below.

Now let’s synchronize our changes to our SharePoint list. Right click into the Excel table and select Synchronize with SharePoint under the Table category.

Go to your SharePoint list and refresh the page. You will now see your new record in the SharePoint list!

Now we are going to test updating our SharePoint list and then synchronize our changes to our Excel book. Let’s add a new record to our SharePoint list.

After adding our record to our SharePoint list, let’s go back to our Excel file and synchronize our records. Click in the table and right click, select Synchronize with SharePoint under the Table category.

Now we can see our finance record that we just created in our SharePoint list is now in our Excel file. We just made Excel and SharePoint work in perfect harmony. All it took was an Excel Add in called Synchronizing Tables with SharePoint Lists.