Microsoft Excel Tutorial: How to use Excel's Filter Search Box to remove items from Filter.
Melvin from Orlando shared a great Excel trick with me during yesterday's live Power Excel seminar in Daytona Beach.
To download the workbook from today: Excel Use Filter Search Box to Remove Items from Filter! - Episode 2623 Sample Files - MrExcel Publishing
We all know you can use the Filter drop-down Search Box to find all cells that contain Apple. But what if you want everything that does not contain Apple? Rather than use the old Text Filters for Does Not Contain, you can use this great trick from Melvin:
1. Search for Apple
2. Uncheck Select All Items
3. Check Add Current Selection to Filter
This applies a "Negative" filter to the current filter, removing items.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
#microsoftexcel #microsoft #exceltutorial #excelfilter
Table of Contents
(0:00) Problem Statement: Remove anything that contains Apple from Filter in Excel
(0:15) Search Excel Filter for Apple
(0:30) Filter out anything starting with C
(1:18) Does Not Contain Text Filter
(1:46) Filter out anything starting with B and Apple
(2:02) Remove apple or berry from same column
(2:34) Did you know this?
(2:45) Wrap-up, Subscribe, Like, Ring the Bell
Melvin from Orlando shared a great Excel trick with me during yesterday's live Power Excel seminar in Daytona Beach.
To download the workbook from today: Excel Use Filter Search Box to Remove Items from Filter! - Episode 2623 Sample Files - MrExcel Publishing
We all know you can use the Filter drop-down Search Box to find all cells that contain Apple. But what if you want everything that does not contain Apple? Rather than use the old Text Filters for Does Not Contain, you can use this great trick from Melvin:
1. Search for Apple
2. Uncheck Select All Items
3. Check Add Current Selection to Filter
This applies a "Negative" filter to the current filter, removing items.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
#microsoftexcel #microsoft #exceltutorial #excelfilter
Table of Contents
(0:00) Problem Statement: Remove anything that contains Apple from Filter in Excel
(0:15) Search Excel Filter for Apple
(0:30) Filter out anything starting with C
(1:18) Does Not Contain Text Filter
(1:46) Filter out anything starting with B and Apple
(2:02) Remove apple or berry from same column
(2:34) Did you know this?
(2:45) Wrap-up, Subscribe, Like, Ring the Bell
Transcript of the video:
I am pretty sure this is one that I've never seen before.
Using the Filter Search box to remove items.
Yesterday I was up in Daytona Beach doing a seminar. Melvin from Orlando hit me with this one.
I am pretty sure I've never seen before.
All right, so you all know that if we needed to find all of the apples in this column. We could open the Filter box.
Come here to search. Search for apple.
Select all search results and everything is great.
But what if you wanted to get rid of a whole bunch of things? Like over here, Category.
If I wanted to get rid of all the things that started with C, that would be a lot of un-checking. Now, check this out.
This is Melvin's trick.
I'm going to type in a C, which gets me a long list of C items. I want to uncheck all of these.
So first step is Select All Search Results.
Choose that, which effectively uncheck all of the Cs.
And then to apply these negative check boxes to the current list. Choose "Add Current Selection To Filter".
Click Okay. And look at that.
All of the Cs have disappeared.
Wow, that is awesome.
When the question came up from someone else in the room.
I initially was going to go into the dropdown, Text Filters, Does Not Contain. Or over here in column A.
You'd have to go to the Ttext Filters, Begins With.
And then change it to Does Not Begin With. Which is down here in the list, C.
And Melvin's like, "Oh, no, no, no. There's an easier way," and I have to agree.
It's definitely an easier way.
So let's say we want to get rid of anything that had a B in it.
Uncheck select all search results, add current selection filter, click okay, and the Bs are gone. We'll try the same thing over here with apple.
Uncheck all the apples, add current selection to filter, and the apples have disappeared.
All right, here's another one. I changed up some of the data here.
I want to get rid of everything that has apple or berry.
Open this search for apple.
Use Melvin's trick. Uncheck that, check this.
Okay, then open again.
Search for berry, uncheck all the berries, add current selection to filter, and it should remove.
It does. Look at that.
I really want to know down in the YouTube comments below, did you know this?
Did you know this way to remove items from a filter?
I'm almost sure this is new to me.
Thanks to Melvin for passing that along, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Using the Filter Search box to remove items.
Yesterday I was up in Daytona Beach doing a seminar. Melvin from Orlando hit me with this one.
I am pretty sure I've never seen before.
All right, so you all know that if we needed to find all of the apples in this column. We could open the Filter box.
Come here to search. Search for apple.
Select all search results and everything is great.
But what if you wanted to get rid of a whole bunch of things? Like over here, Category.
If I wanted to get rid of all the things that started with C, that would be a lot of un-checking. Now, check this out.
This is Melvin's trick.
I'm going to type in a C, which gets me a long list of C items. I want to uncheck all of these.
So first step is Select All Search Results.
Choose that, which effectively uncheck all of the Cs.
And then to apply these negative check boxes to the current list. Choose "Add Current Selection To Filter".
Click Okay. And look at that.
All of the Cs have disappeared.
Wow, that is awesome.
When the question came up from someone else in the room.
I initially was going to go into the dropdown, Text Filters, Does Not Contain. Or over here in column A.
You'd have to go to the Ttext Filters, Begins With.
And then change it to Does Not Begin With. Which is down here in the list, C.
And Melvin's like, "Oh, no, no, no. There's an easier way," and I have to agree.
It's definitely an easier way.
So let's say we want to get rid of anything that had a B in it.
Uncheck select all search results, add current selection filter, click okay, and the Bs are gone. We'll try the same thing over here with apple.
Uncheck all the apples, add current selection to filter, and the apples have disappeared.
All right, here's another one. I changed up some of the data here.
I want to get rid of everything that has apple or berry.
Open this search for apple.
Use Melvin's trick. Uncheck that, check this.
Okay, then open again.
Search for berry, uncheck all the berries, add current selection to filter, and it should remove.
It does. Look at that.
I really want to know down in the YouTube comments below, did you know this?
Did you know this way to remove items from a filter?
I'm almost sure this is new to me.
Thanks to Melvin for passing that along, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
