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.

Image

Click Run in the pop up window

Image

Click Yes on the next pop up

Image

Click Yes to agree the licensing agreement.

Image

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

Image

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.

SynchronizeWSSandExcel File Path

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

Macro Pop Up

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.

Design Tab

Connecting your Excel Spreadsheet to SharePoint

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

Excel Data File

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

Insert Tab

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

Create Table in Excel

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

Design Tab and Table

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.

Create a new List in SharePoint

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

New ID Column in Table

Also, our list has been created in SharePoint.

New SharePoint List

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.

Add new Record

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.

Synchronize with SharePoint List

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

New Record in 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.

Create new record in SharePoint List

Another Record added to 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.

Synchronize with SharePoint List

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.

All Records in Excel

Advertisements
Tagged with: , ,
Posted in Excel, SharePoint
6 comments on “Connecting an Excel Sheet to SharePoint
  1. Mark says:

    Hi Andy,

    Great post. From a different angle, let’s say I have a native list in Sharepoint 2010 — is there a way to connect to it from Excel 2013, or better, Powerpivot 2013? I don’t want to import the list to Excel — I want to keep it stored centrally in Sharepoint — I just want to link the data directly to my data model.

    • andybdixon says:

      Hi Mark,

      Thank you so much for checking out the blog. If I understand your comment, you want to access the SharePoint list information in Excel 2013 almost like a data connection. The blog post points out using an add in called “Synchronizing Tables with SharePoint Lists”. What the add in does is it allows us to synchronize a SharePoint list with an Excel file.

      So when you go in and setup this synchronization, from now on, any updates made in the Excel file will be reflected in the SharePoint list and any update in the SharePoint list will be reflected in the Excel file. The Excel file communicates with the SharePoint list on an ongoing basis. I hope this makes sense and I hope that answered your question. Feel free to point me in the right direction if I misunderstood your question.

      Thanks,

      Andy

  2. You made my day!! Great post!!

  3. Sri Harsha Tadakamalla says:

    This works like a charm even in SharePoint 2013, 2016, along with Excel 2010, 2013 versions !

    Awesome..

  4. harsha6783 says:

    Works like a charm in Excel 2013, SharePoint 2013, 2016 versions too ! Awesome !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: