Weird Filter Behavior

[DougStroud]

I am getting a weird result when applying a data filter. Initially the data filter was working correctly. But after inserting some additional data in the middle of the original data set the filter is now only looking/sorting down to row 18. I have tried manually correcting the range that I found under the Advanced button but it reverts back to this range: $A$2:$Q$18. My data currently extends down to Q250.

Thanks!

 

[Mentor82]

Hi,
Try the following, it may help.
Concert the data of the table to range, remove filter ctrl+shift+L, select the whole range and format it to table.
Let me know if that helps. If not, I recommend you upload a co o y of the file to ex. dropbox to have a look what the problem you have.
Regards,
Sebastian

 

[DougStroud]

Hi Sebastian,
Thank you! That worked... It is a bit laborious b/c I will have to insert ongoing data in the middle of the existing data, but I am grateful for the work-around

Thank you,

Doug

 

[Mentor82]

Hi,
Thanks for the feedback. When you have the whole range of data, all the columns and rows within your range, formatted as an excel table ex a1:c100, every time you place new data in from row right below that table on (row 101 on) in columns A-C excel will add formatting to the entered data as well as the filters of the table will have theylir range automatically adjusted to rows 1-101. The problem that you describe i.a. occurs for ex. when you for incidents copy data from another table and pase it right below the main table with source formatting. Then excel does not adjust the filters range enlarging it by the rows with data you just copied because it considers it as two tables put together. The work around I mentioned is to fix this problem. Pls note when you paste rows with data to the existing excel table use paste special to paste values only.
Anyway, I'm happy to help

Regards,
Sebastian