Friday, March 20, 2009

Using Microsoft Excel with SharePoint 2007

Nice article and tip for end users which is often overlooked is the ability to export a SharePoint list into Excel. Here's a nice write-up on how to do this. Using Microsoft Excel with SharePoint 2007

I would like to point out one major error in the article: "You can't use Excel to edit the data that's stored in SharePoint. That's because the SharePoint data is linked to Excel through a Web query file. In this particular scenario, a Web query file facilitates only one-way operations."

This isn't true at all. The authors of the article fail to mention the List Toolbar which does offer the ability to Synchronize your SharePoint list within Excel. You have to have edit rights to the list in order to write back to the list.

In Excel 2007
You can add the 'Synchronize List' option to your Quick Access Toolbar by clicking the Office Button -> Excel Options (bottom right) ->Customize -> Change the first dropdown to Choose Commands from All Commands and locate the Synchronize List option. Click Add. Now the shortcut will appear at the top of Excel next to your save, undo, and redo changes.

Note you can also click the small arrow next to the Quick Access Toolbar and select More Commands.

Don't you love how intuitive Excel 2007 is! (note the sarcasm)

In Excel 2003
Just enable the List Toolbar by View->Toolbars - List

One key point the article does make is the inability to apply formatting. In order to do that, you'll need to create separate sheets in your Excel workbook and use lookup formulas to the data.