Excel Web Access 101

Excel Services is a new feature introduced with Microsoft Office SharePoint Server 2007 (MOSS 2007) Enterprise Edition. It enables you to, amongst other things, display information from an Excel 2007 spreadsheet inside an Excel Web Access web part. Why is this useful? There are all sorts of reasons, but the best by far is to use Excel 2007 to visualise data. You can build a nifty web-based performance management solution without needing specialist BI tools. Take the following simple example (click to view larger):

Conditional formatting is being used within Excel to help visualise the data. You can see immediately that black cars are selling as well as red, despite having a lower target. The traffic lights highlight problems with stock levels. In this case, there isn't enough stock to meet the target for red cars. But there are plenty of yellow cars to sell. The savvy sales person would come up with a spin to make yellow the new black, red, blue...

So how do you set this up?

Configuring Excel Services

If you have MOSS 2007 Enterprise Edition installed, your Shared Services Administration screen should look as follows:

To use Excel Web Access, the minimum you need to do is add the location of your spreadsheet as a 'Trusted File Location'. A trusted file location can be a SharePoint document library (format http://), web site URL (format http://) or file share UNC (format \\servername\fileshare). For each trusted file location, you can configure additional settings such calculation behaviour (cache lifetime, manual or automatic recalculation), access to external data sources (none by default) and user-defined functions (disabled by default).

Setting up the Excel spreadsheet

To prepare an Excel spreadsheet for display in MOSS 2007, you simply need to do two things:

• Create a named range for the data you want to display
• Store the spreadsheet in a trusted file location (see 'Setting up Excel Services')

To create a named range, select the data you want to display and, from the Office ribbon, select the Formulas tab and click 'Define Name'. Enter a name for your range (ideally one word and easy to remember). To get ready for the next step, count the number of rows and columns that form your named range

This means only the named range will be displayed inside Excel Web Access - all calculations and input fields can be tucked away elsewhere within the worksheet. You can even get clever and create multiple named ranges - one for an input form, to allow users to manipulate the data, and a second one to display the results, based on values in the input form.

Using Excel Web Access

To display your Excel spreadsheet in MOSS 2007, you need to edit a web part page and add the Excel Web Access web part. Then modify the web part properties:

• Enter the location of the spreadsheet (workbook)
• Enter the named range within the spreadsheet (named item)
• Specify the number of rows and columns you want to display

There are all sorts of extra things you can do, depending on how interactive you want the web part to be. You can choose the type of toolbar you want and whether or not people can recalculate the workbook. In the example above, I've switched everything off and have automatic recalculation every 5 minutes during the session. I also recommend sizing the web part to fit rather than leaving MOSS to its own devices.

Gotchas

Spot the mistakes I have made in the (recent) past :-)

1. If you want to change the title of the web part, you must first uncheck the box 'Autogenerate Web Part Title' in the web part properties (under 'Title and Toolbar' heading)
2. If you add rows and/or columns within the named range of the spreadsheet, make sure you adjust the number of rows and columns defined in the web part properties, or the bottom of your named range will disappear from view. You may also need to resize the web part to fit
3. If you move/copy the site to a new environment, make sure you configure the Excel Services settings for the new environment (i.e. configure the Trusted File Location)

Excel Web Access introduces all sorts of possibilities, thanks to the new data visualisation features introduced in Excel 2007 combined with Excel's ability to connect to external data sources, such as business applications and online services. Displaying the summary information inside a web part eliminates the need to open the spreadsheet in the full Excel application and makes it possible to display Excel data alongside other related information sources.

MOSS 2007 Enterprise Edition could get even more interesting if/when Visio Web Access joins the party (mentioned at the Office Developer Conference earlier this year.) For example, Visio and Excel together make it possible to create a fully interactive web-based business scorecard, complete with strategy map. The challenge remains that competition for delivering web-based data visualisations is heating up, thanks to the likes of Google and Zoho.

[Update: 07 May 08] Please check the first comment from Mark Miller, highlighting some of the limitations with the current version of Excel Services. (I should have highlighed it is a v1 feature.)

Filed in Library under: SharePoint, Microsoft BI

Technorati tags: SharePoint, MOSS 2007, Excel