Udostępnij za pośrednictwem

  • Artykuł

Migration considerations for Excel 2007

Updated: March 8, 2007

Applies To: Office Resource Kit

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

Microsoft Office Excel 2007 provides users with many more robust, advanced features. Before a migration to Office Excel 2007, users should learn more about these differences to determine how the differences might affect the scope and pace of migration. The most significant functional differences and corresponding behaviors are summarized below.

The Microsoft Office Migration Planning Manager (OMPM) helps with migration and migration planning. OMPM contains file scanning and conversion tools to help organizations take inventory of their documents, analyze the documents for readiness with the 2007 Microsoft Office system, and provide notification regarding compatibility issues that may affect migration. For more information about OMPM, see Assessing your environment for the 2007 Office system.

Changes in Office Excel 2007

The three fundamental differences between earlier versions of Office Excel and Office Excel 2007 include:

  • New Open XML Formats.

  • Significantly expanded grid.

  • Results-oriented user interface that provides many single-click commands.

New file formats

Microsoft Office Word 2007, Microsoft Office PowerPoint 2007, and Office Excel 2007 use the new Open XML Formats. The Open XML Formats are compact and robust file formats that enable better data integration between documents and back-end systems and that are distinct from the binary-based file formats of previous versions of Microsoft Office.

The Open XML Formats is an open standard that was developed by Ecma International in collaboration with many technology vendors. The standard maximizes interoperability in a heterogeneous environment and enables technology providers to integrate files that are created in the 2007 Office system into their solutions.

After installation of the 2007 Office system, users can continue to open, edit, and save workbooks that were generated in the earlier binary file format. These workbooks can be converted to the Open XML Formats. This enables better interoperability among applications from different vendors and makes the Open XML Formats a better long-term solution. Users click the Microsoft Office Button and click Convert to convert a workbook from an earlier format to the Open XML Formats. See File format reference for the 2007 Office system for more information about the file formats.

Warning

Users should consider existing links between workbooks before your organization converts existing workbooks to the Open XML Formats. Because earlier versions of Office Excel cannot update links to workbooks that are saved in the Open XML Formats, all linked workbooks should be simultaneously converted.

Larger grid size

Users can develop more elaborate and detailed workbooks with the larger grid that is available in Office Excel 2007. The larger grid allows more than 16,000 columns and 1 million rows. However, the larger grid is incompatible with earlier versions of Office Excel. Data that is entered into cells that are outside the previous grid boundaries (A1:IV65536) is permanently deleted when the workbook is saved as an Office Excel 97-2003 workbook. All formulas that reference cells that are outside the previous grid boundaries are also compromised.

Users of Office Excel 2007 who plan to share workbooks with users who work with an earlier version of Office Excel should not enter data that is outside the grid boundary of the earlier version. As an alternative, users can use Compatibility Mode, which helps to mitigate these issues by regulating the larger grid size to match the grid size of earlier versions of Office Excel.

New user interface

Office Excel 2007 takes advantage of the new Microsoft Office Fluent user interface to offer powerful productivity tools that are easily accessible. Most menus, toolbars, and task panes are replaced by the Office Fluent Ribbon, which organizes commands by object or scenario and groups the commands by tabs. The Office Fluent Ribbon provides access to more features, with fewer steps. After migration to Office Excel 2007, the new user interface is available even when a user works with workbooks that were created in earlier versions of Office Excel 2007.

Opening Excel 97–2003 workbooks in Office Excel 2007

Most of the features that are available in earlier versions of Office Excel still exist when a workbook is opened in Office Excel 2007. There may be times when workbooks perform differently than what users expect, either because a feature that is employed in the workbook is removed or changed, or because the formula is associated with the file format or the specific version of Office Excel that is used to create the workbook.

This topic discusses features in the following areas in earlier versions of Office Excel that are either not available or are significantly changed in Office Excel 2007:

  • Compatibility Tools

  • Format Changes

  • Visualization and Design

  • Lists and PivotTables

  • References and Names

Compatibility Tools

Office Excel 2007 contains compatibility support for the most common features that were used in previous versions of Office Excel. Some functions that were seldom used in previous versions of Office Excel are deprecated or eliminated. Most of the features that users rely on regularly are still available in Office Excel 2007. Office Excel 2007 also contains compatibility safeguards to help prevent the loss of data when users move between different versions of Office Excel.

Compatibility Mode

