Sorting and filtering compatibility issues

The Compatibility Checker found one or more compatibility issues related to sorting and filtering.

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

Issues that cause a minor loss of fidelity

Issues that cause a significant loss of functionality

Issue

Solution

A worksheet in this workbook contains a sort state with more than three sort conditions. This information will be lost in earlier versions of Excel.

What it means    In Excel 2013 or later, you can apply sort states with up to sixty-four sort conditions to sort data by, but Excel 97-2003 supports sort states with up to three conditions only. To avoid losing sort state information in Excel 97-2003, you may want to change the sort state to one that uses no more than three conditions. In Excel 97-2003, 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 2013 or later, unless the sort state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been sorted with more than three conditions, and then change the sort state by using only three or less conditions.

A worksheet in this workbook contains a sort state that uses a sort condition with a custom list. This information will be lost in earlier versions of Excel.

What it means    In Excel 2013 or later, you can sort by a custom list. To get similar sorting results in Excel 97-2003, you can group the data that you want to sort, and then 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 2013 or later, unless the sort state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been sorted by a custom list, and then change the sort state so that it no longer contains a custom list.

A worksheet in this workbook contains a sort state that uses a sort condition that specifies formatting information. This information will be lost in earlier versions of Excel.

What it means    In Excel 2013 or later, you can sort data by a specific format, such as cell color, font color, or icon sets. In Excel 97-2003, you can sort only text.

However, all sort state information remains available in the workbook and is applied when the workbook is opened again in Excel 2013 or later, unless the sort state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been sorted by a specific format, and then change the sort state without specifying formatting information.

Top of Page

Issues that cause a minor loss of fidelity

Issue

Solution

Some data in this workbook is filtered in a way that is not supported in earlier versions of Excel. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, you can apply filters that are not supported in Excel 97-2003. 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-2003, 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 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Some data in this workbook is filtered by a cell color. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, you can filter by a cell color, font color, or icon set — these methods are not supported in Excel 97-2003. 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-2003, 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 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Some data in this workbook is filtered by a font color. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, you can filter by a cell color, font color, or icon set — these methods are not supported in Excel 97-2003. 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-2003, 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 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Some data in this workbook is filtered by a cell icon. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, you can filter by a cell color, font color, or icon set — these methods are not supported in Excel 97-2003. 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-2003, 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 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Some data in this workbook is filtered by more than two criteria. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, 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-2003, 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 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Some data in this workbook is filtered by a grouped hierarchy of dates, resulting in more than two criteria. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.

What it means    In Excel 2013 or later, you can filter dates by a grouped hierarchy. Because this is not supported in Excel 97-2003, 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.

However, all filter state information remains available in the workbook and is applied when the workbook is opened again in Excel 2013 or later, unless the filter state information is edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Data grouping can also be turned off on the Advanced tab in the Excel Options dialog box. (File tab, Options).

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×