Forum Discussion
Advance Filter
Hi, how to do an Except filter in Excel?
I have a huge file with more than 50 columns, 5000 of rows.
I would like to do filter almost everything except certain data.
Please anyone able to shed me a light.
Thank you in advance.
4 Replies
- Or if you want to include everything except a couple of item the quickest way would be to turn the data into a Table (using Ctrl + T) and then just unchecking the items you don't want in the filter boxes?
Thank you Wyn Hopkins , however this method is time consuming process. Because there is thousand of items which thousand of rows. If missed one or two, after click OK, meaning we need to re-do the process once again.
- Hi
Can you clarify why you would need to re-do the process? If you miss unchecking one item you can go back in and un-check it.
Another option would be to use Power Query. You set up a table containing the columns and elements you want to exclude and then do an Anti-Join merge just create a table just containing the items you want to keep.
If this is of interest and if you can give me some example data I could mock something up. You would need Excel 2016 or later on Windows for this to work.
- I'd probably add a helper column with IF statements in there resulting in 1 or 0, then just filter that column on the 1s
