Excel Services in SharePoint 2010 Feature Support

Excel Services does a good job of rendering Excel spreadsheets with a high degree of fidelity.  In other words, what you see in Excel is what you will see in Excel Services.  Excel, however, is incredibly feature rich, comprising features added over decades of development.  By comparison, Excel Services is a young product, so naturally Excel Services doesn’t yet support all the things you might create in Excel.  So what does Excel Services in SharePoint 2010 support?  And how does Excel Services deal with files that contain features it doesn’t support?  That is the topic of today’s blog post.

I am going to assume readers are aware of our feature support in Excel Services 2007.  If you need a refresher, read our original blog topic on the matter.  The following sections build on top of this topic.

Support For New Excel 2010 Features

Hopefully by now you’ve heard about some of the great features in Excel 2010 (If not, read through our posts here).  By and large, most of the new features you’ll hear talked about for Excel 2010 will work in some way in Excel Services.  Some features will display as they do in Excel.  Others are also interactive.

• Sparklines can be viewed
• Icon Set and Data Bar improvements can be viewed
• The new functions are supported
• Slicers can be viewed and interacted with
• PivotTable Named Sets can be viewed
• Other PivotTable improvements can be viewed
• PowerPivot files can be viewed and interacted with

In addition to this, I am happy to announce that embedded images, a long time feature of Excel, are now supported and can be viewed in Excel Services.  Here’s an example of a report with an embedded image:

Improved Handling of Unsupported Features

In Excel Services 2007, if a file contained an unsupported feature, Excel Services would not be able to open the file at all.  This was a frustrating point for customers, as the unsupported feature was often something that the customer didn’t care about in the context of rendering in Excel Services.  For example, a workbook may have a cell comment, which is unsupported, on Sheet3, but the workbook, when rendered via Excel Services, was only meant to show the chart on Sheet1.  In this scenario, the only recourse for the customer was to remove the unsupported feature.

In the SharePoint 2010 release of Excel Services, a lot of work went into bringing support for new features as well the other investment areas that were discussed in the Excel Services overview post.  With every release we narrow the functionality gap between Excel and Excel Services and reduce the number of unsupported features, but even in this new release, unsupported features still exist.  To help users work with this limitation, Excel Services will simply “ignore” certain unsupported features.  In other words, rather than blocking the entire file from loading, Excel Services will load the file just fine, you just won’t see the features that Excel Services doesn’t support.

So which features fall into this bucket?  Here are the features that will not prevent Excel Services from loading a file:

• Cell comments
• Formula references to external books
• Query Tables (also known as external data ranges)
• VBA
• Anything using OfficeArt technology, such as Shapes, WordArt, SmartArt, Org Chart, Diagrams, Signature Lines, Ink Annotations, etc.

A couple notes about the above list.  These features continue to be unsupported, so that means they don’t render, execute, or work in any way like they do on the client.  Most of the features in the above list will not render at all in Excel Services.  For example, if there’s a shape near cell A1 when viewed in the client, you will see no shape at all when viewed on the server.  Other features, like formula references and query tables, show you values that were last refreshed in the client.  In other words, the values in the cells are still there, but you cannot update them in any way.  Lastly, VBA code will not execute on the server.

One additional note about VBA.  Excel Services 2007 didn’t support loading *.xlsm files at all because such files are meant to contain macros and files with macros would not load.  Now that the SharePoint 2010 version of Excel Services knows how to ignore VBA macros, I am happy to say that also means *.xlsm files can now be loaded in Excel Services.

Viewing a File with Ignored Unsupported Features

You may be wondering, if Excel Services will load files and not render certain unsupported features, how do I know if the file I’m viewing is missing features?  Excel Services will display a notification above the sheet that the file has missing features.

This is your first clue that the file is rendering different than Excel.  If you’d like to learn more about which unsupported features Excel Services found in the file, click the “Details…” button.

What About Other Unsupported Features?

All other unsupported features will continue to behave as they do in Excel Services 2007.  That is, Excel Services will block loading of the file if it detects the existence of one of these features.

This help topic for the 2007 release covers the detailed list (just ignore any mention of images, as I already mentioned those are now supported).

Summary

I hope that the changes we’ve made to how we handle unsupported features will alleviate many of the pain points our customers experience when rendering spreadsheet files with Excel Services.  I would love to hear your feedback about the work we’ve done, as well as any thoughts you have on which unsupported features you’d like to see supported in a future release.