Compatibility Mode is tied to the Office Excel 97-2003 file format, which disables new features in Office Excel 2007 that are not compatible with earlier versions of Office Excel. For example, when a workbook that is opened in Office Excel 2007 is running in Compatibility Mode, the larger grid size is suppressed so that users cannot enter data, formulas, or references that are outside the smaller grid boundary of earlier versions of Office Excel.

Users that open a workbook that is saved in the Office Excel 97-2003 file format (.xls) see that the workbook automatically functions in Compatibility Mode. Similarly, when users work with a new workbook that will be used in earlier versions of Office Excel, they can turn on Compatibility Mode (by saving the file in the Office Excel 97-2003 file format, and then closing and re-opening the file) to prevent the accidental use of functions and features that are incompatible with the earlier versions of Office Excel. This option is critical for users who plan to share workbooks with other users who have not yet migrated to Office Excel 2007.

When users work in Compatibility Mode, they are still able to work with the Office Fluent user interface and to have access to most of the new features. Only features that are incompatible—such as the larger grid size—are turned off for that workbook. Similarly, when a user saves a new workbook to the Office Excel 97–2003 file format, that workbook is automatically placed into Compatibility Mode the next time the workbook is opened. To exit Compatibility Mode, users convert the workbook to one of the new file formats and then re-open the workbook. Users can accomplish this action in one step by using the Convert command: users click the Microsoft Office Button and click Convert.

Compatibility Checker

The Compatibility Checker functions automatically by default when users save a workbook to the Office Excel 97-2003 file format. It scans for features and characteristics that are not supported by earlier versions of Office Excel. Users also have the option to disable the Compatibility Checker so that it does not run automatically when the workbook is saved to an earlier file format.

The Compatibility Checker identifies two types of compatibility issues: features that are retained but that function differently in earlier versions of Office Excel, and functions that are disabled in Compatibility Mode and that are not functional in earlier versions of Office Excel. These issues display in a dialog box that enables the user to respond and take action before data or functions are lost. The Compatibility Checker lists issues that are identified in the workbook, summarizes the number of occurrences of each issue, identifies tools to help locate the issues, and provides additional help for the types of issues that are identified.

Format changes

The Open XML Formats in Office Excel 2007 accurately mirror and represent the binary data formats of earlier versions of Office Excel. This means that users can still read and modify most workbooks that were created in earlier versions of Office Excel in Office Excel 2007.

The more robust Open XML Formats accommodate more file types. Some file types are no longer in common use. Consequently, changes are made to the names and extensions as follows:

  • Support is removed for some legacy file types.

  • The names of Office Excel 97-2003 file formats are changed to help users avoid confusion.

  • The new Office Excel 2007 file types are assigned new names and new file name extensions.

New and renamed file names and file name extensions

New file name extensions are assigned to the new file formats that are available in Office Excel 2007. These new naming conventions help to avoid confusion when users work between versions of Office Excel. The new file name options are listed in the following table and can be viewed in the Save As dialog box.

File type File name extension

Office Excel 2007 Workbook

.xlsx

Office Excel 2007 Macro-Enabled Workbook

.xlsm

Office Excel 2007 Binary Workbook

.xlsb

Office Excel 2007 Template

.xltx

Office Excel 2007 Macro-Enabled Template

.xltm

Office Excel 2007 Add-In

.xlam

Support is removed for some file formats

A number of the previous file formats are seldom, if ever, used. Support for these formats is removed from Office Excel 2007. Users can no longer open or save workbooks based on the following file formats after a migration to Office Excel 2007:

  • WK1 (1-2-3)

  • WK4 (1-2-3)

  • WJ3 (1-2-3 Japanese) (.wj3)

  • WKS (1-2-3)

  • WK3 (1-2-3)

  • WK1, FMT (1-2-3)

  • WJ2 (1-2-3 Japanese) (.wj2)

  • WJ3, FJ3 (1-2-3 Japanese) (.wj3)

  • DBF 2 (dBASE II)

  • WQ1 (Quattro Pro/DOS)

  • WK3, FM3 (1-2-3)

  • Microsoft Excel Chart (.xlc)

  • WK1, ALL(1-2-3)

  • WJ1 (1-2-3 Japanese) (.wj1)

  • WKS (Works Japanese) (.wks)

HTML file format for publishing only

Due to limited use as a primary file format, Office Excel 2007 no longer stores Excel-only feature information in HTML file formats. The Save as HTML command is mainly used as a publishing format, and Microsoft continues to support the command as a way to create a document that is viewed in a Web browser. HTML files can still be opened in Office Excel 2007. Office Excel-specific features that are contained in the files and that were created in an earlier version of Office Excel are preserved. Users should save these files as a primary version of the document in one of the new file formats. Users should publish the files to HTML.

