Create a workbook for use with earlier versions of Excel
Note: This article has done its job, and will be retiring soon. To prevent "Page not found" woes, we're removing links we know about. If you've created links to this page, please remove them, and together we'll keep the web connected.
Some Excel for Mac 2011 features do not work or behave differently in earlier versions of Excel. If you work with workbooks that were created in an earlier version of Excel, or share workbooks with other people who use an earlier version of Excel, the Compatibility Report can help identify issues. Some issues can cause a significant loss of functionality or a minor loss of fidelity in an earlier version of Excel. To avoid the loss of data or functionality in an earlier version of Excel, you can then make the necessary changes to your Excel for Mac 2011 workbook.
When you save an Excel for Mac 2011 workbook to the Excel 97 through Excel 2004 file format, the Compatibility Report runs automatically. However, to verify that a workbook is compatible with Excel 2008, you must run the Compatibility Report manually the first time that you save the workbook. You can then specify that the Compatibility Report runs automatically every time that you save that workbook. The Compatibility Report lists the compatibility issues it finds, and provides a Help button for many issues. When the Compatibility Report is run automatically, it also specifies the version of Excel in which a potential compatibility issue occurs. For information about how to run the Compatibility Report, see Check a document for compatibility.
The following tables contain information about features that don't work or behave differently in earlier versions of Excel.
Worksheet features
| Feature | Behavior in earlier versions of Excel |
| Worksheet size larger than 256 columns by 65,536 rows | In Excel for Mac 2011 and Excel 2008, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of Excel 97 through Excel 2004 is limited to 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in Excel 97 through Excel 2004. |
| Scenarios with references to cells outside of the row and column limits of the selected file format | If a scenario in the worksheet refers to a cell outside the column and row limit of Excel 97 through Excel 2004 (256 columns by 65,536 rows), the scenario won't work if you save the workbook in the Excel 97 through Excel 2004 file format. In Scenario Manager, look for the scenario that contains a reference to cells that are outside the row and column limit of the earlier version of Excel, and then change the reference to a location within that limit. On the Data tab, under Analysis, click What If, and then click Scenario Manager. In the Scenarios box, locate the scenario that causes the compatibility issue, and then edit its reference. |
| Dates in a calendar format | In Excel for Mac 2011 and Excel 2008, you can create custom international calendar formats that won't work in Excel 97 through Excel 2004. To avoid loss of functionality, you should change the calendar format to a language (or locale) that work in Excel 97 through Excel 2004. |
| Workbook contains more cells with data than are allowed in earlier versions of Excel | In Excel 2008, the total number of available cell blocks is limited by the available RAM. A cell block contains 16 sheet rows. Excel 97 through Excel 2004 supports up to 16 sheets with 4096 cell blocks per sheet, for a total of 64,000 cell blocks. |
| Sparklines | Sparklines cannot be saved in the Excel 97 through Excel 2004 file format. |
| Color formatting in header and footer text | In Excel for Mac 2011 and Excel 2008, you can apply color formatting to header and footer text. You cannot use color formatting in headers and footers in Excel 97 through Excel 2004. The color formatting information appears as plain text in earlier versions of Excel. |
| Cell formatting or styles that are not allowed by the selected file format | In Excel for Mac 2011 and Excel 2008, different cell formatting or cell style options, such as special effects and shadows, are available. These options are not available in Excel 97 through Excel 2004. When you save the workbook, Excel applies the closest available format, which can be identical to another format that you applied to something else. To avoid duplication of formats, you can change or remove the cell formatting and cell styles that don't work before you save the workbook to Excel 97 through Excel 2004 file format. |
| Cell formats | In Excel for Mac 2011 and Excel 2008, you can use 64,000 unique cell formats, but in Excel 97 through Excel 2004, you can use a maximum of 4,000 unique cell formats. Unique cell formats include any specific combination of formatting that is applied in a workbook. To avoid losing specific cell formats that you want to keep available in Excel 97 through Excel 2004, you can remove some cell formats that are not as important to keep. |
| More than 256 font families | In Excel for Mac 2011 and Excel 2008, 1,024 global font families are available, and you can use up to 512 font families per workbook. Excel 97 through Excel 2004 only allows up to 256 font families per workbook. To avoid losing specific font families that you want to keep available in Excel 97 through Excel 2004, you can remove the font families that you do not need. |
PivotTable features
| Feature | Behavior in earlier versions of Excel |
| PivotTable that exceeds 32,500 items | In Excel for Mac 2011, a PivotTable report can have 1,048,576 unique items per field. However, in Excel 97 through Excel 2004, a PivotTable report can have only 32,500 items per field. |
| Conditional formatting rules that are applied to cells in collapsed rows or columns | Conditional formatting rules that are applied to cells in collapsed rows or columns will be lost in Excel 97 through Excel 2004. |
| Named sets that are not associated with a PivotTable | Named sets that are not associated with a PivotTable will be removed in Excel 2008 and Excel 97 through Excel 2004. |
| PivotTable or data connection that contains server settings which do not exist in earlier versions of Excel | Some PivotTable or data connection server settings are unavailable in Excel 2008 and Excel 97 through Excel 2004 and will be lost. |
| PivotTable that contains data represented using the Show Values As feature | The Show Values As feature does not work in Excel 2008 and Excel 97 through Excel 2004. Custom value data that you entered (such as % of Grand Total, % of Column Total, or Running Total In) cannot be displayed. These custom outputs will not be saved, and will be replaced by the original values from the data source. |
| Theme-based PivotTable styles | Theme-based PivotTable styles are not available in Excel 97 through Excel 2004 and cannot be displayed. |
| PivotTables in earlier versions of Excel | A PivotTable report that you create in Excel for Mac 2011 cannot be refreshed in Excel 97 through Excel 2004. |
| PivotTable that has fields in compact form | Compact form (alone or in combination with tabular and outline form) can be used in Excel for Mac 2011 to keep related data from spreading horizontally off the screen and to help minimize scrolling. This feature is not available in Excel 97 through Excel 2004, and fields will be displayed in tabular form. |
| PivotTable that contains one or more fields with repeated labels | Repeated labels are not available in Excel 2008 and Excel 97 through Excel 2004, and the labels will be lost when you refresh the PivotTable report in the earlier version of Excel. |
Sorting and filtering features
| Feature | Behavior in earlier versions of Excel |
| Sort state has more than three sort conditions | In Excel for Mac 2011, you can apply sort states with up to 64 sort conditions to sort data by, but Excel 97 through Excel 2004 permits sort states with up to three conditions only. To avoid losing sort state information in Excel 97 through Excel 2004, you may want to change the sort state to one that uses no more than three conditions. In Excel 97 through Excel 2004, you can also sort the data manually. However, all sort state information remains available in the workbook and is applied when the workbook is opened again in Excel for Mac 2011, unless the sort state information is edited in Excel 97 through Excel 2004. |
| Data that is filtered by a cell color, icon set, or font color | In Excel for Mac 2011, you can filter by a cell color, font color, or icon set. These filter types don't work in Excel 97 through Excel 2004. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In Excel 97 through Excel 2004, you can then filter the data manually. However, all filter state information remains available in the workbook and is applied when the workbook is opened again in Excel for Mac 2011, unless the filter state information is edited in Excel 97 through Excel 2004. |
| Data that is filtered by more than two criteria | In Excel for Mac 2011, you can filter data by more than two criteria. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In Excel 97 through Excel 2004, you can then filter the data manually. However, all filter state information remains available in the workbook and is applied when the workbook is opened again in Excel for Mac 2011, unless the filter state information is edited in Excel 97 through Excel 2004. |
| Data that is filtered by a grouped hierarchy of dates resulting in more than two criteria | In Excel for Mac 2011, you can filter dates by a grouped hierarchy. Because this doesn't work in Excel 97 through Excel 2004, you may want to ungroup the hierarchy of dates. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In Excel 97 through Excel 2004, you can then filter the data manually. However, all filter state information remains available in the workbook and is applied when the workbook is opened again in Excel for Mac 2011, unless the filter state information is edited in Excel 97 through Excel 2004. |
Formula features
| Feature | Behavior in earlier versions of Excel |
| Array formulas that refer to other worksheets | In Excel for Mac 2011, workbook arrays that refer to other worksheets are limited only by available memory, but in Excel 97 through Excel 2004, worksheets can only contain up to 65,472 workbook arrays that refer to other worksheets. Workbook arrays beyond the maximum limit will be converted to and display #VALUE! errors. |
| Formulas that contain more values, references, or names than are available in the selected file format | In Excel for Mac 2011, the maximum length of formula contents is 8,192 characters, and the maximum internal formula length is 16,384 bytes. In Excel 97 through Excel 2004, the maximum length of formula contents is 1,024 characters, and the maximum internal formula length is 1,800 bytes. When the combination of formula arguments (including values, references, and/or names) exceeds the maximum limits of Excel 97 through Excel 2004, the formulas will result in #VALUE! errors when you save the workbook to an earlier Excel file format. |
| Nested formulas | In Excel for Mac 2011, a formula can contain up to 64 levels of nesting, but in Excel 97 through Excel 2004, the maximum is only seven levels of nesting. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors. |
| Formulas that contain functions that have more arguments than are available in the selected file format | In Excel for Mac 2011, a formula can contain up to 255 arguments, but in Excel 97 through Excel 2004, the maximum limit of arguments in a formula is only 30. Formulas that have more than 30 arguments per function will not be saved and will be converted to #VALUE! errors. |
| Formulas that use more operands than are allowed by the selected file format | In Excel for Mac 2011, the maximum number of operands that can be used in formulas is 1,024, but in Excel 97 through Excel 2004, the maximum limit of operands in formulas is only 40. These formulas will not be saved and will be converted to #VALUE! errors. |
| Formulas that contain more than 29 arguments | In Excel for Mac 2011, a User-Defined Function (UDF) that you create by using Visual Basic for Applications (VBA) can contain up to 60 arguments, but in Excel 97 through Excel 2004, the number of arguments in UDFs is limited by VBA to only 29. Formulas with more than 29 arguments to a function will not be saved and will be converted to #VALUE! errors. |
| New functions | Excel for Mac 2011 provides new and renamed functions. Because these functions are not available in Excel 97 through Excel 2004 or Excel 2008, they will return a #NAME? error instead of the expected results when the workbook is opened in the earlier version of Excel. In some cases, the prefix _xlfn is added to the formula, for example, =_xlfn.IFERROR (1,2). |
| Formulas that contain references to tables that don't work in the selected file format | In Excel for Mac 2011, you can use structured references to make it easier and more intuitive to work with table data when you use a formula that refer to a table, either portions of a table, or the whole table. This feature is unavailable in Excel 97 through Excel 2004 or Excel 2008, and structured references will be converted to cell references. |
| Formulas that contain references to tables in other workbooks that are not currently open in this instance of Excel | In Excel for Mac 2011, you can use structured references to make it easier and more intuitive to work with table data when you use a formula that refer to a table, either portions of a table, or the whole table. This feature is unavailable in Excel 97 through Excel 2004 or Excel 2008, and structured references will be converted to cell references. However, if the structured references point to tables in other workbooks that are not currently open, they will be converted to and displayed as #REF errors. |
| Data validation rules that refer to more than 8,192 discontinuous areas of cells | In Excel for Mac 2011, data validation rules can refer to more than 8,192 discontinuous areas of cells. In Excel 2008 and Excel 97 through Excel 2004, this type of data validation rule doesn't work and won't be saved. |
| Data validation rules that refer to values on other worksheets | In Excel for Mac 2011, you can use data validation rules that refer to values on other worksheets. In Excel 2008 and Excel 97 through Excel 2004, this type of data validation is doesn't work and cannot be displayed on the worksheet. However, all data validation rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 2008 or Excel 97 through Excel 2004. |
| Data Consolidation Range with references to cells outside of the row and column limits of the selected file format | In Excel for Mac 2011, data consolidation ranges can contain formulas that refer to data outside of the row and column limit of the selected file format. In Excel 97 through Excel 2004, the worksheet size of Excel 97 through Excel 2004 is only 256 columns by 65,536 rows. Formula references to data in cells outside of this column and row limit are adjusted and may not display correctly. |
| Array formulas that refer to a whole column | Array formulas that refer to a whole column in Excel for Mac 2011 and Excel 2008 will be converted to and displayed as #NUM! errors when they are recalculated in Excel 97 through Excel 2004. |
| Defined names with formulas that use more than 255 characters | When named ranges in a formula exceed the 255-character limit that is allowed in Excel 97 through Excel 2004, the formula will work correctly, but it will be truncated in the Name dialog box and cannot be edited. |
| Formulas that are linked to other workbooks that are closed | When formulas in a workbook are linked to other workbooks that are closed, they can only display up to 255 characters when they are recalculated in Excel 97 through Excel 2004. The formula results might be truncated. To avoid truncated formula results in Excel 97 through Excel 2004, find the cells that contain formulas that link to other workbooks that are closed so that you can verify the links and make the necessary changes. |
| Data Validation formula has more than 255 characters | When a Data Validation formula exceeds the 255-character limit that is allowed in Excel 97 through Excel 2004, the formula will work correctly, but it will be truncated and cannot be edited. |
| Arrays with more than 256 columns or 65,536 rows | In Excel for Mac 2011, you can use array formulas that contain elements for more than 256 columns and 65,536 rows. In Excel 2008, this exceeds the limit for array elements and might return unexpected results. |
| Custom descriptions for VBA User Defined Functions | In Excel for Mac 2011, you can use Visual Basic for Applications (VBA) to create User-Defined Functions (UDFs) with custom descriptions. Custom descriptions are not allowed in Excel 2008 and Excel 97 through Excel 2004 and will be removed. |
Conditional formatting features
| Feature | Behavior in earlier versions of Excel |
| Conditional formatting contains more than three conditions | In Excel for Mac 2011, conditional formatting can contain up to 64 conditions, but in Excel 97 through Excel 2004, you will see the first three conditions only. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Overlapping conditional formatting ranges | Overlapping conditional formatting ranges are not allowed in Excel 97 through Excel 2004, and the conditional formatting is not displayed as expected. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting types | Excel for Mac 2011 includes additional conditional formatting types, such as data bars, color scales, icon sets, top or bottom ranked values, above or below average values, unique or duplicate values, and table column comparison to determine which cells to format. These conditional formatting types don't appear in Excel 97 through Excel 2004. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Stop if True conditional formatting option | In Excel 97 through Excel 2004, conditional formatting without stopping when the condition has been met is not an option. Conditional formatting is no longer applied after the first condition is met. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting in cells that are not adjacent | In Excel 97 through Excel 2004, you will not see conditional formatting in cells that are not adjacent. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting in PivotTables | Conditional formatting results that you see in Excel 97 through Excel 2004 PivotTable reports will not be the same as in Excel for Mac 2011 PivotTable reports. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting that refers to values on other sheets | In Excel 2008 and Excel 97 through Excel 2004, conditional formatting that refers to values on other sheets does not appear. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting that uses formulas for text that contains rules | Excel 2008 and Excel 97 through Excel 2004 cannot correctly display conditional formatting that uses formulas for text that contains rules. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting that uses range-based rules | Excel 2008 and Excel 97 through Excel 2004 cannot correctly display conditional formatting that uses range-based rules when the range-based rules contain formula errors. |
| Conditional formatting icon set arrangement | In Excel 2008 and Excel 97 through Excel 2004, conditional formatting that displays a specific icon set arrangement is not available and the icon set arrangement does not appear on the sheet. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Data bar rules that use a Negative Value setting | In Excel 2008 and Excel 97 through Excel 2004, conditional formatting that contains a data bar rule that uses a negative value does not appear. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Conditional formatting that refers to more than 8,192 discontinuous areas of cells | In Excel 2008 and Excel 97 through Excel 2004, conditional formatting that refers to more than 8,192 discontinuous areas of cells does not appear. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
| Data bar rules that use a fill, border, or bar direction setting | In Excel 2008 and Excel 97 through Excel 2004, conditional formatting that contains a data bar rule that uses a solid color fill or border, or left to right and right to left bar direction settings for data bars, does not appear. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel for Mac 2011, unless the rules were edited in Excel 97 through Excel 2004. |
Chart features
| Feature | Behavior in earlier versions of Excel |
| Chart titles and data labels | Chart or axis titles are limited to 255 characters in Excel 97 through Excel 2004, and any characters beyond this limit will be lost. |
| Custom shape fills, shape outlines, and shape effects | Custom shape fills, shape outlines, and shape effects (such as glow and bevel effects, or gradient line formatting) are not available in Excel 97 through Excel 2004 and do not appear. |
| Colors in charts | Excel for Mac 2011 and Excel 2008 allow up to 16 million colors, but Excel 97 through Excel 2004 limits the colors to those that are available on the standard color palette. Colors that are unavailable will be changed to the closest color on the standard color palette, which may be a color that is already used. |
| Data points in 2-D and 3-D charts | In Excel for Mac 2011, you can use more than 32,000 data points per series in 2-D charts and more than 4,000 data points per series in 3-D charts. This exceeds the limits of data points per series in Excel 97 through Excel 2004 and Excel 2008. |
Graphics, objects, and ActiveX control features
| Feature | Behavior in earlier versions of Excel |
| Special effects on objects | In Excel for Mac 2011 and Excel 2008, you can use special effects, such as transparent shadows, which are unavailable in Excel 97 through Excel 2004. The special effects will be removed. Also, in Excel for Mac 2011 and Excel 2008, when you insert text in a shape that is wider than the shape, the text displays across the boundaries of the shape. In Excel 97 through Excel 2004, this text is truncated. To avoid truncated text, you can adjust the size of the shape for a better fit. |
| Embedded objects | Embedded objects that are created in Excel for Mac 2011 and Excel 2008 cannot be edited in Excel 97 through Excel 2004. |
| Text that overflows shape, WordArt, or text box boundaries | In Excel for Mac 2011, you can display text boxes on objects such as shapes and display the text beyond the boundaries of those objects. In Excel 97 through Excel 2004, text that overflows the boundaries of an object is not visible. |
| Text boxes with text formatting | In Excel for Mac 2011, on an object such as a shape, you can use a text box that displays more than one column of text. In Excel 97 through Excel 2004, the text appears, but in a different format. |
Collaboration features
| Feature | Behavior in earlier versions of Excel |
| Shared workbooks | You cannot share a workbook in the Excel for Mac 2011 file format with people who use Excel 97 through Excel 2004. To continue sharing a workbook with people who use Excel 97 through Excel 2004 and to ensure that all shared workbook features are available in the earlier versions, you should save the shared workbook to the Excel 97 through Excel 2004 file format, and then share it and change it in this file format. |
No comments:
Post a Comment