Differences between using a workbook in Excel and Excel Services

Excel Services is primarily designed as a web-based, data-exploration and reporting system for Excel workbooks, and supports a subset of features in Microsoft Office Excel 2007. The following sections summarize which Office Excel 2007 features are supported and unsupported in Excel Services.

 Important   The ability to publish an Excel workbook to Excel Services is available only in Microsoft Office Ultimate 2007, Microsoft Office Professional Plus 2007, Microsoft Office Enterprise 2007, and Microsoft Office Excel 2007.

In this article


Supported and unsupported features when loading a workbook

You can load a workbook in Excel Services that is in Office Excel 2007 Workbook (.xlsx) or Binary Workbook (.xlsb) file format as a read-only workbook in three ways:

  • Specify a URL or UNC path in the the Excel Web Access Web Part Workbook property.
  • Connect a List View Web Part of a document library to an Excel Web Access Web Part, and then pass the URL of the workbook stored in the document library to display it in Microsoft Office Excel Web Access.
  • View a workbook saved in a document library in the browser. (Point to the item, click the arrow next to it, and then click View in Web Browser.)

All other Microsoft Office Excel file formats are unsupported, including Office Excel 2007 Macro-Enabled Workbook (.xlsm) and Office Excel 2007 97-2003 Workbook (.xls).

 Note   With appropriate permission, you can also open a workbook in Office Excel 2007 on your client computer from the Open menu on the Office Excel Web Access toolbar, either as a full workbook (read/write) or as a read-only snapshot. For more information, see Open a workbook or snapshot in Excel from Excel Services in Excel Web Access Help.

Supported features

The following features are supported when you load a workbook:

Feature Comments
Functions     All Excel worksheet functions are supported, with a few exceptions. For more information, see the following section, Supported and unsupported worksheet functions.
Dates     The Windows and Macintosh date systems.
Excel Tables     Excel Table data, column headers, calculated columns, total rows, structured references, and styles.
Cells     Cell values, including merged cells and cell content overflow.
Names     Defined names and named ranges.
Calculation     Calculation and recalculation settings, including automatic, automatic except tables, manual, and iterative calculation settings for ranges or entire worksheets. For more information, see Calculate and recalculate data in Excel Services in Excel Web Access Help.
Charts     Charts, chart ranges, and PivotChart reports. For more information, see Using charts and PivotChart reports in Excel Services in Excel Web Access Help.
Formatting     Cell and cell range formatting, conditional formatting (except by using data bars and icons) in workbooks, and number formats.
Connections     Connections to external data sources, including OLAP PivotTables.
What-If analysis     The results of What-if analysis tools, including Goal Seek, Data Tables, Scenarios, Solver, and Series.
Consolidation     Consolidated data in ranges.
Data sources     SQL Server 2000, Microsoft SQL Server 2005, OLEDB providers, and ODBC drivers.

Unsupported features

Workbooks that contain the following unsupported features will not load or display in Excel Services. For best results, always save a workbook from Office Excel 2007 by using the Excel Services command. (Click the Microsoft Office Button Button image, click the arrow next to Publish, and then click Excel Services under Distribute the document to other people.) You can confirm whether a feature is supported by clicking the Open this workbook in my browser after I save check box in the Save for Excel Services dialog box to attempt to display it in the browser. If a feature is not supported, Excel Services displays an alert.

The following features are not supported and prevent you from loading a workbook:

Feature Comments
VBA    

Visual Basic for Applications (VBA) code, macros, add-ins, and user defined functions (UDFs).

 Note    A programmer can customize Excel Services in many ways, including the creation of a user-defined function (UDF). For more information, see the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK).

Legacy macro languages     Microsoft Excel 4.0 Macro Functions and Microsoft 5.0 dialog sheets.
Controls     Form toolbar controls, Toolbox controls, and all ActiveX controls.
XML     XML maps and embedded smart tags.
Security and privacy    