Microsoft Script Editor

Integration with Microsoft Script Editor is removed from Office Word 2007, Office PowerPoint 2007, and Office Excel 2007 as part of the decision to move away from supporting HTML as a full-fidelity file format. This change means that script debugging components are no longer installed by default in Office Excel 2007. Microsoft Script Editor can still be installed with the Microsoft Office suites so that it can run as a standalone program to edit HTML files.

Visualization and design

2007 Office system provides users with many improvements in visualization and design tools. The biggest differences in Office Excel 2007 are in the following areas:

  • AutoFormat and Style galleries

  • Charting

  • Shapes

AutoFormat

Office Excel 2007 includes galleries and functions that are now called Table Style and PivotTable Style. These are significant improvements over the AutoFormat feature that was available in earlier versions of Office Excel. The AutoFormat feature is not included in the Office Fluent Ribbon. It can be added to the Quick Access Toolbar, which is an icon-driven tool set that appears above the Office Fluent Ribbon and that users can customize.

Charting

Charting is now part of a shared Microsoft Office drawing layer with the 2007 Office system. This means that some of the charting features that are specific to earlier versions of Office Excel are not available in Office Excel 2007. In most cases, these features are replaced with more robust capabilities.

Although some charting features are removed or changed, Office Excel 2007 users still have the ability to view charts the way they were created in earlier versions of Office Excel. Issues may arise if users attempt to make changes to charting features that are no longer available in Office Excel 2007. The specific charting features that are not available in Office Excel 2007 and ways to work around these differences are listed below.

  • **Resize multiple charts.**Users no longer change the chart type of multiple charts simultaneously. Each chart is now changed individually.

  • Press F11 to duplicate a populated chart sheet. Users no longer press F11 on a populated chart sheet to create a chart sheet with the same data. This action produces a blank chart sheet in Office Excel 2007.

  • **Direct rotation of 3-D charts.**The mouse is used to directly manipulate the 3-D view of the chart in earlier versions of Office Excel. This is accomplished by using the 3-D Rotation dialog box in Office Excel 2007.

  • **Pattern fills.**Pattern fills for shape objects are removed in favor of picture and texture fills. Existing files appear the same when they are loaded. The ability to create new shapes with the previous pattern fills is removed as part of the new drawing capabilities.

  • **Size with window.**Earlier versions of Office Excel have a Size with Window command that automatically resizes charts on chart sheets when the window size changes. This feature is removed. The Zoom to Selection command is used to achieve similar results.

  • **Automatic text box creation when typing.**A text box is no longer automatically created when a user types on a selected chart. A user can still insert text boxes by using the Insert Text Box command.

  • **Default paste behavior in Word and PowerPoint.**The default choice for copying and pasting a chart from Office Excel 2007 to either Office Word or the Office PowerPoint presentation graphics program is changed to linked. This setting can change to picture or entire workbook through the Paste Options menu, which immediately displays after the user pastes the chart.

  • Word table as a data source. Unlike in earlier versions of Office Word, a user does not use a table as data for a new chart in Office Word 2007. The user sees sample data rather than data in the selected Office Word table. The user copies and pastes the real data into the new chart to replace the sample data.

  • Printed chart size. The Printed Chart Size option is removed from the Chart tab in the Page Setup dialog box. The behavior matches the Custom setting from Office Excel 2003 in Office Excel 2007.

  • Drag data onto a chart. Users add data to a chart by selecting the data and dragging it onto the chart in earlier versions of Office Excel. This feature is no longer available in Office Excel 2007. Office Excel 2007 still supports the other, more common methods of adding data to a chart.

  • Direct manipulation of data points on charts. The user can drag data points on a chart in earlier versions of Office Excel, which changes the data source values on the worksheet. This seldom-used feature is removed from Office Excel 2007.

Shapes

All programs in the 2007 Office system feature a new version of shapes, with new formatting capabilities, an upgraded user interface, integration with new Microsoft Office 2007 styles and themes, and compatibility with other features that do not exist in earlier versions of OfficeArt. With the 2007 Office system, the name OfficeArt is changed to SmartArt.

Most of the shapes within that workbook automatically convert to the newer version of shapes when an Excel 97-2003 workbook opens in Office Excel 2007. This enables users to take advantage of features that are available in the SmartArt Shape galleries in Office Excel 2007.

