Excel data filter includes rows below selected data

Question

I’m using excel to compare old and new value every year then using its build-in filter to easily sort them to notice what changes. Only few data change between year so doing it manually (I mean by Filter’s drop down “sort from smallest to largest function”) is more doable than using a formula for every row. My problem is the filter also include data below it when sorting (starting from row 369, AG371 and below. They are COUNTIF function for the data in the picture).

What can I do so anything below the filtered range doesn’t get involved in sorting through filter?

Methods I have tried

-simple solution is to simply sorting it instead of filter but that's just playing around the issue.

• That number starting from 367 in AF column didn’t exist at first, I add them and use “exclude value greater than 367” so they don’t get sorted together with the data

-putting COUNTIF data 20 rows below the data prevent it from being sorted (mere 10 row apart doesn’t work, it still get sorted) but I’d rather the data be close so I can see them quicker

I use Office Profesional Plus 2013 but this is for personal use, Windows 10 Pro.

Answer 1

You should have one blank row (at least) between sections - at least one, below the table that you want to filter. Then, fix your headers so that they are only in one cell, not two (that is a better data practice). Finally, select your data table and use Insert / Table (should be the third ribbon button from the left). Since you have headers, make sure the 'my table has headers' is checked. And then you should be able to only filter on your data.

Answer 2

Create a structured data table that excludes the values below that you want to leave alone.

Answer 3

Create a structured data table

I search for “structured data table” but I couldn’t find what that referring to. How might I do that Bernie?  Also thank you replying

While searching for it I only find more words to describe my problem: the table automatically add and propagate rows,  the data range is dynamic

https://www.excelforum.com/excel-general/1361341-help-adding-tables.html#post5582179

Answer 4

Ooh I never knew there is an option to make Table like that. I need to clear my filter but it work, my data below doesn't get sorted anymore. Thank you