Important:
-
Before you save an Excel 2007 or newer version workbook to an earlier file format, you should address issues that cause a significant loss of functionality so that you can prevent permanent loss of data or incorrect functionality. Excel's Compatibility Checker can help you identify potential issues.
-
Issues that cause a minor loss of fidelity might or might not have to be resolved before you continue saving the workbook — data or functionality is not lost, but the workbook might not look or work exactly the same way when you open it in an earlier version of Microsoft Excel.
The Compatibility Checker will automatically launch when you attempt to save a workbook in the Excel 97-2003 .xls format. If you're not concerned about potential issues, you can turn the Compatibility Checker off.
For more information about the Compatibility Checker, see Save an Excel workbook for compatibility with earlier versions of Excel.
In this article
Issues that cause a significant loss of functionality
| Issue | Solution |
|---|---|
| This workbook has label information that will be lost or not visible if the file is saved as an earlier Excel format. | What it means If you save this workbook in .xls format, the labels will be permanently lost. If you save it in .xlsx format the labels will be preserved, but won't display in Excel 2016 or earlier versions. What to do To preserve the labels, avoid saving the workbook in .xls format. If you need people who can't open .xlsx files to be able to open the workbook, consider opening the workbook in Excel for the web and sending the file URL to them at an email address that is registered as a Microsoft Account. |
| This workbook contains standalone PivotCharts that can be used to view data without a PivotTable in the worksheet. Standalone PivotCharts will be saved as static charts. | What it means Beginning in Excel 2013, PivotCharts do not require PivotTables on the same worksheet. In earlier versions, PivotCharts can only read data from a PivotTable on the same worksheet. If you save the file using an earlier version, PivotCharts that do not read data from PivotTables on the same worksheet will be saved as static charts. What to do If feasible, consider adding a PivotTable as the PivotChart's data source, on the worksheet with the PivotChart. Or, consider replacing the PivotChart with a set of static charts, one for each pivot. If neither is feasible, consider which data to use as the source for the static chart in the saved file. |
| Some formulas contain references to tables in other workbooks that are not currently open in this instance of Excel. These references will be converted to #REF on save to Excel 97-2003 format because they cannot be converted to sheet references. | What it means Your workbook contains some formulas that include structured table references such as =SUM(SalesWorkbook.xlsx!SalesTable[Sales Amount]), which are not supported in the version you've chosen to save this file. The workbook the formulas refer to is not open, so if you save and close your file, the formulas will be converted to =#REF!. What to do It is recommended that you save this file as one of the modern file formats that supports structured table references. Otherwise, before you close this workbook, open the linked workbook. Then save and close this workbook while the linked workbook is open, and Excel will convert the structured table references to cell references. For example, =SUM(SalesWorkbook.xlsx!SalesTable[Sales Amount]) would be converted to something like =SUM([SalesWorkbook.xlsx]Sales!D2:D150). For more information about structured tables references, see Using structured references with Excel tables. |
| One or more sheets in this workbook contain a sheet view. These views will be removed. | What it means Sheet Views are only available in Excel for the web. If you open a workbook that has sheet views in Excel desktop versions, the sheet views are unavailable. If you save the file as Excel 97-2003 format, the sheet views are discarded. What to do If you need to preserve the sheet views, don't save the file as Excel 97-2003. If you need to use the sheet views, open the file in Excel for the web. |
| Decorative setting for this object will be lost. | What it means Beginning in Excel 2019, you can mark an object as decorative instead of adding Alt Text. Examples of objects that should be marked as decorative are stylistic borders. People using screen readers will hear that these objects are decorative so they know they aren't missing any important information. The option to mark as decorative is not available in Excel 2016 or earlier versions. What to do For each such object, add Alt Text for people using screen readers. |
| This workbook contains Timelines that can be used to filter PivotTables, PivotCharts and CUBE functions within the workbook. Timelines will not work in earlier versions of Excel. | What it means Timelines were introduced in Excel 2013 and do not function in earlier versions. What to do If you share the file with people who use earlier versions of Excel, inform them that they will not be able to use the Timelines and should expect to use other methods of filtering data by time periods. |
| This workbook contains Timelines that can be used to filter PivotTables, PivotCharts and CUBE functions within the workbook. Timelines will not be saved. Formulas that reference Timelines will return a #REF! error. | What it means Timelines are not supported by the Excel 97-2003 file format (.xls). If you save the workbook in Excel 97-2003 format, all Timelines will be lost, and formulas that refer to them will return an error. What to do Remove all formula references to Timelines, and plan to use regular date filters to filter the data by time periods. |
| This workbook contains slicers that can be used to filter tables within the workbook. Table slicers won't work in earlier versions of Excel. | What it means If you share the file with people who aren't using Excel 2013 or newer, they won't be able to use the slicers. What to do Inform people with whom you share the file that they will not be able to use the slicers, but can filter the table using other methods. |
| This workbook contains 3D models that will be displayed as an image in previous versions of Excel. | What it means If you share the file with people who aren't using Excel 365, they will not be able to use the 3D models - these will be displayed as images. What to do If it is critical that people can use the 3D models, they will need to open the file in Excel 365. If it is not critical, considering informing people that the 3D models will display as images if not opened in Excel 365. |
| This workbook contains Scalable Vector Graphics (SVGs) that will be displayed as Portable Network Graphics in previous versions of Excel. | What it means SVGs are vector graphics, and can scale easily and look the same. PNGs are raster graphics, and scale poorly - they will get jagged edges. If people who use versions of Excel other than Excel 2019 open the file, they will get PNGs instead of SVGs. What to do If it is critical that your images can scale well, they will need to be opened in Excel 2019 or later. If it not critical that they can scale well, consider informing people you share the file with that the images will not scale well in Excel 2016 or earlier. |
| A PivotTable or Data Connection that supports analysis of multiple tables exists in this workbook and will be lost if it is saved to earlier file formats. | What it means Data Features, such as PivotTables or Data Connections, that use multiple tables will not be saved if you save the workbook in Excel 97-2003 format. If you share the file in its current format (.xslx) with people who use Excel 2007 or 2010, they won't be able to use the Data Features. What to do For each Data Feature, create a new worksheet with a table that contains all the fields needed by the Data Feature, and then either change the data source for the Data Feature to the new table or recreate the Data Feature using the new table as the data source. To create the new table, you can either start by inserting one of your original source tables and then create the remaining fields by using VLOOKUP to retrieve them from the rest of your source tables, or you can use Access to recreate the data model for your Data Feature as a query, and then import the query data into a worksheet in the workbook as a table. |
| Excel Data Features (PivotTables, Workbook Connections, Tables, Slicers, Power View and Cube Functions) that support analysis of multiple tables are not supported in earlier versions of Excel and will be removed. | What it means Data Features that use multiple tables will not be saved if you save the workbook in Excel 97-2003 format. If you share the file in its current format (.xslx) with people who use Excel 2007 or 2010, they won't be able to use the Data Features. What to do For each Data Feature, create a new worksheet with a table that contains all the fields needed by the Data Feature, and then either change the data source for the Data Feature to the new table or recreate the Data Feature using the new table as the data source. To create the new table, you can either start by inserting one of your original source tables and then create the remaining fields by using VLOOKUP to retrieve them from the rest of your source tables, or you can use Access to recreate the data model for your Data Feature as a query, and then import the query data into a worksheet in the workbook as a table. |
| This workbook contains data imported using Power Query. Earlier versions of Excel do not support Get & Transform Data (Power Query) capabilities. All the workbook data that was imported with Power Query queries cannot be refreshed in earlier versions of Excel. | What it means Beginning with Excel 2016, you can use modern Get & Transform Data (Power Query) capabilities to import, shape and combine data from multiple sources. These capabilities are not available in Excel 97-2003. What to do If you frequently save a workbook to Excel 97-2003 file format and you need to refresh your data, you should use one of the legacy data import wizards. Refer to Data import and analysis options for steps to restore the legacy data import wizards. |
| This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 65,536 rows by 256 columns will not be saved. Formula references to data in this region will return a #REF! error. | What it means Beginning with Excel 2007, worksheet size is 1,048,576 rows tall by 16,384 columns wide, but Excel 97-2003 is only 65,536 rows by 256 columns. Data in cells outside of this row and column limit is lost in Excel 97-2003. What to do In the Compatibility Checker, click Find to locate the cells and ranges that fall outside the row and column limits, select those rows and columns, and then place them inside the column and row limits of the worksheet, or on another worksheet by using Cut and Paste. |
| This workbook contains Scenarios with references to cells outside of the row and column limits of the selected file format. These Scenarios will not be saved in the selected file format. | What it means A scenario in the worksheet refers to a cell outside Excel 97-2003's row and column limit (65,536 rows by 256 columns), and is no longer available when you continue saving the workbook to the earlier Excel version. What to do In the Scenario Manager, look for the scenario that contains a reference that falls outside the row and column limit of the earlier Excel version, and then change the reference to a location within that limit. On the Data tab, in the Data Tools group, click What If Analysis > Scenario Manager. In the Scenarios box, locate the scenario that causes the compatibility issue, and then edit its reference. |
| This workbook contains dates in a calendar format that is not supported by the selected file format. These dates will be displayed as Gregorian dates. | What it means Beginning with Excel 2007, you can create custom international calendar formats, such as Hebrew Lunar, Japanese Lunar, Chinese Lunar, Saka, Zodiac Chinese, Zodiac Korean, Rokuyou Lunar, and Korean Lunar. However, these calendar formats are not supported in Excel 97-2003. What to do To avoid loss of functionality, you should change the calendar format to a language (or locale) that is supported in Excel 97-2003. |
| This workbook contains dates in a calendar format that is not supported by the selected file format. These dates must be edited by using the Gregorian calendar. | What it means Beginning with Excel 2007, you can apply a non-Western calendar type, such as Thai Buddhist or Arabic Hijri. In Excel 97-2003, these calendar types can only be edited in Gregorian. What to do To avoid loss of functionality, you should change the calendar format to a language (or locale) that is supported in Excel 97-2003. |
| This workbook contains more cells with data than are supported in earlier versions of Excel. Earlier versions of Excel will not be able to open this workbook. | What it means Beginning with Excel 2007, the total number of available cell blocks (CLBs) is limited by available memory. In Excel 97-2003, the total number of available CLBs is limited to 64,000 CLBs in an instance of Excel. A CLB includes 16 worksheet rows. If all rows in a worksheet contain data, you would have 4,096 CLBs in that worksheet, and you could have only 16 such worksheets in a single instance of Excel (regardless of how many workbooks you have open in Excel). What to do To make sure that the workbook does not exceed the 64,000 CLB limit and that it can be opened in Excel 97-2003, you should work in Compatibility Mode in Excel 2007 and up after you save the workbook to Excel 97-2003 file format. In Compatibility Mode, Excel keeps track of the CLBs in the active workbook. |
| One or more cells in this workbook contain a sparkline. Sparklines will not be saved. | What it means In Excel 97-2010, sparklines are not displayed on the worksheet. However, all sparklines remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and up. What to do In the Compatibility Checker, click Find to locate cells that contain sparklines, and then make the necessary changes. For example, you could apply conditional formatting instead of or in addition to the sparklines that won't be displayed in the earlier version of Excel. |
| This file originally contained features which were not recognized by this version of Excel. These features will not be saved. | What it means Features that were used in this workbook are not available in versions of Excel prior to Excel 2007 (Excel 97-2003), and they will be lost. What to do If you know which features might be causing this issue, remove or replace them if possible, and then save the file in the file format that you want. |
| This file originally contained features which were not recognized by this version of Excel. These features are not preserved when saving an OpenXML file to the XLSB file format, or vice versa. | What it means Features that were used in this workbook will not be available in Excel 2007 when you save an OpenXML file to an Excel Binary Workbook (.xlsb), or vice versa. When you continue saving the file, the features will be lost. What to do If you know which features might be causing this issue, remove or replace them if possible, and then save the file in the file format that you want. |
| This workbook contains one or more charts (such as treemaps) that aren't available in earlier versions of Excel. They will be removed. | What it means Newer chart types, such as treemaps that were first introduced in Excel 2016, used in this workbook will not be available in earlier versions. When you continue saving the file, the charts will be lost. What to do Excel will remove any newer chart types from your workbook if you continue to save in an older file format. If you know users on older versions will need to use your workbook, you should consider removing all new chart types from your workbook and instead use the standard chart types available to all versions. You can then save the file in the file format that you want. |
Issues that cause a minor loss of fidelity
| Issue | Solution |
|---|---|
| One or more charts in this workjbook contain objects that have been filtered out and are now hidden. Earlier versions of Excel will not support the ability to filter the objects back into the chart. | What it means Beginning in Excel 2013, you can save a file that has hidden chart objects and retain the ability to unfilter those hidden objects in the saved file. In earlier Excel versions, any filtered chart objects become unavailable after the file is saved. What to do Unfilter the affected chart objects before saving and opening the file in Excel 2010 and earlier versions. |
| A slicer in this workbook contains settings which do not exist in earlier versions of Excel. Some Slicer settings will not be saved. | What it means Beginning in Excel 2010, slicers provide a filtering mechanism for PivotTable reports. Additional slicer settings were introduced in Excel 2013. These newer slicer settings will not be preserved if you open the file in Excel 2010, and slicers won't work in earlier Excel versions. What to do Make sure anyone who needs the newer slicer settings is using Excel 2013 or newer versions. Do not save the file in Excel 97-2003 format. |
| This workbook contains date filters with a new "Whole Days" option selected. Earlier versions of Excel do not support it. Users of these versions will not be able to refresh the data connection. | What it means Beginning in Excel 2013, you can select "Whole Days" as an option for many date filters. If you apply a filter with this option to a connected range or PivotTable, users of earlier versions will not be able to refresh the connection. What to do To make the file compatible, remove the "Whole Days" option from any affected date filters. |
| One or more charts in this workbook contain leader lines that are not supported in earlier versions. This functionality will be removed from the chart when viewed in earlier versions of Excel. | What it means Beginning in Excel 2013, you can add leader lines to connect a chart's label values with the corresponding chart portions. These visual cues won't display in earlier versions of Excel. What to do To make the chart appear the same in all supported Excel versions, replace each leader line by inserting a line shape and positioning it manually (click Insert > Illustrations > Shapes, then pick a line from the available options). |
| One or more charts in this workbook contain objects that have been filtered out and are now hidden. These charts will retain full fidelity in the selected file format, but filtered out parts of the chart will not be saved. | What it means Beginning in Excel 2007, filtered portions of charts are retained and remain hidden when the file is saved in .xlsx file formats. However, hidden portions will not be retained if the file is saved in the Excel 97-2003 file format. What to do Before saving the file in the Excel 97-2003 file format, locate the affected chart(s) and unhide the filtered portions. |
| An embedded object in this worksheet can't be edited because it was created in a newer version of Microsoft Office. | What it means Beginning with Excel 2007, objects are embedded with a newer method. These objects are read-only if the file that contains them is opened in Excel 97-2003. What to do Either delete the object or inform people who you share the file with that they must use Excel 2007 or newer versions if they want to be able to edit the embedded object. |
| Earlier versions of Excel do not support color formatting in header and footer text. The color formatting information will be displayed as plain text in earlier versions of Excel. | What it means Beginning with Excel 2007, you can apply color formatting to header and footer text. You cannot use color formatting in headers and footers in Excel 97-2003. What to do In the Compatibility Checker, click Fix if you want to remove the color formatting. |
| This workbook contains worksheets that have even page or first page headers and footers. These page headers and footers cannot be displayed in earlier versions of Excel. | What it means Beginning with Excel 2007, you have the option to display different header and footer text on even pages or on the first page. In Excel 97-2003, even page or first page headers and footers cannot be displayed, but they remain available for display when you open the workbook in Excel 2007 and up again. What to do If you frequently save a workbook to Excel 97-2003 file format, it is best not to specify even or first page headers or footers for that workbook. |
| Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. | What it means Beginning with Excel 2007, different cell formatting or cell style options, such as special effects and shadows, are available. These options are not available in Excel 97-2003. What to do When you continue saving the workbook, Excel applies the closest available format, which can be identical to another format you applied to something else. To avoid duplication of formats, you can change or remove the cell formatting and cell styles that are not supported before you save the workbook to Excel 97-2003 file format. |
| This workbook contains more unique cell formats than are supported by the selected file format. Some cell formats will not be saved. | What it means Beginning with Excel 2007, you can use 64,000 unique cell formats, but in Excel 97-2003, you can only use up to 4,000 unique cell formats. Unique cell formats include any specific combination of formatting that is applied in a workbook. What to do To avoid losing specific cell formats that you want to keep available in Excel 97-2003, you can remove some cell formats that are not as important to keep. |
| This workbook contains more unique font formats than are supported in the selected file format. Some font formats will not be saved. | What it means Beginning with Excel 2007, 1,024 global font types are available, and you can use up to 512 of them per workbook. Excel 97-2003 supports less unique font formats. What to do To avoid losing specific font formats that you want to keep available in Excel 97-2003, you can remove some font formats that are not as important to keep. |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
See Also
Save an Excel workbook for compatibility with earlier versions of Excel
Excel formatting and features that are not transferred to other file formats
No comments:
Post a Comment