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.

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!

    JC
    6 years ago

    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?

    6 years ago

    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
    6 years ago

    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?

    6 years ago

    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
    6 years ago

    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?

    6 years ago

    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
    6 years ago

    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?

    6 years ago

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

Comments Closed.