Table filtering broken by Subtotal function in Excel 2010
 

I recently added a running total column to a Table. To my surprise, the filtering stopped working. In a column with numerical values, the rows with the selected value remained visible but were followed by other rows containing random values. Clearing the filter generally left a row or two hidden near the end of the table.

However, when I convert the table to a range, filtering starts working again

Simple test code shows this problem;

Data block as a Table
Table1
C D E
8 COL1 COL2 COL3
9 1 4 4
10 2 4 8
11 2 50 58
12 34 49 107
13 34 50 157
14 50 1 158
15 2 160

NOTES
1
2 Table1 defined as C8:E15
3 COL3 maintains a running total of COL2 i.e
E9: =SUBTOTAL(9,$D$8:D9)
The formula in COL3 breaks filtering on the table

Table3
C D
24 COL1 COL2
25 1 4
26 2 4
27 2 50
28 34 49
29 34 50
30 50 1
31 2

NOTES
1
2 Table3 defined as C24:D31
3 Running total column removed from table
Filtering now works on the table


Table converted to a range
C D E
41 COL1 COL2 COL3
42 1 4 4
43 2 4 8
44 2 50 58
45 34 49 107
46 34 50 157
47 50 1 158
48 2 160

NOTES

1 C41:E48 is a copy of Table1 converted to a range
2 COL3 maintains a running total of COL2 i.e
E42: =SUBTOTAL(9,$D$41:D42)
3 Filtering now works on the data block

I have checked online and have seen no reference to this problem. I am surprised since a running total is a common display feature. I am running Excel 2010 on Windows 7 (64 bit)

Table filtering broken by Subtotal function in Excel 2010
 

Seems this problem was reported on 11-Jan-2009 on this news group.
Search for heading ;
rogue rows appear in autofiltered sort

In my opinion, this should be considered an Excel bug. Surprised that it jhas not been fixed

Table filtering broken by Subtotal function in Excel 2010
 

On Sunday, March 6, 2016 at 1:24:44 AM UTC-5, Desmond Walsh wrote:
I recently added a running total column to a Table. To my surprise, the filtering stopped working. In a column with numerical values, the rows with the selected value remained visible but were followed by other rows containing random values. Clearing the filter generally left a row or two hidden near the end of the table.

However, when I convert the table to a range, filtering starts working again

Simple test code shows this problem;

Data block as a Table
Table1
C D E
8 COL1 COL2 COL3
9 1 4 4
10 2 4 8
11 2 50 58
12 34 49 107
13 34 50 157
14 50 1 158
15 2 160

NOTES
1 Table1 defined as C8:E15
2 COL3 maintains a running total of COL2 i.e
E9: =SUBTOTAL(9,$D$8:D9)
The formula in COL3 breaks filtering on the table


The option of converting the table into a range has drawbacks especially if the data is going to be accessed using VBA code. One loses the very real advantages of having a table object.

The other workaround is to position the Running Total column (COL3) as the first column to the right of the table. Filtering works fine as long as this column is not part of the table definition. There is no way to exclude the running total column from the table definition if it is positioned anywhere between the table's first column and last column.