Numbers' filtering ability is a powerful way to see and interpret your data, and you can use it in all sorts of ways. Wanna find out how much you spent on March 3? No problem. Need to know how many times you went out to dinner? Piece o' cake. Gotta sort for a particular executive's expenses to scold him for how much he's blown on cufflinks? Well, finding out is easy, at least.
Here's how it works. Click on the table you'd like to filter, and then either pick Table> Show Reorganize Panel or click the Reorganize button in your toolbar.
When the panel opens, you'll note it's divided into three sections—Sort, Filter, and Categories.
Man, that is just the most exciting screenshot in the history of the world.
You can uncollapse any of those by clicking on the disclosure triangle, of course. For this tip, we're going over how to use the Filter ability in particular, but the Sort and Categories features are pretty awesome, too. Sort is one way to change which column your table is organized by, and using Categories, Numbers can automatically break your table out into subsections, as I've done below.
See how Numbers automatically added the “Home,” “Entertainment,” and “Food” categories on the left for me, so I didn't have to organize those myself? Neat! There are other methods for both sorting and categorizing your data in Numbers, but I find this way to be pretty convenient. More than one way to skin a cat, blah blah blah.
Anyhow, let's move on to the thing I actually started to write this tip about before I bore you all to tears. So if you uncollapse the Filter section, you'll see a drop-down menu labeled Choose a column.
Within that menu will be all of the columns you created, and you can select any one of them to filter by.
Now here's why I think this is so great. Pick, say, your Amount column, and you can then further refine your filter with the sort of comparisons that you're probably used to on your Mac, like “greater than,” “less than,” “is between,” and “contains.”
So for example, you could use this to see only the cells that contain amounts that are between $100 and $1000. If you're making an expense breakdown, you could view all transactions that contain the word “dinner.” Or you could filter out everything but a certain person's name, cells that were left blank, and so on. It's really powerful. And here's another nifty thing you can do—once you've got the filter set, it's easy as pie to select the cells you're interested in and see the stats for the numbers within them in the lower-left of the window.
This makes filtering much more useful than a search, as you can see the totals for your filtered rows in an instant. So once you find the transactions for Mister Snooty Executive's collection of solid-gold cufflinks, you can add all of those cells up and know the extent of his wrongdoing.
One last thing—when you're ready to unhide everything that didn't match your filter and go back to your regularly scheduled table, reopen your Reorganize Panel and either uncheck Show rows that match the following or hit Reset.
And you'll be back where you started, ready to go have an uncomfortable conversation with The Man Who Spent Too Much. I'm so sorry.