Creating Searchable Databases In Numbers 2025

MacOS Tips
1 follower
#macOS #MacTips #MacOSTips #AppleTips #MacBook #MacBookTips #Productivity #Workflow #Shortcuts #KeyboardShortcuts #MacShortcuts
AI Generated·May contain errors
Hi, this is Gary with MacMost.com.
Let me show you how to make searchable databases in Numbers.
So let's say you have a relatively simple numbers table like this one.
This just keeps track of expenses.
Everything's got a date, a description, amount, and a category.
Let's say you don't want to view it all at once.
You want to easily be able to search this.
You, of course, can just use Command-F and then search for something.
So for instance, you can search for pub and it will find the word pub here.
But what you'd rather do is have it filter this table to just show you
things that match a search.
So an easy way to do this without actually building anything at all
is to use the filter function.
So let's say I just want to see everything here in category that matches transportation.
So what I can do is select the table and then go to the letters at the top for each column,
click on the context menu button here, and you'll see show filter options and quick filter.
If I choose quick filter, it's going to show me everything it finds in this column
and how many times it finds it.
So for instance, for say transportation, there are 28 here.
I can easily filter everything by unchecking what I don't want to see
or I can click deselect all and then select just transportation.
And now you can see I can easily look at everything that has transportation in the category column.
But of course, this is difficult to work with if you're constantly wanting to filter the table.
So instead, let's build something that allows us to do this easily.
Let's start with the description column here and say we want to search for something
in the description column.
We're going to create a new table and I'm going to choose the simplest type of table
and I'm going to make it a one by one table like this.
And I'm going to call this description search.
And this is where we'll type what we want to see.
So for instance, if we are going to look for charity in the description like that,
we should be able to type that here and then we'll automatically filter this whole table
showing us only things that match this.
So let's make that happen.
To do that, we're going to add another column here and we're going to call this one match
description.
Now, we're going to put a formula in here that's going to test whether the description
here matches what is shown here in this special search field.
So I'm going to press the equals key with this cell selected and start typing my formula.
We're going to look to see if this cell here contains what's in here.
So we're going to look on the right under functions and we're going to look under text
here and find something that will work for that.
The find function seems to do what we want.
It just looks for a search string inside of a source string.
So we'll add find here.
The search string is this.
We'll select it and we want to click it there and then set preserve row, preserve column.
So as we copy and paste this throughout the table, it only refers to this one cell.
It doesn't try to move down relative to where we paste it.
But the source string, what we're searching here, that's going to be this one and that
is going to move down relative to where we paste it.
We don't need to worry about the start position.
That's optional, so we'll just leave that blank.
And we'll click the checkbox here so it couldn't find charity in this one.
We'll copy this cell, double click on the letter here at the top of the column and paste
to paste it in every cell in this column.
And we'll see it does find one here.
It gives a one there as opposed to an error.
So now if we filter this table with just the ones that have a one in them, I can do that
with a quick filter here.
Then I'll just get the ones that match that.
Now if I were to change this to say restaurant like that, then I'm going to get the ones
that just match restaurant.
Now since we're looking for something inside, we don't have to actually match the entire
thing.
I could just match say the first four letters like that and it works or that and it works.
And if it's blank, it will always match which is exactly what we want.
Now that we have this, we can hide this column here so we don't have to see it.
If you find these videos valuable, consider joining the more than 2,000 others that support
MacMost at Patreon.
You get exclusive content, course discounts, and more.
You can read about it at macmost.com slash Patreon.
Now let's add a second search field for category here.
So I'm going to add another table here and reduce this to just one cell like that.
And we'll call this category search like that.
And this is where we'll have the category.
So for instance, if I type food in here, it should match everything that has food in category.
So to get that working, we can do the same thing here.
Let's unhide that column we had there before and we'll add an additional one.
And we'll call this match category.
And then we need to put a formula here.
But instead of searching in the string for this, we want to match exactly.
That in general is pretty easy.
If this cell equals this cell here, and I want to make sure I preserve row, preserve
column for this, then that's it.
Just check to see if it's equals.
And I'm going to get either a true or false value with it.
So I'll copy, double click on the letter and paste.
And you can see true and false values depending upon whether this matches this.
Now we could filter by that as well.
But before we do that, let's modify this a bit.
Instead of just checking for this, I'm going to use or.
And that's a function here.
And the first thing we're going to check is what we were checking before.
But we're also going to check to see if this and again, we'll do preserve row, preserve
column, and if it's equal to blank.
So in other words, if they match or if there's nothing in here, then this will be true.
So now I'm going to copy and paste this throughout.
You got to remember to do that to update all the rest of these.
So now if this has a value in it, it matches it.
But if there's no value in it, then it's equal to blank.
And they're all true.
So now I'm going to make sure I have this table selected here.
And I'm going to go to organize filter.
And I'll see that quick filter I added here for the match description.
It's just looking for if the value is one, we're going to add another filter here.
And we're going to add this for match category.
And the rule here, I can do quick filter as well.
I can also go to sell and say is true.
Both will get me the same thing.
So if the filters are that the description must match one, and the match category is
true, if both of these are true, it matches all filters, then we'll see the result there.
So I can do a search here for say, food.
And then I can do a description search.
And I can search just for GRO for grocery.
And now we'll see food and grocery, both filtered out.
If I clear this out, I'll see only grocery, which will give me the same result.
But if I clear this out here, I could see that say, searching for the letter G,
gets me grocery and gas.
But searching here for only show me food, now just gets me grocery with food.
And I can go and hide these columns here.
Now you may be asking, well, I'd really like to have a pop up menu
for this rather than having to type.
You wouldn't want it for this because we're searching for text inside of text.
So you want to be able to type something that matches a substrate,
like maybe Dr. Copay or Dr. Visit by just typing doctor.
But for category search, we have specific categories that we're following here.
So we could select this cell, go to format, go to cell and change this to pop up menu.
So when we do, we get these items here and we have to actually type in,
like food and transportation, like that.
And then we can select here like this.
And you can tell how easy it is now to search for something.
But if we have a lot of categories here, there's a trick to get them all into this field.
Let's go back and change this just to automatic here.
So it's just text that we type.
I'm going to double click on the letter here to select all of these
and change format cell data format to pop up menu.
So now these are all pop up menus and it populated
with all of the values it finds in all of the selected cells.
That's really handy.
And you may want to leave these as pop up menus,
make it easier to change them or add new ones.
But you could also select one here, copy, go over here and paste.
And that will paste in that pop up menu like this.
And you could see I get all of the different options there.
Plus, I can change this to start with blank.
And now I have an additional item here that's none.
And that allows me to set this to blank and see everything in categories here.
You can then decide whether or not to keep these as pop up menus
or double click to select them all and change it back to automatic or just text for these.
Let's add one more type of search.
And that's for a date range.
So I'm going to add a new table here, going to make it a blank table.
And I'm going to have two cells here.
I'm going to call this date range.
And the first one is going to be the start date.
So let's start with something like 6-1-2024.
And the end will be, say, 8-15-2024, just as an example.
Now, we want to filter out all of these by this date column
according to what fits in between here.
So to do that, we want to add yet another column.
And we'll call this match date.
Here, this formula is going to be a bit more complex.
I'll do equals here.
And then I'm going to have two things I need to match.
If it's on or after this date, and if it's before or on this date.
So I want to do and for two conditions.
And the first one is that this date should be greater than or equal to this date.
The second one should be that this same date here
should be less than or equal to this date.
And then I want to make sure, like before,
that I preserve row, preserve column for both of these, like that.
And now we'll get a result here that gives us true or false.
It matches that.
So if I scroll down here, I can see a bunch of true starting here on the beginning of June.
So now I simply want to add another filter here.
So I'll select the table here.
I'll go to organize filters, and I'll add a new filter this time for match date.
And I'll have it set to if it's true, I'll use cell is true.
And now it's only going to match those that are true.
I will change this to hide this column as well.
All three of my match columns are hidden.
And now I can do things like, for instance, the category, search for housing,
and then only search in the date range between June 1st and August 25th of 2024.
And you can see that's exactly what I get here.
If I were to look in description here and search for, say, R,
you can see it only shows me rent.
So I've got all of these working.
These I can make blank.
In this one, I can set to none.
And this, I could simply just set it to a large date range to show everything.
And I have a pretty comprehensive way to search this database.
You don't have to get as complex as this and use all three of these.
You could just use one for a fairly simple search.
I'll include this example document at the post at macmost.com for you to examine.
Hope you found this useful.
Thanks for watching.
