Mac Numbers Filter Tricks To Make Your Spreadsheets Easy To Use

Hi, this is Gary with MacMost.com. Let's take a look at some advanced filtering tips that will make your Numbers spreadsheets easier to use.

MacMost is brought to you thanks to a great group of more than 750 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts.

Now here's an example spreadsheet. This really could be for anything. This could be orders, it could be products, it could be DVD's you own, it could be anything. I'm just going to use a little database here tracking players on a sports team. Let's say I need to filter this table occasionally based on things like player age, the number of goals they scored, and maybe overall performance. Now I can select the table here and go to Organize, Filter and add filters. But I'm kind of limited on what I can do. 

For instance there's no way to filter by their age. If I want to look at the top players and not just the players who have scored a certain number of goals or higher then I can't really do it with just a simple filter. So I'll need to create some more columns to have those advanced options. But even once I do that it's going to be a matter of always going to Organize, Filter and changing all of the filters here to make it do what I want. But I can use some tricks to make it this easier so I can quickly and easily go between the different filters that I need.

So first let's create some more data columns. Now these columns were create we can actually hide when we're done. First let's create one for Age. So it's going to calculate the age based on their birthday which is in column B. So in order to do that we can use the DATEDIF function and that will take first a value, like this, and then compare it to another value. So let's use Now. Then we have to ask for something like Y, for years. So when I do that I'm going to get the age in years. Now I can Copy this. I'll double click on the column heading here so I select all of the non-header cells, and Paste. Now I have everybody's age. So now we can filter by that.

The next thing we want is we don't want necessarily the number of goals they've scored, but their ranking. Who's the top scorer, the second, the third, and all. So let's go and create a Goals Rank here and I'm going to use the RANK function for this. So I can do Rank and then I could select the value, comma and then the set to use for ranking. So I'm going to click on column C. This will give me the rank here. In this case this player here is 6th in goals. I can Copy this and Paste it throughout here and quickly see, ah yeah this player here at 20 is first in goals. This one with 17 is 4th in goals.

Ranking will even group things together properly. So if there's a tie for 3rd place you would see two number 3's and then a number 5. 

Now let's do the same thing for overall performance and we'll base that on goals plus assists. So first let's create a Goals + Assists and just do a simple formula that just adds the two of those together. I'll spread that around here. Let's shrink some of these columns down so we have more room. Let's add a few more. So let's do Goals + Assists Rank and we'll do the same ranking formula that we did there but using Goals + Assists. So Rank and the value there from the entire set and now we'll Copy and Paste that throughout. So now we can see who the top ranking player is. It's number one here with 20 goals and 18 assists for a total of 38.

So now we want to filter based on these things. Now I could create a filter looking for everybody that is 15 or older and add that to the filters. I could look for a Rank of 3 or less for goals and for goals plus assists. I don't want to have to go and mess with the filters every time I want to change the view. So instead I'm going to create a bunch of columns that will have True and False values as to whether or not that player should be included in a filter. 

First let's start off with age. I'll create one that is 15, so 15 and older here. I'm going to do a simple formula here. I'm simply going to go and say, is age greater than or equal to 15. You could see for this first player that's True. I'll Copy that and Paste that throughout. You could see True and False here based on their age. Let's do the same thing for Rank. We'll say Top Goals and I'll do a formula here for Goals Rank is less than or equal to 3. In other words is in the top 3. I'll paste that throughout as well. So you can see this player here is in the top 3 and so is this one. Then let's do the same thing for Goals + Assists. Goals + Assists Rank is less than or equal to 3. I'll Copy and Paste that throughout. So now we have the top players here.

Now I could actually just filter on these three columns whether or not they're true. If there's a True in this column then include them in the filter. If there's a True in this column include them in a filter. But I want to do this in a clever way so I don't have to go to Organize, Filters and change everything here. So I'm going to create another table. I'm just going to create a simple table here and shrink it down to one cell. Then I'm going to move it here to the top. Let's call this table Filter. We'll put something in here like older than 15. Now we'll create one more column here. This will be our Filter Column. In here we can check what the filter is and grab the appropriate value from here. 

