Filtered table shows unwanted rows in Excel 2013

Question

Hi!

I have a disturbing problem with how filters work in my data table. It seems that after I include a =SUBTOTAL function column in the table, the filters won't work properly anymore.

I have a simple sheet with a table. I have a column named "Hobby". This column applies a dropdown list data validation (and gets data from column named "Hobby validation"). I also have a column named "Budget cumulated", where I use the =SUBTOTAL function.

When I filter the table by "Hobby" I get unwanted rows (i.e. unselected Hobbies) in the filtered table. After playing with the filter (selecting all, i.e. deleting the filter condition then filtering again etc...) the unwanted rows/data in the filtered view are getting more and more numerous. After playing with the filter for couple of times, eventually I get hidden rows in the unfiltered view.

I would appreciate any hints how to fix this.

Pic 1: Initial table; selected cell shows how I used the =SUBTOTAL function (it's in Hungarian but it should not bother)

Pic 2: filtered view with unwanted rows - column is filtered by 'Computers'; rows with 'Music' shouldn't appear

Pic 3: hidden rows as an unintentional result of using the filter several times

Answer 1

Try moving column E out of the table to another column, separated from your filtered table, and don't apply a filter to that column.