Workbooks, worksheets, or ranges with protection, and workbooks that have Information Rights Management (IRM).

 Note   To protect workbooks in Excel Services, use Microsoft Windows SharePoint Services rights and permissions.

Images and objects      Linked or embedded objects or images, inserted pictures, AutoShapes, WordArt, and diagrams, such as organization charts.
Ink     All ink features including drawing, writing, and annotations.
OLE and DDE      Object Linking and Embedding (OLE) objects and Dynamic Data Exchange (DDE) links.
Displayed formulas     Workbooks saved with formulas that are displayed.
Data validation     Preventing invalid data entry and creating drop-down lists.
Data sources      Data retrieval services for Microsoft Business Solutions, Windows SharePoint Services lists, Microsoft SQL Server, external data ranges (also called query tables), and tables linked to Windows SharePoint Services lists.
Queries     Web queries and text queries.
External references to linked workbooks     Creating external references (also called links) to a specific cell range, to a defined name for the specific cell range, or as part of a name definition.
Comments     Display and adjustment of comments.
Consolidation     Consolidated data in PivotTable reports.
Shared workbooks     Sharing of workbooks and resolving conflicting changes.
Digital signatures     Visible and invisible digital signatures in a workbook.
Attached toolbars     Custom toolbars attached to the workbook by using Office Excel 2003 before the workbook was converted to Excel 2007.

Top of Page Top of Page

Supported and unsupported worksheet functions

All functions are fully supported when you load and recalculate a workbook with the following exceptions, HYPERLINK, RTD, and SQL.Request, each of which has limited support.

If the Function is: Then Excel returns: And Excel Services returns:
HYPERLINK     An active hyperlink that you can click and follow.

One of the following:

  • An active hyperlink that you can click and follow, if the Office Excel Web Access  All Workbook Interactivity and Workbook Navigation properties are set.
  • An active hyperlink to another Web page or document that you can click and follow, but not to a location within the workbook, if the Office Excel Web Access  All Workbook Interactivity property is set and the Workbook Navigation property is not set.
  • An inactive hyperlink text string that you cannot follow, if the Office Excel Web Access  All Workbook Interactivity and Workbook Navigation properties are not set.
RTD     Real-time data from a program that supports COM automation.

The following:

  • Any values returned by the RTD function that are currently stored in the workbook are displayed.
  • If there are no values currently returned, then a #N/A error is returned.
  • If Excel Services recalculates the workbook and attempts to run the RTD function or SQL.Request function, a #N/A error is returned.

If you want, you can use the ISERROR or IFERROR functions to test for the return value.

SQL.Request     The results of a query that is connected to an external data source.

The following:

  • Any values returned by the SQL.Request function that are currently stored in the workbook are displayed.
  • If there are no values currently returned, then a #N/A error is returned.
  • If Excel Services recalculates the workbook and attempts to run the RTD function or SQL.Request function, a #N/A error is returned.

If you want, you can use the ISERROR or IFERROR functions to test for the return value.

CHAR     A character specified by a number, and a block character for a nonprinting character. A character specified by a number, and a blank value for a nonprinting character.
CELL     Information about the formatting, location, or contents of the upper-left cell in a reference. A #VALUE! error.
INFO     The path of the current directory or folder on your client computer. A #VALUE! error.

The following volatile functions may return different values when they are calculated in Excel Services on a server computer than when they are calculated in Excel on a client computer.

If the Function is: Then Excel returns: And Excel Services returns:
NOW     The date and time on your client computer. The date and time on the server computer.
TODAY     The date on your client computer. The date on the server computer.
RAND, RANDBETWEEN     A random and therefore different number each time it is run. A random and therefore different number each time it is run.

Top of Page Top of Page

Supported and unsupported features when viewing a workbook

Viewing a workbook in Excel Services on a server computer is very similar to viewing a workbook in Excel on a client computer, but there are differences. Note that whether these features are supported or unsupported, they do not prevent the workbook from loading, and these features are preserved in the workbook so that they continue to work as expected in Excel.