There are a few exceptions to the automatic upgrade, and in these instances OfficeArt Shapes remain whole and usable in their previous format. These features include: comments, forms controls (including dialog sheet backgrounds), Microsoft ActiveX objects, shapes used internally by Office Excel 2007 for various features (such as filter drop-down lists, auditing and circular reference arrows, and data validation ovals), OLE objects, camera tool objects, ink annotations, header pictures, organization charts, and legacy diagrams.

It is important to note that non-upgraded shapes that are drawn in earlier versions of Office Excel cannot be grouped with shapes that are drawn in Office Excel 2007 or with shapes that are upgraded. Similarly, users cannot select upgraded and non-upgraded charts at the same time. As a result, object types are layered when users mix object types. The earlier versions of shapes are drawn on top of all later versions of shapes. The new version of charts cannot be shown on top of the previous version of dialog sheets.

Although the charts still exist, users cannot view charts that are layered underneath the dialog sheets. Users must access the new shapes by using the Select Objects command. Users must use the Select Multiple Objects command in the Customize window to select shapes from an earlier version of Office Excel.

Lists and PivotTables

Changes in Office Excel 2007 affect the following aspects of lists and PivotTables:

  • AutoFilter.

  • AutoFilterMode property.

  • Lists are now called tables.

  • Adding new records to tables.

  • Table names.

  • Office SharePoint Server lists and write-back to Office SharePoint Server 2007.

  • PivotTables.

AutoFilter

Filtering is used to display a subset of the data or a portion of the workbook and to hide the rest of the data or workbook from view. This feature is modified in Office Excel 2007 to enable easy access to the most common filtering tasks: filtering by more than two conditions and filtering by format.

AutoFilterMode property

In Office Excel 2003, users could create macros to check the AutoFilterMode property when the selection was in a list (referred to as a table in Office Excel 2007) to determine if autofiltering was turned on in that list. In Office Excel 2007, the AutoFilterMode property works with worksheet autofilters and not with autofilters that are part of a table. This is due to properties of Office Excel 2007 that give each table its own AutoFilter object, which in turn enables multiple autofilters per worksheet through the use of tables.

The AutoFilterMode property may not work correctly when users open an Office Excel 2003 workbook that contains macros that are used to check the AutoFilterMode property of a list (table). This does not affect documents or macros that are created in versions of Office Excel before Office Excel 2003.

To correct this issue, users enable the AutoFilter task from the List object, instead of at the macro level.

Lists are now called tables

The list feature was introduced in Office Excel 2003 as a way for users to easily track lists of data, such as contacts and orders. It is also the mechanism by which lists or data from an Office SharePoint Server site can display in Office Excel 2003. In Office Excel 2007, the name of this feature is changed to Table to match this feature in other Microsoft Office programs, such as Office Word and Office PowerPoint.

Other changes to this feature make it a more robust organizational tool. When users work with workbooks that contain lists that were created in earlier versions of Office Excel, users will discover behavioral differences in the table features of Office Excel 2007. These differences are described below.

Adding new records to a table

A special row that is used to add new records to a list appears at the bottom of a list in Office Excel 2003. Although this row is removed from tables in Office Excel 2007, it is extremely easy to add new records. In most cases, typing just below a table triggers Office Excel to add that row to the table. The user may need to use the Insert Row command in the Table menu when there is already data or another object just below the table.

Table name

When users create a table, they also create a defined name for the same range in Office Excel 2007. This name is used to reference the table in formulas that use the new structured referencing feature.

Names used for PivotTables and tables in earlier versions of Office Excel may not meet the requirements for range names in Office Excel 2007. Users must change the names of these objects when the objects open in Office Excel 2007. This ensures that the objects are appropriate for use in formulas and elsewhere.

The type of table names that are used for referencing are built on top of the existing Defined Name feature in Office Excel. Consequently, the table names inherit the same restrictions that defined names have, such as containing no spaces and having no support for certain characters. The table name that was previously only available through the object model does not share these same restrictions, so the two functions are separate.

Office SharePoint Server 2007 lists and write-back

Office Excel 2003 supports a user's ability to connect to (read) and update (write) lists that reside on an Office SharePoint Server 2007 site. Lists that were created in Office Excel 2003 and that connect to an Office SharePoint Server 2007 site continue to function as they always have, even when the lists are opened in Office Excel 2007. Users can continue to update the Office SharePoint Server 2007 site. However, the update feature is removed from Office Excel 2007. This means that new tables that connect to an Office SharePoint Server 2007 site do not support the ability to write back.

Users can still publish a table to an Office SharePoint Server 2007 site through a one-time write option. Links to the list are read-only after they are published. Users can update the Office SharePoint Server 2007 list by refreshing the table data in Office Excel, but users cannot update the data directly on the Office SharePoint Server 2007 site.

