Creating Searchable Databases In Numbers

If you have a large Numbers table and regularly search and filter it, you may want to use a formula and a filter to enable quick and easy searches instead. The technique involves creating a hidden column that will use a single-cell second table to search, and then a filter based on the results in that column.

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

Video Transcript: 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.

Comments: 9 Responses to “Creating Searchable Databases In Numbers”

Jim S.

6 years ago

Excellent tip! Filtering a 2000 row spreadsheet for my checkbook. I added a second filter on the same spreadsheet so I could filter by a "Description" and/or a "Category" column. Life is good! Thanks!

Great tip!! There is one thing I didn't get in your video, although I viewed it a few times. At approx 3:41, you get values of "2" in the first 2 rows. Can you explain why?

Gary Rosenzweig

JC: The SEARCH function returns the position of the matching text. At 3:41 I am using the search term "Mac." Those first two rows have "iMac" in the text. "Mac" matches "iMac" starting at the 2nd letter. The other rows have "MacBook" so "Mac" matches "MacBook" at the 1st letter. Thus 2 and 1.

Ian Leckie

I never knew you could do this, and I´ve been using Numbers for years! Excellent! I duplicated your example table (13 rows of data) to play around with it. But if I delete what is in the “Search” field in order to show all 13 rows again everything is “locked” and I just can´t find any way of adding new rows, which I´d have to be able to do in my banking spreadsheet so as to enter new data. Removing the filter frees up the rows again, but is there any other way of doing this?

Ian: Good point. You can simply switch the filter off temporarily (checkmark next to it in the right sidebar) so that it is easy to switch it on again instead of removing it and adding it again. It doesn't really "lock" the table, it just makes it difficult to enter in new data since creating a new row won't show it. I suppose you could experiment with adding a condition to the filter that allows the search condition and also a blank cell.

Gene H

Great tip! I've added this to a large spreadsheet. In the new table (search string) I've formatted as a Pop Up Menu and added a few search terms. Next to the Pop Up I have a CountIf formula to sum the total number of the search word. I also turned the Filter off. This works great as I can easily switch search terms & see the total. However, when the Pop Up menu is blank the CountIF cell will total all cells with an entry. Anyway to add an item to the Pop Up so that the CountIF count is blank?

Gene: Why not add an entry like "Click To Search" or "Blank" or anything that never appears in the list? Then the result should be 0.

Emmanuel Scerri

Interesting. The FIND function (apple f) helps me when I'm looking through the selected sheet. Do you know how to simultaneously search through multiple sheets in a numbers document?

Emmanuel: Command+F will find text or a number in all tables and all sheets.

Comments Closed.

Creating Searchable Databases In Numbers

Comments: 9 Responses to “Creating Searchable Databases In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information