When using the Data Filter in the newer versions of Excel, you have the ability to search for items that contain some characters. You can then, in one step choose to add the items to the existing filter using the ‘Add Current Selection to filter’ tick box. However it is not as obvious how to ‘REMOVE current selection from data filter’, that is you want to tick the items that need to be REMOVED from your current filtered data set.
Search in the Data Filter
The first step in either case is to type some characters in the search option to find all items that contain your characters.
As shown below, we have typed in ‘XP’ into the search box and below we only see the items that contain the characters ‘XP’. Note that by default they are all ticked. If you want you can tick the box ‘Add Current Selection to filter’. When you click OK, the ticked items will be ADDED to any existing filter you have. If you don’t want all of the items, you can untick some of them and then click on the ‘Add Current Selection to filter’
REMOVE current selection from data filter
To ‘REMOVE current selection from data filter’ you follow the same process, although this time you will search for the items you don’t want to see. So assuming that now we want to remove ‘XP’ from the filter selection you would:
- Type ‘XP’ in the search box
- Untick the ‘Select All Search Results’
- This will remove the ticks from all the found items
- Tick on the ‘Add current selection to filter’.
- Click on OK
This has the effect of unticking those items from the Filter and so performs a remove current selection from data filter.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.