In addition to these new behaviors, read/write connections to an Office SharePoint Server 2007 site are converted to a read-only connection when the workbook is saved to the new Office Excel 2007 file format. Users receive an alert message during the Save function. The message lets them cancel out of the operation and process uncommitted changes.

Read-only connections to an Office SharePoint Server 2007 site behave the same as other external data queries. For example, users no longer view the ID column. The column only appears if it is part of the view for that list. Users can delete the ID column or any other column. Users can also insert columns in the middle or add them to the end. This enables users to build calculated columns by using existing columns. Users can also rename headers, change data values for ad-hoc analysis, insert or delete worksheet rows, and apply custom data validation rules.

These exercises do not affect data on the Office SharePoint Server 2007 site, because users cannot save these modifications or update the Office SharePoint Server 2007 site. In other words, the experience of working with a table that is connected to an Office SharePoint Server 2007 site should be as comfortable as working with a normal workbook that operates in read-only mode.

PivotTables

Many changes are made to the formulas and architecture of PivotTable dynamic views in Office Excel 2007. These changes enable users to do more with PivotTables, but the changes may also cause some issues when users migrate from earlier versions of Office Excel.

To ensure that PivotTables remain whole and functional, the version property of PivotTables is completely separate from the version property of Office Excel. PivotTables in Office Excel 2007 are version 12 (xlPivotTableVersion12). PivotTables that are created in earlier versions of Office Excel have a different version number. For example, version 10 PivotTables (xlPivotTableVersion10) are created in either Office Excel 2002 or Office Excel 2003.

When users work in Office Excel 2007, the version of the PivotTable is determined by whether the user is working in Compatibility Mode. New PivotTables that are created in that workbook are created in version 10 if the current workbook is in Compatibility Mode. New PivotTables are created in version 12 when the current workbook is not in Compatibility Mode.

When the user saves a workbook from Compatibility Mode to one of the new file formats, the PivotTables in that workbook are marked for upgrade. When each PivotTable is refreshed, it is upgraded to version 12 and new functionality is enabled for that PivotTable.

It is important to note that version 12 PivotTables cannot be downgraded to version 10, even when the workbook is saved by using a previous file format. This means that a version 12 PivotTable that is created in Office Excel 2007 cannot be modified or refreshed when it is opened in an earlier version of Office Excel. However, the PivotTable maintains all functionality and can be modified or refreshed when it is re-opened in Office Excel 2007.

If the user plans to share PivotTables with other users who are working in a previous version of Office Excel and those users need to refresh the PivotTables, it is important to make sure that the PivotTables are created as version 10 PivotTables. The simplest way to do this is by using Compatibility Mode.

In addition to these versioning differences, other functional changes affect the behavior of PivotTables. The following features are either removed or are significantly deprecated in Office Excel 2007 due to a lack of interest from users. In some cases, these functions are replaced by more robust PivotTable tools.

Calculated members

Calculated members that are defined in Microsoft Online Analytical Processing (OLAP) cubes do not display by default in version 12 PivotTables. This does not apply to calculated measures (calculated members in the Measures dimension). These always appear. Calculated members appear by default in Office Excel 2003. However, this feature can be controlled in the object model (PivotTable.ViewCalculatedMembers) in both Office Excel 2003 and Office Excel 2007. This setting is also added to the Display tab in the PivotTable Options dialog box in Office Excel 2007 so that it is easy to make calculated members appear when they are needed.

Filtering with SQL Server 2005 Analysis Services

The new filtering features in OLAP PivotTables require support for sub-selects on the OLAP server. Microsoft SQL Server 2005 Analysis Services supports sub-selects, so that all of the new filtering options are available. Earlier versions of SQL Server Analysis Services do not support sub-selects. When users work with earlier versions, only the filtering features that are available in Office Excel 2003 PivotTables are available in Office Excel 2007 PivotTables.

OLAP Cube Wizard

The OLAP Cube Wizard enables the user to create OLAP cube files from relational data sources and add hierarchical data organization to relational data in earlier versions of Office Excel. The relational data can be viewed in PivotTables and the data can be stored in a separate file. The OLAP Cube Wizard is removed from Office Excel 2007 because this feature was seldom used. Users can still create PivotTables based on relational data by using the more common methods of directly connecting to the relational data or by importing the data into an Office Excel workbook.

PivotTable Wizard