Supported features

The following features are supported but may display differently on a server:

Feature Comments
Hyperlinks    

Supported, but controlled by Office Excel Web Access properties in the following way:

  • An active hyperlink that you can click and follow, if the Office Excel Web Access  All Workbook Interactivity and Workbook Navigation properties are set.
  • An active hyperlink to another Web page or document that you can click and follow, but not a location within the workbook, if the Office Excel Web Access  All Workbook Interactivity property is set and the Workbook Navigation property is not set.
  • An inactive hyperlink text string that you cannot follow, if the Office Excel Web Access  All Workbook Interactivity and Workbook Navigation properties are not set.
Fonts     Fonts are usually the same style and size on the server as they are on the client, but if a specific font is not available on the server, then a substitute font may be used. Also, a user can ignore font styles and sizes in a browser, such as Internet Explorer.
Charts and PivotChart reports      Charts and PivotChart reports are static images and refresh and redisplay if you interact (filtering, sorting, and so on) with the data that the chart is based on or with the data in the associated PivotTable report. For more information, see Using charts and PivotChart reports in Excel Services in Excel Web Access Help.
Line borders     

The following line borders are fully supported: all line colors; continuous and double line styles; solid, diamond, and dashed lines; and thin, medium, and thick line weights.

 Note   The following line borders are partially supported: triple and double line styles; and solid, diamond, square dotted, dash-short-dash, long-short-dash, and dash-short-dash-short-dash lines.

Color gradient directions     Horizontal and vertical color gradient directions.
Cell fill color      All fill colors.
Cell alignment     All cell alignments are supported with the following exceptions: vertical justify and vertical distributed, which are both replaced by vertical center.
Text rotation     Text rotation is supported along with cell and column header content overflow, horizontal or vertical left alignment for positive rotation (+) and horizontal or vertical right alignment for a negative rotation (-).
Bidirectional text     Fully supported when a set of characters from one language is displayed.
AS conditional formatting      Microsoft SQL Server Analysis Services (AS) conditional formatting is limited to the following: font color; fill color; font flags, such as bold, italics, underline, and strikethrough; and format strings, such as number formats.
Worksheet scrolling     The number of rows and columns that you can scroll on a worksheet is limited to a maximum size of 500. You use navigation buttons to display the next set of rows and columns beyond the current limit. For more information, see Navigate a workbook in Excel Services in Excel Web Access Help.

Unsupported features

The following features are not supported:

Feature Comments
Hyperlinks in charts     Clicking and following hyperlinks in charts.
Tables     Replacement of worksheet column headers by Excel table headers when scrolling headers out of view in a scrolling region.
Cell fill patterns     All fill patterns.
Color gradient directions     Diagonal up, diagonal down, and corner-to-center color gradient directions.
Text rotation     The cell fill or pattern is not rotated with the text, diagonal borders display as though the text was not rotated, and horizontal or vertical alignment other than left alignment for positive rotation (+) and right alignment for a negative rotation (-).
Bidirectional text     Mixing right-to-left and left-to-right bidirectional text characters that have a different glyph (or character shape) orientation, such as ( (Left parenthesis) and ) (Right parenthesis), is not supported in vertical text.
Line borders      The following line borders are partially unsupported: triple and double line styles; and solid, diamond, square dotted, dash-short-dash, long-short-dash, and dash-short-dash-short-dash lines.
Charts    

2007 Office release 3-D graphic effects, such as shadow, glow, warp, bevel, soft edges, recolor, and reflection. These effects are either removed or converted to an alternative effect.

The following 3-D charts are not supported:

  • 3-D surface
  • Wireframe 3-D surface
  • Contour surface
  • Wireframe contour surface

Rich text in an object, such as bullets and varying fonts or font sizes, which is converted to plain text.

Vertical text alignment.

 Note   Although embedded charts on worksheets and original charts on chart sheets are supported for display in Excel Services, an embedded chart that a workbook author has copied or moved from a worksheet to a chart sheet is not supported for display.

