When I started working with SharePoint 2010 back in August 2009, I was thrilled when I found out that out of the box, I can synchronize a Project 2010 task list to a SharePoint 2010 project task list.
My world fell apart when I realized that the capability to synchronize Excel spreadsheets with a SharePoint list was no more in 2010. It bothered me why this was taken away since I can easily do this with Excel 2007 and SharePoint 2007.
While working on an expense report in Excel 2010 tonight, a crazy idea dawned upon me "Hmm, I wonder if I can install the Excel 2007 Add-in that allows me to sync an Excel table to a SharePoint list in Excel 2010 … " Long story short, it worked!
I was overjoyed (do you hear Stevie Wonder in the background?) that tears started to fall … OK, OK, enough of the drama. Here are the steps on how you can synchronize an Excel 2010 spreadsheet to a SharePoint 2010 list:
1. Make sure you have Excel 2010 installed on your machine (just makin sure).
2. Download and install “Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists” to your copy of Excel 2010.
3. Create or open an Excel Spreadsheet (make sure the file is saved as .XLS)
4. Create an Excel table (click here to learn how)
5. In Excel 2010, from the Design tab, click Publish and allow Sync
6. Enter the SharePoint site URL, list name and description
7. Click Publish
That’s it! You can now update either the Excel spreadsheet or SharePoint list and it will be in sync. Make sure you manually sync Excel with SharePoint by right-clicking within the Excel table and select Table | Synchronize with SharePoint.