The PivotTable Wizard is no longer the primary user interface for creating PivotTables in Office Excel 2007. Users can access a new, simpler one-step dialog box to create PivotTables for most purposes. Users can add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. The following features are only available through the wizard:

  • Server-defined page fields.

  • Option to optimize memory.

  • Ability to explicitly create a PivotTable based on another PivotTable.

  • Multiple consolidation ranges.

Tracking customizations

Version 12 OLAP PivotTables track customizations of items, even when those items are temporarily not visible in the PivotTable. This is true for formatting that is applied to items and to customized item labels. This is an improvement over Office Excel 2003, where custom labels and formatting were lost when the parent field collapsed. Office Excel 2007 stores and applies the parent field information after the collapse-and-expand operations. Office Excel 2007 stores customized labels when the field is removed from the PivotTable so that the customized labels still appear when the field is added back to the PivotTable at a later time.

References and names

Though some features and functions are not altered in Office Excel 2007, users may experience formula issues when a workbook that was created in an earlier version of Office Excel is opened in Office Excel 2007. Cell references or names may become confused with the naming and reference conventions that were made possible by the larger grid. Users should be aware of the following possible issues in their existing workbooks.

Full row or column references

All full row and full column references automatically consider the new cells in the larger grid size of Office Excel 2007 when a workbook from an earlier version of Office Excel is converted to the Office Excel 2007 file format. This is because the reference =A:A refers to cells A1:A65536 in earlier versions of Office Excel, but that same reference points to cells A1:A1048536 in the Office Excel 2007 format.

Full row or column references are often used as a shortcut to capture all data in a range within that row or column. This can lead to issues when a user enters data that is not meant to be included in that reference further down the column or row. In addition, the results of functions that reference full rows or columns can change when the functions are converted to Office Excel 2007. Examples include COUNTBLANK, ROWS, and COLUMN functions, which count the number of cells, rows, or columns in the reference.

Names and column header labels

With the addition of over 16,000 columns, the column header labels in Office Excel 2007 now extend to XFD. Many names that could be defined in earlier versions of Office Excel (for example, USA1, FOO100, MGR4) are now valid cell references. Additionally, Office Excel 2007 now reserves names that begin with xl for internal use.

When incompatible names are found during the conversion to the Office Excel 2007 file format, the user is alerted to the conflict and an underscore (_) is automatically added to the beginning of all incompatible names to make the names unique. External workbook references and functions that take strings references, such as INDIRECT, are not updated when they are converted to the Office Excel 2007 file format. These must be changed manually.

Office Excel 2007 does not change defined names that are referenced through Microsoft Visual Basic for Applications (VBA) code. VBA code that references incompatible names does not work and must be updated by the user or an IT professional. This is accomplished by performing a find and replace action on the old name.

Opening Office Excel 2007 workbooks in earlier versions of Office Excel

It is reasonable to expect that after a migration to Office Excel 2007, users will continue to share workbooks with other users who are still working with earlier versions of Office Excel. There are two ways to facilitate file sharing across versions of Office Excel.

The workbook performs in the same way, whether the file is saved as an Office Excel 97-2003 workbook or whether the user opens the file with the Compatibility Pack. However, some of the features, functions, and data that are entered in an Office Excel 2007 workbook may not be visible or usable in earlier versions of Office Excel. Users can expect the following issues to arise when an Office Excel 2007 workbook is opened in earlier versions of Office Excel.

Conditional formatting

Office Excel 2007 users benefit from many improvements to conditional formatting, including new and more robust visualization tools, the Office Fluent Ribbon user interface, and new conditional formatting rules. An increase to the number of formatting parameters that can be used simultaneously and some added PivotTable and table functionality also improve the conditional formatting experience. When Office Excel 2007 workbooks are shared with other users who are working with earlier versions of Office Excel, it is important that users know how conditional formatting functions in the earlier versions.

Formatting is retained

In general, all of the conditional formatting that is created in an Office Excel 2007 workbook is retained when the formatting is saved to the earlier file format. Because conditional formatting is another property that is assigned to the cell, it is not affected when the user alters data, font, borders, and so on. If the user does not alter the conditional formatting, the user can open and save the file in earlier versions of Office Excel with no loss of the new conditional formatting.

Earlier versions of Office Excel cannot display the new data visualizations that are added, such as data bars, icon sets, and color scales. This does not mean the visualizations are lost. The visualizations remain available so that they can be viewed when the workbook is opened again in Office Excel 2007. But when the workbook is opened in the earlier versions, some of the visualizations are not visible and others may appear slightly different, because earlier versions of Office Excel may substitute a compatible visual quality when the new feature is not available. For example, more variations on the color blue are available in Office Excel 2007. When a file that references an unavailable blue is opened in an earlier version of Office Excel, the user still sees blue, but a different hue of blue.

