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.

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 workbook or as a snapshot. For more information, see Open a workbook or snapshot in Excel from Excel Services.

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.

Charts   

Charts, chart ranges, and PivotChart reports. For more information, see Using charts and PivotChart reports in Excel Services.

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 from ranges.

Data sources   

SQL Server, OLAP providers, 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 the 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 of and adjustment of comments.

Consolidation   

Consolidated data from 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.

TODOTop 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 Servicesl 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.

Information about the formatting, location, or contents of the upper-left cell in a reference.

 Note   The following types of cell information are not supported and return a #VALUE! error: color, filename, format, parentheses, prefix, protect, and width.

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.

TODOTop 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.

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.

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 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.

TODOTop 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 change parameters, which temporarily changes cell values in the workbook either by using a Parameters Task Pane 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.

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.

Outlining   

Outlining, including showing and hiding details. For more information, see Outline data in Excel Services.

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.

Calculation   

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

 Note   Although Excel Services 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 once it is loaded. For more information, see Calculate and recalculate data in Excel Services.

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.

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.

TODOTop of Page

 
 
Applies to:
SharePoint Server 2007