Poorman Excel Services: Excel query files

The Steps

If you don’t have SharePoint Excel Services and want to do more charting, pivots, or heavy excel-related functions than WSS or MOSS Standard allows, try “Exporting to Spreadsheet” from a list.  It works well since the data can more easily be maintained in a SharePoint list but shared and presented in Excel.  What I’ve outlined below, really just leverages the Excel query functionality.  This works with all versions of Excel and SharePoint. Here are my quick steps:

1. Go the SharePoint List you want to link to 
2. Create a view with all the fields you want to capture.  I recommend putting them in the order you want to report on as well.  I unusually call the view "zReporting".
3. Go To the Actions button and select “Export to Spreadsheet”.
4. Click Save, to the File Download box prompting to open the *.iqy file 
5. Rename and save the query (iqy) file to a network location where everyone has access.  Usually on the same SharePoint site make sense. 
6. Open the saved query (iqy) file and excel will open.
7. With the Excel file open, on Sheet1, right click in data and select Data Range Properties
8. Check off “Refresh Data on file open” and click Ok.
9. Create the formatting, pivots and charts as need in new worksheet. 
10. You cannot edit the data on Sheet1 as it is being pulled from SharePoint.   
11. Then just share a link to your excel doc.

Benefits

• Data still available in SharePoint 
• Allows multiple users to edit/add data 
• All the functionality within SharePoint can be used such views, calculated columns, etc.
• With SharePoint 2010, you can synchronize between Excel and SharePoint

For Example

Since the SharePoint Gantt view is not useful for annual representations, I pull a SharePoint list into excel and use the networkdays() function and formatting to present the data.  See below for example.

Supported with:
- Excel 2003, Excel 2007, and Excel 2010
- WSS, SharePoint 2003, SharePoint 2007, and SharePoint 2010