Users can edit files that contain new conditional formatting with earlier versions of Office Excel. Users can change cell values, sort ranges, add formatting, and perform a number of other tasks without changing the conditional formatting. In general, if the user does not make changes directly to the conditional formatting on a range, the formatting safely reappears as it was designed when the workbook is opened again in Office Excel 2007.

Designing formats for use in multiple versions

Users who want to create workbooks to share across multiple versions of Office Excel and who want those workbooks to look the same no matter which version of Office Excel is used, should not use the new visualizations and new rules.

The following list of new conditional formatting features affects the appearance of workbooks that are opened in earlier versions of Office Excel. Users should avoid these new features in the following circumstances:

  • More than three conditions used for formatting. Earlier versions of Office Excel are limited to three conditions per cell. If more than three conditions are applied in an Office Excel 2007 workbook, only the first three conditions display when the workbook is opened in earlier versions of Office Excel.

  • Conditional formatting that overlaps. Users can define overlapping conditional formatting in Office Excel 2007, but earlier versions of Office Excel do not evaluate all of the rules and these cells may show different formatting.

  • New visualizations. Data bars, color scales, and icon sets cannot display in earlier versions of Office Excel.

  • Stop if True. Office Excel 2007 has a new conditional formatting option to process additional formatting rules, even when a previous condition is true. Earlier versions of Office Excel do not recognize this option, and stop after the first true condition.

  • Top 10 or Compare to Average. Office Excel 2007 can apply conditions to subsets of values. Earlier versions of Office Excel do not have these conditional formatting options The earlier versions calculate the condition across all values.

  • Non-contiguous formatting. Office Excel 2007 supports additional conditional formatting on ranges that are not adjacent to each other. This conditional formatting type is not supported in earlier versions of Office Excel.

Incompatible grid size

Data (including cell data, charts, and all other objects) that is located in cells that are outside the grid boundaries of earlier versions of Office Excel (A1:IV65536) is permanently removed from the workbook when it is opened in or is saved to Office Excel 2003 and earlier file formats. Consequently, when workbooks are shared between earlier versions of Office Excel and the new Office Excel 2007, Microsoft strongly recommends that the user does not enter data or create references that point to data in cells that are outside the grid boundaries of earlier versions of Office Excel, because those cells will not exist when they are opened in earlier versions. Compatibility Mode helps to prevent this problem by regulating the larger grid of Office Excel 2007 for use across earlier versions of Office Excel.

When a user saves a file that contains references to cells that are outside the A1:IV65536 range into an earlier file format, Office Excel 2007 provides the option to recalculate the workbook when it is opened again. The user can also be prompted with a recalculate cells alert. This recalculates all cell formulas and references based on actual data in the workbook. Cells with references that are outside the A1:IV65536 range change to #REF!. The user should take one of the following actions when the alert appears:

  • Respond yes to this alert to make sure that users who view the workbook see cell values that accurately reflect the data in the workbook. For example: a cell that referenced A100000 and displayed the value of that cell now shows #REF!, because cell A100000 no longer exists.

  • Respond no to this alert to send a snapshot of the values that appear in the A1:IV65536 range, even if some of the values are no longer valid because data was lost. For example, a cell that referenced A100000 and displayed the value of that cell still displays that value even though cell A100000 no longer exists, because Office Excel did not recalculate the cell reference.

Incompatible tables and lists

The table feature (formerly list) was first introduced with Office Excel 2003. The table data appears in the workbook when an Office Excel 2007 workbook is opened in a version of Office Excel that is earlier than Office Excel 2003, but none of the surrounding table functionality is available. The workbook, including the data inside the table, can be modified and the table should remain intact when the workbook is re-opened in Office Excel 2007. However, structural changes made to the workbook in versions of Office Excel that are earlier than Office Excel 2003, such as inserting or deleting cells and columns, may cause the table—but not the data—to be lost when the file is opened in Office Excel 2007.

External data queries

An external data query that is created in Office Excel 2007 still functions as an external query in earlier versions of Office Excel, but the table functionality is not present. The external data query can still be refreshed and changed without the table functionality. Further, the table functionality should reappear when the file is opened in Office Excel 2007, but some types of modification may cause the table to be lost. In those cases, the data and the external data functionality remain.

New security features

Office Excel 2007 includes the following new features to secure data:

  • Trust Center and the Message Bar.

  • Trusted Locations.

  • Disallowing empty macros.

  • Change in security levels.

  • Programmability enhancements.

  • Interaction between Office Excel 2007 and Internet Explorer.

