Advanced filtering not working in Excel 2021

Copper Contributor

pls forgive is this is a dual post - I though I put this out a few days ago but I cannot find it.

I use Excel to handle large sets of data. In this case, temperature data by date.

The current xlsx has over 74,000 rows. I wish to filter these data and standard filtering functions do not seem to work.

Documents indicate that Advanced Filtering is the way to go.

I am unable to make this work. I recently spent over an hour with MS Chat Support (and with screen sharing) and between the two of us, we were unable to make this work.

Here are a few screenshots to show what I'm dealing with.





MS CSR diagnosed this and we were unable to make this work on another copy, or on other data, or on single digit data (rather than a date) and it seems to me like something is wrong here as I believe I am doing this correctly.

MS CSR also "repaired" the software and that also made no change.

Rebooting or reloading the software also did not make any differences..

This is a brand new copy of Microsoft Office Plus 2021 (for desktop).   

This is NOT 365 product.

Please - this is quite upsetting - please what is the procedure to further diagnose this issue - or more likely, train this user in doing this correctly??


8 Replies
Your criteria range needs to include the headers so F1:G2
Have you tried linking the sheets together?



Thanks - none of this works.  Data is consistent to use in a filtering formula, but the so-called Advanced Filter does not work.


With assistance from a MS Customer Service Rep, we repeated all the diagnostics and changing things around sufficiently that we were both stumped.


I especially dislike bumping into these kinds of problems.

Back when I was aggressively trying to fix another thing, maybe 10 years ago or so, I paid the very expensive tech support fee - as I know it would be reimbursed if it was a real bug.

Sure enough, I distinctly recall getting the refund.

Wish MS paid a bounty for bugs.  I - unfortunately - find a ton of them, 

This seems one of them, perhaps.


i tested this and it worked for me so I'm curious why it isn't working for you. as i mentioned the image you show does not have the header included on the filter criteria but maybe you tried that too. Can you share a sample sheet so we can try it also?


Demo and video upload below.


Good thing the lost hours I invested drove me to find a better approach.


Yet this is still something I want to get to the bottom of.

If, and - when this works - this will be REAL helpful.

Thanks all,


Ah ha! the problem is the headers of your filters must match the header of the column that filter is applied to. In the attached file rename "Filter1" and "Filter2" to BOTH be "Target" and then it should work. At least it did on my computer.







Fabulous - problem solved - I guess my reading and comprehension skills need practice!

Will continue to use this for the rest of my working life!!



PS - will maybe have to hang around here a bit more.  I prefer old-style telephone tech support, but this first go-round has worked out well.



Oops - just ran this on 120K row dataset - and while it did not look like it was working, after leaving the computer and coming back in 10 minutes, it did the job.

Sure took a while - but I'll be using this along with all the other tools I've learned through the years.

Thanks again!