Mac Numbers: New Filter Options

Numbers version 11.2 adds the ability to filter by unique values, fin duplicates and distinct values. Quick filters are also improved with a more comprehensive checkbox system. Learn how to search for duplicates that include several columns with a function.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at the new filtering options in Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So Numbers version 11.2 gained some new filtering options that could be very useful. First let's take a look at Quick Filtering. So Quick Filtering was something that was around before but was a little difficult to use. You have a table like this and you could simply click here and then choose Quick Filter. Now previously the menu would simply list the different names. You could select them one at a time. So you could keep going in and reselecting them to get the group that you wanted. But now with this new interface it makes it easy to select exactly what you want. You could see I've got a lot of names here. I could check or uncheck various names to remove them. I could also just Deselect All and then check the ones that I want to add. I could also Search here. So I could say Deselect All like I did before and then type a letter and then it would come up with search results and I could easily add those one by one or select them all. So it becomes really easy to select the exact group that you want. 
Now you could also access those in the Organize Menu here. If you go to Organize Filter and you add a filter you could add one for that column and then you'll get the option here before you add any other filters to use Quick Filter. It's the same basic interface here. I could check and uncheck ones I want. I could Search. I could Deselect All. I can Select All to get the ones that I want. Then if you've set something up like this it's easy to turn the filter On or Off. 
Now in addition to that regular filters have some useful and new options. First you'll notice the whole new look here. So when I want to use Filters it's a nice new look here with the ability to select the filters that you want. Most of the options you'll see here are basically the same. But you'll notice the Blank/Not Blank options are gone. Instead you've got this new category called Cell. This could be used for the same thing. Blank/Not Blank. But there are a few other things here. If you go down the list you'll see  Is Checked/Is Not Checked. Is True. Is False. So a little easier to get those options. But in the middle you have a bunch of new options that weren't really possible before. You would have had to do these yourself by adding new columns using the functions to figure out if something was a duplicate or if it was unique. Then filter based on that column. But now you just do them as a simple filter. So, for instance, I could select Is Unique and you could see it removes an item there.  There's one that's not unique. I could change this to Is a Duplicate. Now you can see it will show me that there is indeed a duplicate here. In addition you'll also have the option to choose Is Distinct. So this will show one of everything. It just won't show the second copy of something. 
Note that you can combine Is Duplicate and add Is Distinct to have it show simply one of every duplicate. That way you could easily select all of those rows and then easily remove those duplicate rows. If you ever needed to actually find truly duplicate rows you could add another column here and then simply concatenate a bunch of data. So I could do equals to start a formula and then in this cell ampersand, ampersand this cell, ampersand this cell. If I wanted to get a little bit more accurate I could put quotes with something like a dash in here like that. So instead of just an ampersand just putting them all together it's actually going to put a dash between each one of those making it less likely that there could be a mistake. So then you get a column like this. I could select and paste it all into here and I could do Unique as the name of that column. Then go to Organize Filter. Add a filter on Unique and then I could say I'm looking for unique values, duplicate values, or is distinct. So in this case there will be no duplicate since that duplicate row didn't actually have survey responses that matched. But if I turn the filter off and actually make a true duplicate like this one here and I'll paste it over here so you can see row 7 and row 10 are identical, at least for the things I want to match for, name and the three questions. Not the date. Now when I turn the filters on you could see it does find that these two entrees have exactly the same name and responses here. These cells match.
So I hope you found this useful. Thanks for watching.

Comments: 6 Responses to “Mac Numbers: New Filter Options”

Michael Miles

3 years ago

Looking for app at i can use on my MacBook pro, ipad pro and or iPhone to track my bills and spending each month and tips on how to use the app.

Gary Rosenzweig

Michael: You could always create a spreadsheet for that if you like. There is even a template. If you search for "budget" in the App Store, a few apps appear but I've never used them.

nick

hi Gary, I meant to post this in the Database video but missed the opportunity. I've used Excel forever and have tried to start using Numbers but still not comfortable with a few features, one is the way Numbers highlights the rows and column headings. If I have a table with column headings but no row headings, would I leave the highlighted row headings blank? thx

Nick: If you don't want to use a header row, you can just remove it.

Grant

Hi Gary,
Great overview of the new filters. My challenge: I have a Numbers file of 300+ people invited to an event. I now need to contact those who haven't yet registered. I have a separate Numbers file with those who have registered. I combined it with the full list and then Filtered for Duplicates to identify which people on the full list who have registered. I need a quick way to remove them so I'm left with the only people who haven't registered. Any suggestions?

Grant: Use Is Unique instead of Is Duplicate.

Comments Closed.

Mac Numbers: New Filter Options

Comments: 6 Responses to “Mac Numbers: New Filter Options”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information