Mac Numbers Filter Tricks To Make Your Spreadsheets Easy To Use

[@SoNowWhat]

After many years in a corporate environment using Microsoft products, I made the leap to Apple products, including an iMac. I've enjoyed learning the new operating system, and now use mostly Apple's software and apps, but on those increasingly rare occasions that I need to do some spreadsheet work, I still pull up Excel - just seems like too much effort to learn all of the idiosynchronies of Numbers. Might be a different story if I still had to do spreadsheets all day long. But I appreciate your tutorials.

[@natanaelarcano]

Thank you so much Gary for offering your knowledge and expertise to all. This is super helpful as well as all your content on this channel. I really appreciate your support. Blessings... 😇

[@Captain_Awesome42]

mr gary for president, thank you sooo much

[@microphytobenthos]

This is really useful to me Gary, thanks as always!

[@rumpis3332]

NUMBERS!!!

[@mylooooooooooo]

You are a brilliant human being!

[@DWEccles]

As usual - brilliant 👍🏻👏

[@Sarmachus]

Thanks so much for this video, I was really wanting to avoid changing the filter by clicking in organize tab every time.

[@swaha55]

Hi Gary, Is there a way to duplicate a numbers document to save the style and formulas but delete the data all at once instead of having to go manually through each row and delete data which also might delete the formulas?

[@EdwinvandenAkker]

Wow... I think I like _Numbers._ Just the conditional filtering in one line looks a bit chaotic. But in other spreadsheet apps it's even less legible. It should have a more coding indention of some sorts. The fact that multiple tables in one sheet is useful. I should take a look at Numbers. Thanks!

[@Trackhoe075]

Lol yeah this is just to crazy I’ll stick to having to go to the dreaded filters and do it manually! I understand your point but I really don’t find it a big hassle to change filters here and there!!! As another comment suggested this gives me a headache lol

[@MrBgsounds]

Is there a way to request new features. Cant understand why a TABLE couldn't have filters on is by default.

[@AlanGoldberg]

Hi Gary. Always learn some thing from you. Thanks so much One question. I’m curious as to why you use copy and paste instead of fill down when filling a column with a formula. Is it habit because many tables might be taller than your screen size and that’s a convenient way to do it, or is there another reason I’m not getting?

[@01msmr]

I´d say by double-clicking the top row you select automatically the correct rows (minus heading and footing), which lets new rows also be filled.

[@ancientartrevived]

Gary thank you so much for these lessons!! Please can you help with this issue, I'm dying with frustration... Doing exactly this but with a checking account - the filter works great - but when I enter a new row it removes the cell formula. So every time I want to enter a new transaction I have to disable the filter so the cell formula stays intact. Is there no I can fix this? Thank you so much

[@macmost]

You may just have to turn off the filter for it to know what to do with your new row.

[@ancientartrevived]

@@macmost Thank you Gary, I will learn to live with it. It'd be nice if we could create a button shortcut that could be placed within a cell for the "sort now" and "turn on/off filter"... that would be really cool

[@vidshotz]

There's an easier way to do this - SUBTOTAL(109,*) - the Subtotal function allows you to specify SUM_H, which is sum minus hidden values, and is subfunction 109. This changes contextually depending on what is filtered.

[@chrissharp9141]

THANKYOU!!! Brilliant tip, I finally solved how to filter out dates when another filter is active by using SUBTOTAL(105,Range) and SUBTOTAL(104,Range), removed hidden date data in other filters I was using. Notes: The the number in the formulas I used in the actual function were 104=Min-H and 105=Max-H. BTW once formula is entered, you can change the number (say 105) by the dropdown list (arrow down) which includes the description as well. Here is a part of HELP for SUBTOTAL() in Numbers without the examples... The SUBTOTAL function returns a subtotal for a range of cells. SUBTOTAL(function-num, range, range…) function-num: The function to use for the subtotal. “AVERAGE” (1): Uses the AVERAGE function. COUNT (2): Uses the COUNT function. COUNTA (3): Uses the COUNTA function. MAX (4): Uses the MAX function. MIN (5): Uses the MIN function. PRODUCT (6): Uses the PRODUCT function. STDEV (7): Uses the STDEV function. STDEVP (8): Uses the STDEVP function. SUM (9): Uses the SUM function. VAR (10): Uses the VAR function. VARP (11): Uses the VARP function. AVERAGE_H (101): Uses the AVERAGE function and skips hidden rows. COUNT_H (102): Uses the COUNT function and skips hidden rows. COUNTA_H (103): Uses the COUNTA function and skips hidden rows. MAX_H (104): Uses the MAX function and skips hidden rows. MIN_H (105): Uses the MIN function and skips hidden rows. PRODUCT_H (106): Uses the PRODUCT function and skips hidden rows. STDEV_H (107): Uses the STDEV function and skips hidden rows. STDEVP_H (108): Uses the STDEVP function and skips hidden rows. SUM_H (109): Uses the SUM function and skips hidden rows. VAR_H (110): Uses the VAR function and skips hidden rows. VARP_H (111): Uses the VARP function and skips hidden rows. range: The range of cells for which to calculate a subtotal. range…: Optionally include one or more additional ranges of cells for which to calculate a subtotal. NOTES: You can skip hidden rows by adding a prefix of 10. For example: AVERAGE (1): Uses the AVERAGE function. or AVERAGE_H (101): Uses the AVERAGE function and skips hidden rows. Hidden columns never affect the value of the SUBTOTAL. If SUBTOTAL(function-num, range, range…) includes other SUBTOTAL formulas, they are ignored.

