Excel 2007 data corruption using sort within a filter.
 

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

Excel 2007 data corruption using sort within a filter.
 

I reproduced the issue and I'm really surprised MS changed this behavior.
While you could argue for either 'sort range', once a product picks a way it
should be consistent across versions.

The only suggestion I have is to keep users from sorting via autofilter.
Autofilter sorting can be disabled with a policy setting.

--
Jim
"J_Harrington" wrote in message
...
| 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

Excel 2007 data corruption using sort within a filter.
 

Jim,

Where might I find this policy setting? I've looked, but I don't see
anything that appears to disable the sort within the filter. Any assistance
is appreciated.

Thanks,
Jodi


"Jim Rech" wrote:

I reproduced the issue and I'm really surprised MS changed this behavior.
While you could argue for either 'sort range', once a product picks a way it
should be consistent across versions.

The only suggestion I have is to keep users from sorting via autofilter.
Autofilter sorting can be disabled with a policy setting.

--
Jim
"J_Harrington" wrote in message
...
| 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

Excel 2007 data corruption using sort within a filter.
 

I cannot recreate any of this. Excel 2007 sorts only filtered
records, using either the ribbon command or the filter drop-down. In
either case, all columns are sorted and rows preserved. Excel 2003
appears to behave identically. What am I doing wrong?

1. In range A1:J1, enter ="Column "&COLUMN() then copy and paste
values.
2. In range A2:J20, enter =RANDBETWEEN(1,100) then copy and paste
values.
3. Copy range over starting at A25
4. Filter Column 1 to =50
5. Use sort ascending from Column 1 filter dropdown
6. Unfilter data - only filter rows sort
7. Use sort ascending from Column 1 filter dropdown
8. Use sort ascending from Column 1 filter dropdown on the copied
range

Conditional formatting does not reveal any discrepancies. With Ribbon
sort, result is the same. In Excel 2003, replace step 2 with
=INT(RAND()*100)+1 and result is the same.


On Nov 11, 8:46 pm, J_Harrington
wrote:
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

Sample Table
 

1. In Row 1 enter: Test; Test1; Test2; Test3 (semicolons indicate column
separations)
2. In Row 2 enter: computer; 111; Ohio; 123
3. In Row 3 enter: server; 2222; Texas; 999
4. In Row 4 enter: router; 5678; Kansas; 789
5. In Row 5 enter: switch; 3457; Virginia; 567
6. Select Row 4 and apply a fill color of red
7. Select Columns B and C (Test1 and Test2)
8. Click the Data tab, select Filter in the Sort & Filter group
9. Click the drop-down next to Test2, select Sort A to Z
10. Data is now inconsistent with 5678 and Kansas appearing inline with
computer and 123 in Row 1

You can repeat this with any data. If you apply the filter to all columns
or use the sort on the Ribbon next to the Filter command, there are no issues.

Thank you for trying this. Your input is greatly appreciated.

-Jodi