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.