Excel - Filter for Excel Records that Does not Contain - Episode 571
1,705 views · Published 29 June 2009 · 1:57 · Indexed 14 November 2026
Channel: MrExcel.com · 2009 · Education
Microsoft Excel Tutorial: Using Excel Filter to Find Values that Do Not Contain. Welcome back to the MrExcel netcast! In today's episode, we have a question from Joe about using custom auto filters in Excel. Joe wants to know how to find all the sales where the code contains the letter O. Let's dive into this topic and see how we can use the AutoFilter feature to achieve this. To start, we have a data set with a code and sales column. We can turn on the AutoFilter by going to Data, Filter, and selecting AutoFilter. Normally, we would be able to choose a single code from the drop-down menu. However, in this case, we want to use a custom filter. So, we select Custom and specify that we want the code to equal *O*. This will give us all the line items with just an O in them. Now, here's a great tip - once we have applied the filter, we can choose the bottom cell and hit the AutoSum button. Instead of putting in a sum, Excel will automatically put in the right syntax to give us the total of just the visible items. But, Joe's question is how to make this show all the items that don't contain an O. In other words, he wants to see the inverse of the filter we just applied. To do this, we go to the Code column, choose Custom, and select "does not contain" from the drop-down menu. This will give us all the entries that do not contain an O. And there you have it! A quick and easy way to use custom auto filters in Excel to find specific data. Thanks for stopping by and don't forget to tune in for our next netcast from MrExcel. If you have any questions or suggestions for future episodes, leave them in the comments below. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ 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! Table of Contents: (00:00) Introduction (00:19) Using Custom Auto Filters (00:30) Applying the Filter (00:49) Using AutoSum with Filters (01:02) Joe's Question (01:12) Finding the Inverse (01:37) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Advanced filtering techniques in Excel using custom filters Excel 2003 vs Excel 2010: Changes in the custom filter options Excel custom auto filters example Excel tips and tricks: Using custom filters for advanced data filtering Excel tutorial: How to use the Custom filter option Filtering data in Excel with custom criteria Finding sales data in Excel using custom auto filters How to filter data in Excel using custom auto filters How to find and display the inverse of a filter in Excel MrExcel netcast: Tips for using custom auto filters in Excel Using custom filters in Excel to find sales with specific criteria YouTube tutorial on using custom auto filters in Excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152485/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919