So, for instance, I can use the AND function to group these together. So AND and we'll look at this value here, is equal to Older Than 15. Also this value is True. Then put a TRUE here. Let me go and change this so it's Absolute Value so it always is this cell no matter where I paste this formula. Now if I Copy and Paste this throughout notice that because this has older than 15 that the True's and False's match in the filter column and is 15.

But what happens if we want to do something like Top Goals. Now you could see they are all False here because there's no condition that matches this. The AND here is just looking for older than 15 and then checking to see if that's true. Let's create two possibilities. I'm going to do that using OR. The first part of the OR is going to be that initial AND. The second one is going to be another AND and this time we're going to look for whether this is equal to Top Goals. If it is also look at the value for here. Close up that AND then close up the OR there. Let's also remember to set this to Absolute. Now I'm going to take that and spread it through the entire thing. So now you could see because this is Top Goals, these True's and False's match the Top Goals, not is 15. But if I were to change this to Older Than 15 you could see all of these change to match this column instead. 

We could do the same thing for Top Players. Let's add another part to the OR here. Another AND inside the OR. Say IF this is equal to Top Players AND also this value is True then we close that AND and we close the OR and now we have a third thing we can switch it to. Let's remember to change this to Preserve Row, Preserve Column here. I can Copy this and Paste it throughout again. 

Let's add one more though. I'm going to make it so that we can also enter NONE in here and there will be no filter. So in that case they would all just be True. So instead of an AND there, because we're only looking for one thing, we can just say IF this, and I'm going to make it equal to NONE, then that's it. No reason to use an AND here because we're only doing one thing. Let's make sure Preserve Row, Preserve Column. I'll Copy and Paste that throughout and now if I change this to NONE you could see they are all True.

So now what I can do is I can make the filter based on this column here. So I'll go to Organize, Filter here and Add a Filter for the Filter Column. I'll simply set it to Text IS and then True. So now that this is set to NONE all of these are True and we see everything. But if I were to go here and change to Older Than 15 you could see that it now only shows me players who are 15 and older. If I change to Top Goals you could see it shows me those that are ranked 1st, 2nd, and 3rd. In this case there is a tie for 1st place for the number of goals scored. 

The next thing I want to do is make it easier. Instead of typing this out I want to be able to select one of these things that's in here, like Older than 15, Top Goals, Top Players, and also None. So let's change this cell here to make it a Pop-up and I'll add all of these items. So Older Than 15, Top Players, None. Let's arrange them in an order that makes more sense. Like that. Now instead of having to type I can click the Menu here and I can go to Older Than 15 and you can see it changes there. I can go to Top Players and I can go to None. Now you see everything which is useful for changing the data.

I can even add more to this. Let's go in here and change this to add one more. I'm actually going to select this here and Copy it and Paste it in as another one making sure I've got a comma before and after that AND statement. This is going to be Younger than 15. In this case instead of looking at the value of is 15 I want to look at the opposite of that. So Not and put the Not function around this. So Not True here. So younger than 15 is my new one. I need to Copy and Paste that throughout. In here I'll add another option and I'll move that up here.

Now I can go in here and say Older Than 15, which is a little misnamed but you get the idea, and then younger than 15 and it will show me everybody here is 15 is set to False. Now remember this is actually looking for the age is greater than or equal to 15. So the opposite of that, the Not of that is less than 15. 

So to wrap this up I would probably want to Hide as many columns as I don't want to see. Now it may be useful to see Age here. It may also be useful to see Goals Rank, and Goals + Assists, and Goals + Assists Rank. But I don't need too see these. So I can select them All and then go to Table, then Hide 4 columns. Just because they are hidden doesn't mean that the things won't work. I can still go here and choose different things and the sorting will all work. If I ever want to Unhide these I can just select any cell in the table, go to Table and then Unhide all Columns to bring them back. Make changes and then Hide them again.

So there were a lot of different techniques packed in here. All making this Table a lot more useful than just the raw data that's in there.