Microsoft Office SharePoint Server and Excel Services - 04 Aug 2008

When I first read the description about Microsoft Office SharePoint Services (MOSS) 2007 and Excel Services I thought you could edit an Excel spreadsheet without having Excel installed on the computer that was accessing the portal.  I thought that this would be a perfect solution for a client that works with large Excel spreadsheets.  This particular client has remote offices that access the portal, so if a remote user needed to make a quick change on a spreadsheet, he or she could make it directly on the portal without having to download the entire spreadsheet.  After taking a closer look at Excel Services, it doesn’t really have the functionality that this client needs.  However, Excel Services does have some useful features that you might be able to use.  However, there are quite a few issues that you should be aware of before you implement Excel Services on your MOSS portal.
• MOSS Version.  You must have the MOSS Enterprise Client Access License (CAL) to use Excel Services.
• Excel Services and Data Connection Libraries.  You can use Excel Services in conjunction with a Data Connection Library to access Business Intelligence (BI) data.  In my opinion, this is one of the more useful features of Excel Services.  You could create a data connection library that connects to an Enterprise Resource Planning (ERP) package to produce reports, charts, or graphs in Excel.  The information in the spreadsheet can be displayed and refreshed directly on the portal.  However, you cannot modify any of this data and save it back on the portal without downloading the spreadsheet and working on it locally.
• Displaying Excel Spreadsheets.  With Excel Services you can view a spreadsheet on the portal without having Excel installed on the local machine.  Only Excel Spreadsheets saved in Excel 2007 (*.xlsx) can be directly viewed on the portal.  Previous versions (*.xls) cannot be viewed directly on the portal.
• Microsoft Office Version and Modifying Excel Spreadsheets.  You can modify designated fields on an Excel Spreadsheet if you plan ahead.  Once the designated fields are modified, you can recalculate the spreadsheet to view the results.  However, you can't save any of your changes to the spreadsheet without first downloading it and using Excel to modify it locally.  To configure a spreadsheet to allow for changes of designated fields, you must have the correct version of Microsoft Office to designate these fields.  These versions include Microsoft Office 2007 Professional Plus, Enterprise, and Ultimate. 
• Assigning Names to Cells to Allow for Changes on the Portal.  If you can anticipate which fields users would like to change on the portal, you must create a name for each of these cells.  You do this by selecting a single cell you want to name, and clicking on Formulas, Define Name and giving the cell a descriptive name.  It’s important to use a descriptive name, because if you allow for multiple cell changes, it may not be clear which name a cell refers to.
• Publishing the Spreadsheet.  After you assign names to all of the cells that will be modified, you must designate that these cells can be changed on the portal.  Click Office Orb, Publish, Excel Services.  Click on Excel Services Options, Parameters Tab, select the Parameters that you want to modify in MOSS, and save the file.  If the Excel Services menu item doesn’t appear, you probably aren’t running a version of Microsoft Office that allows publishing of Excel spreadsheets on the portal. 
• Place the Spreadsheet on the Portal.  After you’ve properly configured the spreadsheet you can place it on the portal. Select the page (or create a new page) where you’d like the spreadsheet to appear and click on Site Actions, Edit Page, Add a Web Part and select Excel Web Access.  Then select the spreadsheet you created for publishing.  Now you should be able to view the spreadsheet and modify the entries that you designated when you published the spreadsheet. Note, however, that there is no way to save your changes with any new values you input on the portal, but you can display the results of your modifications.
I was a little disappointed with the lack of functionality of Excel Services, because I was expecting to be able to modify a spreadsheet directly on the portal without having to download it first and then use Excel to modify the file.  But in reality, if you want to make any significant changes on a spreadsheet, you still have to download the file and work on it locally.  Hopefully, with the next release of MOSS, Microsoft will improve Excel Services to at least allow for simple modifications to Excel spreadsheets to be made and saved on the portal without downloading them first.  If you have to work with extremely large spreadsheets, one possible workaround is to use a terminal server that is installed at the same location as the MOSS portal and download the spreadsheet to the terminal server and use Excel on the terminal server to download, modify, and upload the changes to any large Excel files.