PivotTable compatibility issues
The Compatibility Checker found one or more compatibility issues related to PivotTables.
Important: Before you continue saving the 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.
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.
In this article
Issues that cause a significant loss of functionality
| Issue | Solution |
| A PivotTable in this workbook exceeds former limits and will be lost if it is saved to earlier file formats. Only PivotTables that are created in Compatibility Mode will work in earlier versions of Excel. | What it means In Excel 2007 and later, a PivotTable supports 1,048,576 unique items per field, but in Excel 97-2003, only 32,500 items per field are supported. What to do In the Compatibility Checker, click Find to locate the PivotTable that exceeds the former limits. Save the workbook to Excel 97-2003 format, and then re-create this PivotTable in Compatibility Mode. |
| A PivotTable in this workbook contains conditional formatting rules that are applied to cells in collapsed rows or columns. To avoid losing these rules in earlier versions of Excel, expand those rows or columns. | What it means Conditional formatting rules that are applied to cells in collapsed rows or columns will be lost in Excel 97-2003. What to do In the Compatibility Checker, click Find to locate the collapsed rows or columns that contain conditional formatting rules, and then expand those rows or columns before you save the workbook in an earlier Excel file format. |
| This workbook contains named sets which are not associated with a PivotTable. These named sets will not be saved. | What it means Named sets that are not associated with a PivotTable will be removed in Excel 97-2007. What to do To avoid this issue, make sure that you create a PivotTable by using a connection. |
| A PivotTable in this workbook has what-if analysis turned on. Any unpublished what-if changes will be lost in earlier versions of Excel. | What it means What-if analysis changes that are not published to the server are not displayed in the earlier version of Excel. What to do Make sure that you publish the what-if analysis changes before you open the workbook in an earlier version of Excel (PivotTable Tools, Analyze tab, Calculations group, OLAP Tools button, What-If Analysis). |
| A PivotTable in this workbook contains a data axis upon which the same measure appears more than once. This PivotTable will not be saved. | What it means In Excel 2010 and later, you can duplicate a measure in a PivotTable that is connected to an Online Analytical Processing (OLAP) data source. The PivotTable cannot be displayed in Excel 97-2007. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains a data axis upon which more than one of the same measure appears, and then remove any duplicate measures so that only one measure remains. |
| A PivotTable or data connection in this workbook contains server settings which do not exist in earlier versions of Excel. Some PivotTable or data connection server settings will not be saved. | What it means Some PivotTable or data connection server settings that are not available in Excel 97-2007 will be lost. What to do Verify that the server settings you use are compatible with earlier versions of Excel and then make the necessary changes (PivotTable Tools, Analyze tab, Data group, Change Data Source button, Connection Properties command). |
| A PivotTable in this workbook contains data represented using the 'Show Values As' feature. These custom outputs will not be saved, and will be replaced by the original values from the data source. | What it means The Show Values As feature is not supported in Excel 97-2007, and custom value data you entered (such as % of Parent Row Total, % of Parent Column Total, or % Running Total In) cannot be displayed. What to do In the Compatibility Checker, click Find to locate the PivotTables that contain custom value outputs, and then remove those outputs (right-click the field, click Show Values As, No Calculation). |
| This workbook contains custom embedded data. This data will not be saved. | What it means Embedded custom data such as Power Pivot data is not supported in Excel 97-2007. The data will be lost. What to do Remove the Power Pivot data from a workbook that you plan to work on in an earlier version of Excel. |
| This workbook contains slicers that can be used to filter PivotTables and CUBE functions within the workbook. Slicers will not work in earlier versions of Excel. | What it means Slicers will not be available in Excel 97-2007 but will remain available for use in Excel 2010 or later. What to do In the earlier version of Excel, you can use PivotTable filters to filter data. |
| This workbook contains slicers that can be used to filter PivotTables and CUBE functions within the workbook. Slicers will not be saved. Formulas that reference Slicers will return a #NAME? error. | What it means Slicers are not supported in earlier versions of Excel. They cannot be shown and will be lost. Refreshing connections that have OLAP Functions which reference slicers will return #NAME? errors instead of the expected results. What to do Use PivotTable filters instead of slicers to filter the data. |
Issues that cause a minor loss of fidelity
| Issue | Solution |
| A PivotTable style is applied to a PivotTable in this workbook. PivotTable style formatting cannot be displayed in earlier versions of Excel. | What it means Theme-based PivotTable styles are not available in Excel 97-2003 and cannot be displayed. What to do In the Compatibility Checker, click Find to locate the PivotTable that has a PivotTable style applied, remove that PivotTable style, and then manually apply PivotTable formatting that is supported in the earlier versions of Excel. |
| A PivotTable in this workbook will not work in versions prior to Excel 2007. Only PivotTables that are created in Compatibility Mode will work in earlier versions of Excel. | What it means A PivotTable you create in Excel 2007 or later cannot be refreshed in Excel 97-2003. What to do In the Compatibility Checker, click Find to locate the PivotTable that is created in the current file format. Save the workbook to Excel 97-2003 format, and then re-create this PivotTable in Compatibility Mode so that you can open it in Excel 97-2003 without loss of functionality. |
| A PivotTable in this workbook has fields in compact form. In earlier versions of Excel, this layout will be changed to tabular form. | What it means Compact form (alone or in combination with tabular and outline form) to keep related data from spreading horizontally off of the screen and to help minimize scrolling is not available in Excel 97-2003, and fields will be displayed in tabular form. What to do In the Compatibility Checker, click Find to locate the PivotTable that has fields in a compact form, and then change that format to outline form or tabular form as needed by clearing the Merge and center cells with labels check box (PivotTable Tools, Analyze tab, PivotTable group, Options command, Layout & Format tab). |
| A PivotTable in this workbook contains settings which do not exist in earlier versions of Excel. Some PivotTable settings will not be saved. | What it means Some PivotTable settings are not supported in Excel 97-2007. These settings will be lost. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains the settings that are not supported, and then make the necessary changes. |
| A PivotChart in this workbook has specific field buttons enabled or disabled. All field buttons will be saved as enabled. | What it means Field buttons that are not shown on a PivotChart will be saved as enabled when open and save the workbook in an earlier version of Excel. What to do When you reopen a workbook after you save it to a file format of an earlier version of Excel, you may have to enable and disable field buttons to display the ones that you want (PivotChart Tools, Analyze tab, Show/Hide group, Field Buttons button). |
| A PivotTable in this workbook contains one or more named sets. Some properties of the named sets may not be saved. | What it means One or more named sets have non-default property settings that may not be saved when you save the workbook to the Excel 97-2003 file format. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains the named set properties that have been changed, and then make the necessary adjustments in the Field Settings dialog box. Right-click any member of the named set, and then click Field Settings. On the Layout & Print tab, make sure that the Display items from different levels in separate fields check box is not selected (the default setting), and that the Automatically order and remove duplicates from the set check box is selected (the default setting). |
| A connection in this workbook contains one or more named sets or calculated members. Some properties of the named sets or calculated members may not be saved. | What it means Named sets or calculated members have properties that are not supported in Excel 2007 and Excel 97-2003.These properties may not be saved. What to do Right-click any member of the named sets, and then click Field Settings, or Value Field Settings, and then make the necessary adjustments. |
| A slicer style exists in this workbook, and is not supported in earlier versions of Excel. This slicer style will not be saved. | What it means A custom slicer style will be lost when the workbook is saved to the file format of an earlier version of Excel. What to do Because slicers are not supported in earlier versions of Excel, there's nothing you can do to retain a custom slicer style. In the earlier version of Excel, you can use PivotTable filters to filter the data. |
| A PivotTable in this workbook contains one or more fields that contain repeated labels. If the PivotTable is refreshed, these labels will be lost. | What it means Repeated labels are not supported in Excel 97-2007, and the labels will be lost when you refresh the PivotTable in the earlier version of Excel. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains repeated labels, and then stop repeating labels (PivotTable Tools, Design tab, Layout group, Report Layout button, Do Not Repeat Item Labels command). |
| Alternative text is applied to a PivotTable in this workbook. Alternative text on PivotTables will be removed in versions prior to Excel 2007. | What it means Alternative text is not available in Excel 97-2007, and cannot be displayed in these earlier versions of Excel. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains alternative text. To display the alternative text in the earlier version of Excel, you can copy it into a blank cell on the worksheet, or you could insert a comment that contains the text. Right-click anywhere in the PivotTable, click PivotTable Options. On the Alt Text tab, in the Description box, select the alternative text, and then press Ctrl+C to copy it. |
| Alternative text is applied to a PivotTable in this workbook. Alternative text cannot be displayed in Excel 2007. | What it means Alternative text is not available in Excel 97-2007, and cannot be displayed. What to do In the Compatibility Checker, click Find to locate the PivotTable that contains alternative text. To display the alternative text in the earlier version of Excel, you can copy it into a blank cell on the worksheet, or you could insert a comment that contains the text. Right-click anywhere in the PivotTable, click PivotTable Options. On the Alt Text tab, in the Description box, select the alternative text, and then press Ctrl+C to copy it. |
No comments:
Post a Comment