Joe asks about using custom autofilters with Excel. He can use a wildcard to find all of the cells that contain *abc*, but how about finding cells that do not contain abc? Episode 571 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sent in by Joe.
Joe was asking about using custom auto filters.
Let's run through a quick example here, I have a data set with a code and some sales and I want to find all of the sales, where the code contains the letter O.
So, we use Data, Filter, AutoFilter, to turn on the auto filter.
And normally, we would be able to pick a single code from here.
But, instead we're gonna use Custom, and say that we want, where the Code, Equals, *O*, click OK.
And sure enough, it gives me all the line items with just an O in them.
Now, an interesting thing is once I've applied a filter, I can choose the bottom cell and hit the AutoSum button and instead of putting in a sum, Excel will put in the right syntax, that will give me the total of just the visible items.
That's a great tip in itself.
Now, here's Joe's question.
He says well that works fine.
But, how do I make this show me all the things that don't contain a no?
I want to see basically, the inverse of this.
All the items that aren't selected and what we're going to do is, we're going to go to the Code and choose Custom.
And it's interesting that before I had asked for everything an equals *O* and Excel 2003 automatically change that to be contains.
Well, if we look through this drop down, you'll see that we also have an option for does not contain.
So, basically we ask for every entry that does not contain an O, and click OK.
And Sure enough, Excel gives us the inverse.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sent in by Joe.
Joe was asking about using custom auto filters.
Let's run through a quick example here, I have a data set with a code and some sales and I want to find all of the sales, where the code contains the letter O.
So, we use Data, Filter, AutoFilter, to turn on the auto filter.
And normally, we would be able to pick a single code from here.
But, instead we're gonna use Custom, and say that we want, where the Code, Equals, *O*, click OK.
And sure enough, it gives me all the line items with just an O in them.
Now, an interesting thing is once I've applied a filter, I can choose the bottom cell and hit the AutoSum button and instead of putting in a sum, Excel will put in the right syntax, that will give me the total of just the visible items.
That's a great tip in itself.
Now, here's Joe's question.
He says well that works fine.
But, how do I make this show me all the things that don't contain a no?
I want to see basically, the inverse of this.
All the items that aren't selected and what we're going to do is, we're going to go to the Code and choose Custom.
And it's interesting that before I had asked for everything an equals *O* and Excel 2003 automatically change that to be contains.
Well, if we look through this drop down, you'll see that we also have an option for does not contain.
So, basically we ask for every entry that does not contain an O, and click OK.
And Sure enough, Excel gives us the inverse.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
