So one of the things you may want to do with a Numbers spreadsheet is to search for certain
items.
For instance, say you've got an inventory here for a used Apple device store and you
want to search and just show the Macs or just show the iPhones here.
Now you can do that using a filter.
If you just want to do this one time you can select the table here, click on Sort and Filter
on the right, Filter, Add a Filter.
Say I want to filter by item and then say Text, Contains, iPhone.
There you go.
You've got it.
But that was a lot of steps.
Let's say you're constantly wanting to do this.
Let's get rid of this filter here and create a really simple interface for doing this.
You don't have to know all this sidebar stuff.
Let's create a new table.
We're just going to create the simplest table type there is with no headers and footers.
I'm going to reduce it to have one cell.
Then I'm going to call this table search.
I'm going to leave that cell blank for now.
Now in this table here I'm going to expand by one column.
Add a column here and call this column search.
In here I'm going to put a formula.
The formula is basically going to go and put a different value in there depending upon
if this term matches what's in item over here.
So we'll go and I'll type equals start typing a formula.
Then I'm going to search and under Text I find the search functions is what we want.
Define function is case sensitive, search function is not.
So we'll use that because we don't have to worry about, you know, capitalizing the P
in iPhone or the M in iMac.
So we'll do search and then you can see the parameters here are Search string and Source
string.
So Search string is going to be this and comma what we search is going to be that, the Source.
Now we want to make sure that as we Copy and Paste this into other rows and columns that
the row and column for what it's searching for is going to be preserved.
In other words we're just going to use this one cell each and every time.
So even when I paste it into the one below here I don't want this one to move down to
the one below.
I want it to stay here.
Notice it gives me a dollar there as the result.
I'm going to change the formatting so it's number formatting.
Just so we can see it clearly there the one is the result.
There's nothing in here and nothing does match the first or the beginning of this.
If I were to type, say, iMac you can see I get one as the result.
But if I type iPhone I get an error as a result because it doesn't find it at all.
Let's leave iPhone in there for now.
I'm going to Copy and then Paste this here.
You can see it puts a one next to every row that has iPhone in this term here.
So it's working.
But I want to filter this.
So the way I'm going to do that is I'm going to do what I did before.
Sort and Filter, Filter, Add a Filter, this time in the Search column, and say Numbers
Greater than or Equal to because that number is going to change if say you're searching
for something that's later in the string.
But it's greater than or equal to one.
Then we're good.
You can see there it filters everything here and is hiding everything that doesn't have
a value greater than or equal to one here.
So if I were to change this to iMac you can see it filters and only gives me the rows
with iMac in it.
If I do just Mac, which is actually going to put two's in the column here for iMac and
one for MacBook, that why I used that equal to or greater than, then I get the results
there.
If I leave it blank it basically is going to give me everything because everything will
match the blank.
The last step here is I just want to Hide this column so it's invisible.
The filtering doesn't care if it's hidden or not.
So now I can search for things.
So if I want to search for the iPhone I can.
If I want to search for iPhone 8 I can do that or MacBook I can.
So I have this cool little functionality here.
I never have to touch the Sort and Filtering stuff again.
I can just use this Search field to quickly search for things.
It could be very useful in a variety of different purposes in Numbers.