Excel 2007 corrupts data during sort

By J_Harrington ·
We're seeing an issue in Excel 2007 related to sorting data using the sort within an auto filter. Excel behaves differently if you run the sort from the filter drop-down or if you run it from the Ribbon. From the Ribbon, the sort function works every time for all columns. From the drop-down, Excel only sorts the filtered columns.

This behavior is different than 2003 where Excel sorts all of the columns regardless of how many columns have filters. This is a problem since there are manyspreadsheets created in earlier versions of Excel with filters on only some columns and there is no warning that the data will become inconsistent. Any assistance is greatly appreciated.

Below are the steps to recreate the problem.

1. Create a blank spreadsheet in Excel 2007.
2. Populate the first row with column headers.
3. Populate a few rows worth of data.
4. Select some, but not all, columns and click the Filter button on the Ribbon. This applies filters to the columns you select, not all columns.
5. Use the sort feature in the drop-down menu in the column header to sort the data.
6. No warning is given. Only filtered rows sort. Your table now has inconsistent data

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

re: sorting....

by ThumbsUp2 In reply to Excel 2007 corrupts data ...

Typically, sorts work on what has been selected. If nothing is selected, it defaults to what is visible. If you have filters in place thus hiding rows, they don't sort and are copied over the top of by sorted data.

Try highlighting all data that you want to sort, from A1 to ZZ99, or wherever it ends, which will select the hidden rows too. That will hold the rows together as one unit and includes hidden data that has been filtered out in the sort criteria.

Personally, I don't like sorting with filters in place because of this problem. You can't see what it's doing. So, I forget about the filters. I use one big sort to get what I don't want to count into one group and then further sort smaller groups of rows down until I get just the group I really want to sort. Then, I can apply filters to hide what I don't want to see.

Collapse -

Selection of cells is irrelevant

by J_Harrington In reply to re: sorting....

Thanks for the reply.

In this particular problem, the cells you select are irrelevant. Even if you select all cells or the entire data range, Excel will only sort the filtered columns if you use the autofilter sort.

Related Discussions

Related Forums