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

Watch on YouTube

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