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.
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.
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
You made my day!! Great post!!
Very good post!
This works like a charm even in SharePoint 2013, 2016, along with Excel 2010, 2013 versions !
Awesome..
Works like a charm in Excel 2013, SharePoint 2013, 2016 versions too ! Awesome !