Single Tables, Filters and Categories in Mac Numbers
Dividing up your data among multiple tables across different sheets can make it difficult to perform calculations and organize. But by using filters or categories you can easily manage your data in a single table. You can also use Pivot Tables in some cases.
Want to know more about how to use Numbers on your Mac? Check out this MacMost course!
Paul: What do you mean by "cap?" Do you mean limit it to a maximum value? You can't do that using categories. You'll need to make your own formula that uses MIN and SUM (or whatever the value function is).
Clint
12 months ago
Hey gary,
I'm using numbers for Icloud and iv'e hit the cap on number of filters. I tried following one of your videos but i cant pull it off. I've gotten as far as functional true/false columns. Can not for the life of me group their use in a single filter. Please help!
Thanks
Clint: Turn off the filters. Forget them for a minute. Now create a new column. Use that column to put a formula in it that includes whatever you want the "filter" to be. So a combination of IF and other things. Your goal is that every column you want to "see" should have a TRUE in it, and every column you want hidden should have a FALSE in it. Once you get that working to your satisfaction, then you an put a filter for TRUE on that one column.
Zaph Mann
1 month ago
Hi Gary - your filtering at 3 ins doesn't seem to work in my spreadsheet - For example I have a column with Countries in it and I create a filter with ANY of the negative options for text (say "is not" & Japan) - it then shows w/o Japan entries, but I ONLY want the Japan entries but when I change to "Text Is" is shows no rows at all??
(what i actually want to do is change the spelling in all the fields in this column but not others - so I thought - select them, then find/replace)
Zaph: Not sure what you are doing there with your filter so it isn't working like you expect. But to change the spelling, do a sort instead. No filter, just a sort. Then you can easily select those cells as they will be grouped together and paste the new value.
Is there a way to cap a SUM in a Categories Summary? I.E. I can cap an individual row, but would like to cap the category SUM.
pws442: do you mean a sum but limited to a maximum value? You can’t do that with Categories but you can just do it using regular formulas.
How do I cap a category summary row value?
Paul: What do you mean by "cap?" Do you mean limit it to a maximum value? You can't do that using categories. You'll need to make your own formula that uses MIN and SUM (or whatever the value function is).
Hey gary,
I'm using numbers for Icloud and iv'e hit the cap on number of filters. I tried following one of your videos but i cant pull it off. I've gotten as far as functional true/false columns. Can not for the life of me group their use in a single filter. Please help!
Thanks
Clint: Turn off the filters. Forget them for a minute. Now create a new column. Use that column to put a formula in it that includes whatever you want the "filter" to be. So a combination of IF and other things. Your goal is that every column you want to "see" should have a TRUE in it, and every column you want hidden should have a FALSE in it. Once you get that working to your satisfaction, then you an put a filter for TRUE on that one column.
Hi Gary - your filtering at 3 ins doesn't seem to work in my spreadsheet - For example I have a column with Countries in it and I create a filter with ANY of the negative options for text (say "is not" & Japan) - it then shows w/o Japan entries, but I ONLY want the Japan entries but when I change to "Text Is" is shows no rows at all??
(what i actually want to do is change the spelling in all the fields in this column but not others - so I thought - select them, then find/replace)
Zaph: Not sure what you are doing there with your filter so it isn't working like you expect. But to change the spelling, do a sort instead. No filter, just a sort. Then you can easily select those cells as they will be grouped together and paste the new value.