Row and column headers     The following formatting in row and column headers: double accounting, double underline, superscript, and subscript.
Nonprinting characters     Text with a 7-bit ASCII (a subset of the ANSI character set) value of 0 through 32, and any characters not supported by Extensible Markup Language (XML) version 1.0.
Print     Page layout and page headers and footers.
Tooltips     Tooltips of Microsoft SQL Server Analysis Services member properties.
XML     XML expansion packs.

Top of Page Top of Page

Supported and unsupported features when interacting with a workbook

When you load a workbook into Excel Services, you can interact with it in a number of ways, but there are some interactions that are not supported.

 Note   Another way to interact that is unique to Excel Services is to create and to change parameter, which temporarily change cell values in the workbook either by using the Parameter Task Pane in Excel Services or by passing data to the Excel Web Access Web Part from a connected Web Part, such as a Filter Web Part. For more information, see Change workbook parameters in Excel Services in Excel Web Access Help.

Supported features

The following features are supported but may behave differently.

Feature Comments
PivotTable reports      Report filtering, member selection, expanding (drilling down) and collapsing (drilling up) levels of data, sorting, filtering, and showing and hiding subtotals.
Find     Button image Finding text, numbers, and dates by a case-sensitive match, and finding by the partial contents of a cell.
Simple selection     Selecting a single cell, row, or column.
Filtering and sorting     Using the Filter menu, filtering by text, numbers, dates and times, specifying multiple criteria, and sorting. For more information, see Filter data in Excel Services and Sort data in Excel Services in Excel Web Access Help.
Outlining     Outlining, including showing and hiding details. For more information, see Outline data in Excel Services in Excel Web Access Help.
Scenarios     Executing a predefined scenario.
Refresh     Refreshing external data sources, including all data sources in the current workbook, a specific data source, periodic refresh, manual refresh, and refresh upon loading the workbook. For more information, see Refresh external data in Excel Services in Excel Web Access Help.
Calculation    

Automatic and manual Calculation. For more information, see Calculate and recalculate data in Excel Services in Excel Web Access Help.

 Note   Although Excel Services supports loading a workbook that contains circular references, the detection of circular references when it loads or recalculates a workbook behaves differently. If Excel Services cannot resolve a circular reference, under certain circumstances it displays a warning message that there is a circular reference. The values that are calculated are the same as the values that you would get if you cancel the operation on the Excel client. In effect, Excel Services automatically cancels the circular reference to prevent the calculation from degrading the server performance.

Unsupported features

The following features are not supported.

Feature Comments
PivotTable reports      The Detail Group and Show Detail options (also called drill-through) for OLAP data, member search, Microsoft SQL Server Analysis Services actions, or using the field list to add, rearrange, or remove fields.
PivotChart reports      Interacting directly with a PivotChart (because it is a static image).
Asynchronous evaluation of Cube functions     Asynchronous retrieval of data when a Cube function evaluates and the display of the #GETTINGDATA message before all data is retrieved. All data is retrieved synchronously before the view is displayed or redisplayed.
Find     Button image Finding by the underlying data (as opposed to the formatted data and by a case-sensitive match).
Replace     Replacing text and values after using Find Button image.
Row and column resizing     Row and column width and height adjustments.
Advanced selection     Selecting an adjacent and nonadjacent cell range or the entire worksheet.
Calculation     Changing the calculation setting of the workbook after it is loaded. For more information, see Calculate and recalculate data in Excel Services in Excel Web Access Help.
Filtering and sorting     Sorting and filtering by color, and saving a filter or sort to the workbook in Excel Services. For more information, see Filter data in Excel Services and Sort data in Excel Services in Excel Web Access Help.
Go To      All Go To operations.
Managing panes     Split and Freeze panes.
Zoom     Adjusting the view of a worksheet by a percentage of the actual size.

Top of Page Top of Page

 
 
Applies to:
Excel 2007