Trust Center and the Message Bar

Trust Center is a new feature in the 2007 Office system that hosts all security settings for each program in a location that is common across all programs in the 2007 Office system. In addition to combining all security options in one central location, Trust Center includes the Message Bar, which replaces security prompts when an Excel workbook is opened. This Message Bar is similar to the pop-up blocker message that is used in the Windows Internet Explorer Internet browser.

All potentially dangerous content is blocked or disabled in the workbook without prompts by default. No security decisions are made when a workbook is opened. For example, documents with macros, ActiveX controls, and data links no longer prompt the user about the possible security concern. These features are disabled. However, users are notified when something is blocked, because the Message Bar appears in the program window. The user can click the bar to take action, including allowing the blocked content if the user’s Group Policy settings allow it. In the past, users had to make this decision before they were able to see the document and with access to little or no information about the spreadsheet.

This new security model enables users to read a document and edit content while the document is protected. Macros are only enabled if necessary and only after Trust Center and Group Policy have determined that the document is what the user is expecting.

Office Excel 2007 users should become familiar with these new security features. Macros, ActiveX controls, data links, and other robust features are commonly used in workbooks.

Trusted Locations

Trusted Locations is a new feature that provides additional management capabilities for IT administrators and enhanced security for users. Trusted Locations are defined folders from which documents that contain active content (macros, ActiveX controls) run without being subjected to further security review, such as the Message Bar. With the 2007 Office system, administrators can better manage the types of active content that can run and the conditions under which the active content is permitted to run. Group Policy can prohibit running macros from all other locations.

It is important to note that these Trusted Locations must be properly managed. Only documents that are known to be safe should be placed in Trusted Locations. Any document that is stored in the folders is completely trusted and does not display security warnings before the document performs potentially harmful actions, such as running macros or connecting to data.

Empty macros

Users could retain comments or declarations in Excel VBA—or macro—code in Office Excel 2003. Users can no longer save macro code that contains only comments and declaration statements in Office Excel 2007. The user must add a subroutine or function to the Excel VBA code to retain these features.

Change in security levels

Four levels of macro security settings are available in Office Excel 2003: low, medium, high, and very high. Users access Trust Center to find new macro security settings in the 2007 Office system. These new settings are more descriptive than the previous settings and are more flexible regarding security settings that are specific to Office Excel 2007.

Programmability issues

All documents that are saved in Open XML Formats are considered to be macro-free files and cannot contain code by default. This behavior ensures that malicious code that resides in a default document can never run unexpectedly. While documents can still contain and use macros in the 2007 Office system, users must save documents as a macro-enabled document type. This safeguard does not affect a developer’s ability to build solutions, but it does enable organizations to use documents with more confidence.

Macro-enabled files employ the same file format as macro-free files, but the files contain additional parts that macro-free files do not. The additional parts depend on the type of automation that exists in the document. A macro-enabled file that uses VBA contains a binary part that stores the VBA project. Any Office Excel 2007 workbook that utilizes macros that are created in previous versions of Office Excel or any Office PowerPoint presentation that contains action buttons are also saved as macro-enabled files. If a code-specific part exists in a macro-free file, whether it is placed there accidentally or maliciously, the programs in the 2007 Office system do not allow the code to run—without exception.

Users can now determine if code exists within a Microsoft Office document before the document opens. Previously, this could not be easily accomplished unless the file was opened in a Microsoft Office program. Users can now inspect the package file for the existence of code-based parts and relationships without running Microsoft Office programs and potentially risky code. If a file looks suspicious, users can remove the parts of the file that are capable of running code. This ensures that the code cannot cause harm.

Interaction between Office Excel 2007 and Internet Explorer

When a user navigated to a Microsoft Office document from within Internet Explorer in earlier versions of the Microsoft Office suites, the document opened with the program that was hosted inside the Internet Explorer program. Many developers used this functionality to create a more integrated interaction between their Web programs and the data that was produced by those programs, such as creating a workbook and then instructing Internet Explorer to navigate to that workbook. However, this caused confusion for users who wanted to work with documents from the Web (Internet or intranet), because the full Office Excel user interface was not available from within Internet Explorer.

This default behavior is changed in the 2007 Office system. The program does not open inside Internet Explorer. Instead, the appropriate Microsoft Office program launches and opens the document. This provides a more consistent experience; however, it may cause unexpected behavior in custom applications that use Internet Explorer. Although this is the default behavior, users can choose to revert to the earlier hosted behavior if that is what they prefer.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Office Resource Kit information.