[@3730enrique]

Thank you for your nice video, like many others. Let me say something. I don't know why but in my version of Numbers (12.2.1) and under Ventura the AND formula presented here only works if I write ; (semicolon) after the TEXT EXPRESSION. I mean AND( Table::$A$1 =“Top Goals”;J2), for example. In the video you write ..."Top Goals", J2 (with a comma). I Also need to write semicolon between every consecutive AND formulas, right after the final parenthesis Any explanation?

[@macmost]

It id regional. In the US, the formatting uses a comma.

[@weldo1948]

Great video that made me to decide to join Patreon. I created a similar data sheet for weight, BP, and blood glucose. I made a pop-up menu with filer columns, which filter 7 day, 30 day, 90 day and all days. All of the filters work great from the pop-up menu. My problem is that the filter column that evaluates the other filter columns does not copy the formula when I add another line of data. All of the other cell’s formulas do update to the next row. Am I missing something? I would appreciate it if anyone can help!

[@macmost]

I think that since it is a cell with data, not a formula, it doesn't self-populate. You have to copy and paste. Instead of using a pop-up menu, consider just making it a normal text cell. It will still use the autocomplete from the previous rows. So typing "7" should bring up "7 Day" and it is almost as easy as a pop-up.

[@weldo1948]

Thank you for the suggestion. I will try it out with a test sheet. I’ve also thought about utilizing pivot tables after seeing that video. I have learned many tips and shortcuts to improve and quicken my entries that frees up more time for evaluation.

[@mickymahler2785]

Thank you sooo much!! This Video was very helpful. But know I'm a little stuck. What would be, if I needed two cells to be confederated for TRUE. not only B2="Water" but B2 and C2

[@macmost]

Sorry, I don't know what you mean by "confederated for TRUE." Do you mean you want to check to see if both B2 and C2 are "water"? Use a boolean function for that. So in D2 you may have AND(B2="water",C2="water")

[@mickymahler2785]

@@macmost where ever confederateed came from. Sorry. Not quite. C2 will be empty in some row. But if it contains „water“ regardless of what its showing in b2, it shall be TRUE

[@macmost]

@@mickymahler2785 Sorry, I don't understand. "confederated" is not a word that makes sense here.

[@mickymahler2785]

@@macmost i need the row D2 to show true aslong as one of the rows B or C contain water. Sometimes colum C is not filled.

[@macmost]

@@mickymahler2785 So what you want then is OR (you use the word "or" right in your comment). =OR(B2="water",C2="water")

[@justkailash]

Life saving trick for me... i am building a Project Management system and this trick is very useful to add a feature in that... the only problem i am facing with this.. i prepared the sheet and added this filters.. now i want to keep adding more Rows but the Filter/Search column where we can value for TRUE/FALSE is not following that formula in new rows... i can see a blue triangle at the corner of cell and it is saying "The formula was replace with last calculated cell" something like this.. how to solve this problem... i am also sending the query to Macmost Thank you

[@macmost]

Turn off the filters before adding new rows. Then turn them on again to organize.

[@rodrigosalas3687]

@@macmost is there anyway around this issue? or any other way(formulas) to achieve the same outcome without using filters? Thanks in advance and great videos!

[@macmost]

@@rodrigosalas3687 What outcome? Hard to suggest something without more information. Ask at macmost.com/ask with details and I'll try to help.

[@rezendenetto]

How to sort table data automatically from first to last top 5 after filtering it?

[@macmost]

If it is already sorted, then filtering just removes rows. So sort first, or sort after, either way.

[@JohnB53]

I thought that DATEDIF means DATE DIF(ference) rather than DATED IF

[@antoniagia6482]

Potresi fare i tutorial in ITALIANO? Grazie

[@geoffreywalker7308]

My head hurts. Lol

[@ryanmooncat3949]

Typing this before watching the video: I think my life is about to change.....

[@iJammer007]

This just confirms to me that filtering in Numbers is a non-starter. This is the least intuitive product Apple has made.

[@mikelodeon09]

I'm sorry to say this, but EXCEL is immensely EASIER and way LESS complicated to use! Actually, what you did in this video was to show how insert fórmulas NOT filters.

[@macmost]

It often depends on what you are used to. Once you use Numbers a lot you get very frustrated with Excel since you can't use Tables like in Numbers. This video shows filter tricks, and some of them do rely on formulas.

[@samyswry7497]

you make it very complected why not show similar to excel instead true or false

[@VelvetBee67]

so lost