I am going to make time each week to study these videos so that I can seriously improve my proficiency with Numbers. EXCELLENT video and information, yet again!
I learn! Every time! You are amazing. Love to see a video on drawing in pages. Particularly using the line tool to connect blocks, and routing of lines that are connected
Very good tips. The ones I use most are rules for dates. Those are very handy when making an event log. Use it frequently in my amateur radio activities.
Thank you Gary, Learned a lot from this tutorial! But how can I change the color of a cell in a colum, depending of a checkbox (true or false) which is in another column?
I had been looking for a while to find the equivalent to Excels absolute and relative cell references in numbers and it looks like the preserve row and columns seems to do that for me. thanks
Gary, this video is packed with info. Can you perform a function based on the color text in a cell? In other words, let's say you have a column of red and black numbers. How can you sum all the red numbers and likewise the black numbers? I played around with conditional highlighting unsuccessfully. Thanks
No, functions can't see the cell styling, only the value. But you should be able to find another way to do what you want, like having a black/red value in another column.
love your post! What if I have a gig price column and a paid column, and if the date column is not filled in at the present date, can we make the price turn red? thank you! Oops your last explanation with hiding a column might be a way, thank you!
Hello, so i have a registration sheet for my hockey schools, how can I create a second table that would tell me how many registrations I would have in different camps, exemple I have a shooting camp chosen, and I want to have a second table that would tell me how many are registered for that specific program. Thank you so mush for your advice my friend! Love your videos
great video. Is it possible to HIDE rows based on a condition? I don't want to lose the information, but I don't need to see it regularly.... If Column O = 'Yes' then hide the row...
I know you can do alternating row highlights, but can you expand on that and do repeating rows with different colors so if you are constantly looking back-and-forth from the spreadsheet to real life that you don’t lose your place as easily?
Your content is outstanding in every way Gary! I came here looking for a particular kind of conditional highlighting formula and I'm wondering if Numbers allows for it. I would like that if I enter a dollar amount into a particular cell, that if it is NOT a multiple of 5¢ ($0.05) that it will change text color or highlighting or both. I'm not stuck on how to choose the text color or highlighting, but I AM stuck on how to create that formula. The options in the conditional formatting do not directly offer the option of "if not a multiple of". I hope I explained my question clearly. Thank you in advance for your wonderful help!
No, there is no "not a multiple of" condition. So you'll need to use something else. Have another column with a formula using an IF statement and MOD to put a character (like an arrow or X) if the MOD of that other cell and 0.05 is not 0.
Again a very helpful video. I have some trouble withe csv-format to analyse i.e. some blood pressure values in an app. May be you still have prepared a video or may be you can explain to import and prepare a diagram.
Even if I did a video on CSV files, it probably wouldn't address the exact problem you are having, whatever that is. You'll need to work at it to figure out what is going on.
@@gerdt.7106 Not sure what you are asking. What is it you want to DO with a CSV file, exactly? Import it into Numbers? Export it? That's pretty much it. Once it is in Numbers, it is a Numbers file. So maybe you aren't really asking about "CSV files" but how to do something with data in a spreadsheet? Ask, with specifics, at macmost.com/ask
No, you wouldn't count the highlights but you would count the number of cells that meet the same condition as you are using to highlight them. So if the condition is to highlight the cells when they are >100 then use COUNTIF to test >100 for those cells.
Conditional Highlighting works by comparing the value in a cell. If you want to calculate something first, then do that in a column and use the Conditional Highlighting on that column's cells instead.
Hi Gary...HELP! I have a table to track participant attendance at a 5-session workshop. I'm using checkboxes to track attendance for each date. I need to indicate either "Completion" for students who completed 4 of 5 sessions or "Participation" for those who complete at least 2 of 5 sessions, based on the number of checkboxes checked. How do I do this?
In another column, use COUNTIF to see how many are checked. So if B2, C2, D2 are checkboxes, in E2 you could use COUNTIF(B2:D2,TRUE) and get the number of checks. Then lots of ways to turn that into a value. For instance, with CHOOSE: CHOOSE(COUNTIF(B2:D2,TRUE)+1, "Fewer than 2 checked", "Fewer than 2 checked", "2 Checked", "All Checked") I added +1 to the COUNTIF so a value of 0 is 1, 1 is 2, etc., for easier lookup in CHOOSE. But you can also use some nested IF functions.
Not easily. I've never seen that format. Is that from something you are importing? You can use functions to simply take characters 1-2, characters 4-5 and characters 7-10 and plug them into a DATE and get what you want though.
I am looking for a way to color the "TAB name/Worksheet name" in Mac Numbers application. This should just be so simple but I can't seems to find the option. In Excel all I have to do is right click and select color. Any suggestions?
There's no way to style the sheet name tabs at the top. What's your goal here? Can maybe putting some heading text at the top of the sheet work instead? Or maybe adding a color emoji character to the sheet name work?
still can't find how to grade a group of number cells. with red for the lowest and green highest, yellow and orange in the middle. the rules only apply to the individual cell and not as a group.
That's a pretty complex thing to need. You'd need to experiment with extra columns and using RANK, maybe the RANK of the RANK, to get something like that. Will take some real spreadsheet skills.
Hello, I have a small question, hope you will answer :< My question is: does the code that I convert from Watch me do into RunAppleScript will be (slightly) different from a Macbook to another of a different version? When I send my Automator code to my colleagues with different Macs, the code never works and runs into error. Hope you can help me with this. Thank you
Hard to say. Watch Me Do usually involves clicking on specific spots on the screen, so it will only work right if the elements on the screen are exactly the same as when you created it.
I want to highlight if cell B2 is Not equal to C2+D2. It doesn't seem to be possible without adding a new column, but it is possible in Excel conditional formatting 😥
@@macmost sorry, I realize now that was very vague. No. I have a balance book on a sheet and would like to see alternating colors where the first of the month is one shade, the second would be the other, the third would alternate back and so on since I have multiple transactions per day. Conditional highlighting for when the date changes.
@@Rerdeleon0810 Easy to do on the date cell itself (conditional highlighting), very hard to do for the whole row. See ruclips.net/video/8U9ubBGsue8/видео.html or maybe consider an alternative strategy.
Not the right tool for that. You'll need a third column with a formula in all of the cells that uses LOOKUP to search all of column A for the same name as in the cell in column B.
@@Don-gm2um Hard to say without knowing the function name. But if you wanted to count unique values you can do some clever things with a new column and using COUNTIF to compare each cell's value to the values of all of the cells below it, then add up the 0s that appear (0 would represent no match below it).
Do you mean passwords? If you are using iCloud Keychain then you can access them on your other Apple devices as well in Safari, System Preferences or Settings on iOS. If you are creating special entries in your Keychain outside of iCloud then those would only be on that Mac.
I can't find any video for highlights entire rows non- conditional. In other words I just need to highlight an entire row randomly as I examine a numbers report .
So not a conditional highlight, just highlight the row temporarily while you examine it? Just click on the row number on the left side. The whole row will now be outlined and easy to read across. The best part is you can then use the up and down arrows to move the row selection. If you need more, you can always go to the Format sidebar, then Cell, then change the fill to something. Then change it back (or undo) when you are done.
I'm new to numbers and i wanted to know if there was a way to add 4 different colors to one cell ? I play the lotto and i want to visually keep track of wins. So i place a W on each cell and ideally id like to add a color behind each W. Is that possible ? Also, How can i add up the W's at the bottom of each column ?
Not sure what you mean. You can assign a different background color to each cell as you like. As for adding up cells that have a "W" in them, you would use COUNTIF for that. See ruclips.net/video/vkYm6jx7OaY/видео.html
@@macmost I get that but is it possible to use 4 different colors per cell ? For example Texas has four drawings every day. I’d like to utilize that by showing a different color for each one
@@tinaharlow4508 this is for the Numbers app for Mac. There is no numbers app for PC. Are using the web version? That doesn’t have all of the features of the full proper app.
@@macmost oh ok, I use the app on my iphone but editing it seems easier on my computer so I emailed myself the link :) Do you know if I use it on an Ipad if it has all of the features?
I only give you a 99 for this tutorial. Why ? You didn't address, although you came close, to the boundary condition I would test against, that is more than one rule that is satisfied. Which rule wins ? First, last ? For instance if a >= and a
@@macmost But I'm offended that I have to do the test, and I did, and it seems that processing stops when it hits the first rule that is satisfied. I'm supposed to be the dumb RUclipse and you the king of the geeks RUclipsr :) - perhaps I should un-retire and become an Apple support person - seems like an easy job ..... for someone playing with UNIX since the early 1970's ....
Gary is always clear and concise in his explanations. Excellent video as usual.
The preserving row/column advice was exactly what I was looking for. I had been going cell by cell (eye-roll). Thanks
I am going to make time each week to study these videos so that I can seriously improve my proficiency with Numbers. EXCELLENT video and information, yet again!
This just made organizing asset inventory by IP range so much more simple. thank you
Been needing this exact type of info for my work. Thanks!!!
A great use for numbers! Thanks, Gary!
I learn! Every time! You are amazing. Love to see a video on drawing in pages. Particularly using the line tool to connect blocks, and routing of lines that are connected
ruclips.net/video/BenoectLpig/видео.html
Very good tips. The ones I use most are rules for dates. Those are very handy when making an event log. Use it frequently in my amateur radio activities.
FANTASTIC! Gary, you always make my work so much easier!!! Thank you!
Great tutorial!
Thanks Gary 😊
I used an IF combined with Isblank…thanks for showing me how to begin
A very useful and informative video tutorial today! I’m always learning something new. Thank you, Gary! ❤️👏🏻
Yes! Good Apple week! Thanks
Excellent video, as always, Gary! I love conditional formatting, and you've explained it very clearly. Thanks!
Thank you Gary, Learned a lot from this tutorial! But how can I change the color of a cell in a colum, depending of a checkbox (true or false) which is in another column?
Thank you!
Great video. Nicely paced so I could understand. Excellent stuff !! Thank you
Thanks very much, Gary, for another incredibly useful and detailed tutorial!
Really learning some great stuff from your videos Gary, Thanks.
This is priceless! GREAT GREAT GARY.
Thank you - now I fixed my problem
I had been looking for a while to find the equivalent to Excels absolute and relative cell references in numbers and it looks like the preserve row and columns seems to do that for me. thanks
Gary, this video is packed with info. Can you perform a function based on the color text in a cell? In other words, let's say you have a column of red and black numbers. How can you sum all the red numbers and likewise the black numbers? I played around with conditional highlighting unsuccessfully. Thanks
No, functions can't see the cell styling, only the value. But you should be able to find another way to do what you want, like having a black/red value in another column.
Thank you. You’ve always been very helpful.
great explanation. thank you so much
Great stuff. Thank you!
Amazing value! ✅
love your post! What if I have a gig price column and a paid column, and if the date column is not filled in at the present date, can we make the price turn red? thank you! Oops your last explanation with hiding a column might be a way, thank you!
THANK YOU!!!💚
Great tutorial, keep it up!
Hello, so i have a registration sheet for my hockey schools, how can I create a second table that would tell me how many registrations I would have in different camps, exemple I have a shooting camp chosen, and I want to have a second table that would tell me how many are registered for that specific program. Thank you so mush for your advice my friend! Love your videos
Look into the SUMIF function. ruclips.net/video/KU-nYXO7fPg/видео.html
Thank you very much ‼
great video. Is it possible to HIDE rows based on a condition? I don't want to lose the information, but I don't need to see it regularly.... If Column O = 'Yes' then hide the row...
I know you can do alternating row highlights, but can you expand on that and do repeating rows with different colors so if you are constantly looking back-and-forth from the spreadsheet to real life that you don’t lose your place as easily?
Not automatically, no.
Thankfully I am waiting for your video
Nice!!!!
Thanks Gary
How do you conditionally format an entire row based on a value in one of it’s cell? Do this in Excel is cumbersome, at best, but possible.
Cumbersome in Numbers too, but possible. See ruclips.net/video/8U9ubBGsue8/видео.html
Great video. Is it possible to highlight an entire row based off the value of a cell?
ruclips.net/video/8U9ubBGsue8/видео.html
Your content is outstanding in every way Gary! I came here looking for a particular kind of conditional highlighting formula and I'm wondering if Numbers allows for it. I would like that if I enter a dollar amount into a particular cell, that if it is NOT a multiple of 5¢ ($0.05) that it will change text color or highlighting or both. I'm not stuck on how to choose the text color or highlighting, but I AM stuck on how to create that formula. The options in the conditional formatting do not directly offer the option of "if not a multiple of". I hope I explained my question clearly. Thank you in advance for your wonderful help!
No, there is no "not a multiple of" condition. So you'll need to use something else. Have another column with a formula using an IF statement and MOD to put a character (like an arrow or X) if the MOD of that other cell and 0.05 is not 0.
@@macmost Thank you for the prompt & very helpful reply that pointed me in the right direction!
Again a very helpful video. I have some trouble withe csv-format to analyse i.e. some blood pressure values in an app. May be you still have prepared a video or may be you can explain to import and prepare a diagram.
Even if I did a video on CSV files, it probably wouldn't address the exact problem you are having, whatever that is. You'll need to work at it to figure out what is going on.
@@macmost Thanks, which video I should select for csv files?
@@gerdt.7106 Not sure what you are asking. What is it you want to DO with a CSV file, exactly? Import it into Numbers? Export it? That's pretty much it. Once it is in Numbers, it is a Numbers file. So maybe you aren't really asking about "CSV files" but how to do something with data in a spreadsheet? Ask, with specifics, at macmost.com/ask
Thanks bunches
Are you able to inputs formulas to count the number of cells that are highlighted a certain color
No, you wouldn't count the highlights but you would count the number of cells that meet the same condition as you are using to highlight them. So if the condition is to highlight the cells when they are >100 then use COUNTIF to test >100 for those cells.
Hi, hope u doing well….do u know if it’s possible on numbers (iPad) create a new conditional highlight with formulas?… thanks for your time.
Conditional Highlighting works by comparing the value in a cell. If you want to calculate something first, then do that in a column and use the Conditional Highlighting on that column's cells instead.
Hi Gary...HELP! I have a table to track participant attendance at a 5-session workshop. I'm using checkboxes to track attendance for each date. I need to indicate either "Completion" for students who completed 4 of 5 sessions or "Participation" for those who complete at least 2 of 5 sessions, based on the number of checkboxes checked. How do I do this?
In another column, use COUNTIF to see how many are checked. So if B2, C2, D2 are checkboxes, in E2 you could use COUNTIF(B2:D2,TRUE) and get the number of checks. Then lots of ways to turn that into a value. For instance, with CHOOSE: CHOOSE(COUNTIF(B2:D2,TRUE)+1, "Fewer than 2 checked", "Fewer than 2 checked", "2 Checked", "All Checked")
I added +1 to the COUNTIF so a value of 0 is 1, 1 is 2, etc., for easier lookup in CHOOSE. But you can also use some nested IF functions.
I'm not clear on some of the keystrokes like up arrow 9, up arrow 0 when selecting a column. What am I missing? Thanks
Is it possible to make Numbers recognize dd-mm-yyyy dates, as opposed to the mm-dd-yyyy it recognizes your default?
Not easily. I've never seen that format. Is that from something you are importing? You can use functions to simply take characters 1-2, characters 4-5 and characters 7-10 and plug them into a DATE and get what you want though.
I am looking for a way to color the "TAB name/Worksheet name" in Mac Numbers application. This should just be so simple but I can't seems to find the option. In Excel all I have to do is right click and select color. Any suggestions?
There's no way to style the sheet name tabs at the top. What's your goal here? Can maybe putting some heading text at the top of the sheet work instead? Or maybe adding a color emoji character to the sheet name work?
Is it possible to save your conditional rules to a shortcut key?
I can't think of any way that could work.
still can't find how to grade a group of number cells. with red for the lowest and green highest, yellow and orange in the middle. the rules only apply to the individual cell and not as a group.
If you want to highlight only 8 lowest numbers and there are
duplicates. What is the format to only use 8 of them and not the duplicates?
That's a pretty complex thing to need. You'd need to experiment with extra columns and using RANK, maybe the RANK of the RANK, to get something like that. Will take some real spreadsheet skills.
Hello, I have a small question, hope you will answer :< My question is: does the code that I convert from Watch me do into RunAppleScript will be (slightly) different from a Macbook to another of a different version? When I send my Automator code to my colleagues with different Macs, the code never works and runs into error. Hope you can help me with this. Thank you
Hard to say. Watch Me Do usually involves clicking on specific spots on the screen, so it will only work right if the elements on the screen are exactly the same as when you created it.
Is there a way to highlight an active cell row to make it easier to read?
Select the whole row by clicking on the row number to the left.
@@macmost thank you a lot
I want to highlight if cell B2 is Not equal to C2+D2.
It doesn't seem to be possible without adding a new column, but it is possible in Excel conditional formatting 😥
Right. You would add a new column with the value of C2+D2. Hide the column if you like.
Do you know if there is a way to alternate the color of the table as the date changes?
Between odd and even rows? That's a checkbox in the Table settings.
@@macmost sorry, I realize now that was very vague. No. I have a balance book on a sheet and would like to see alternating colors where the first of the month is one shade, the second would be the other, the third would alternate back and so on since I have multiple transactions per day. Conditional highlighting for when the date changes.
@@Rerdeleon0810 Easy to do on the date cell itself (conditional highlighting), very hard to do for the whole row. See ruclips.net/video/8U9ubBGsue8/видео.html or maybe consider an alternative strategy.
Lets say I have two columns with 50 names in each column. Can I use conditional highlighting to highlight the names that appear in both columns?
Not the right tool for that. You'll need a third column with a formula in all of the cells that uses LOOKUP to search all of column A for the same name as in the cell in column B.
@@macmost got it. Another question. Excel has a function that lets you count the unique values in a column/row. Can numbers do that?
@@Don-gm2um Hard to say without knowing the function name. But if you wanted to count unique values you can do some clever things with a new column and using COUNTIF to compare each cell's value to the values of all of the cells below it, then add up the 0s that appear (0 would represent no match below it).
Hi Gary, I use Secure valut (keychain) to store my important records. Can I access them outside of my Mac mini?
Do you mean passwords? If you are using iCloud Keychain then you can access them on your other Apple devices as well in Safari, System Preferences or Settings on iOS. If you are creating special entries in your Keychain outside of iCloud then those would only be on that Mac.
@@macmost thanks Gary I am clear now 👍
When I click on cell, there is not a Conditional Formatting button. No Add Rule either. Can you help?
Are you looking at the correct section of the right sidebar?
How are you doing that "paste throughout"?
Not sure what you mean by that. Can you specify a time in the video where I am doing this?
I can't find any video for highlights entire rows non- conditional. In other words I just need to highlight an entire row randomly as I examine a numbers report .
So not a conditional highlight, just highlight the row temporarily while you examine it? Just click on the row number on the left side. The whole row will now be outlined and easy to read across. The best part is you can then use the up and down arrows to move the row selection. If you need more, you can always go to the Format sidebar, then Cell, then change the fill to something. Then change it back (or undo) when you are done.
I'm new to numbers and i wanted to know if there was a way to add 4 different colors to one cell ? I play the lotto and i want to visually keep track of wins. So i place a W on each cell and ideally id like to add a color behind each W. Is that possible ? Also, How can i add up the W's at the bottom of each column ?
Not sure what you mean. You can assign a different background color to each cell as you like. As for adding up cells that have a "W" in them, you would use COUNTIF for that. See ruclips.net/video/vkYm6jx7OaY/видео.html
@@macmost I get that but is it possible to use 4 different colors per cell ? For example Texas has four drawings every day. I’d like to utilize that by showing a different color for each one
@@Sjrick No, not in the same cell. Each cell should represent one point of data. So if there are 4 drawings, you should use 4 cells.
@@macmost Oh that actually might work, i hadnt thought of that. Thanks !!
btw is there a way numbers can automatically count all the W's i enter ?
How come custom formatting is not available in mobile
Numbers for Mac has a few features that aren't available in Numbers for iOS.
I am migrating from Excel to Numbers...and sad to say that Excel is way ahead wrt conditional formatting.
How do I remove a conditional highlighting rule?
I found it. The trash can was so small and light I did not see it. Sorry for the bother…old eyes!
I don’t have the conditional highlighting option in numbers. Any suggestions?
Not sure why you aren't seeing it. Follow along closely with what I show here.
@@macmost I’m using a pc not a Mac. Does that matter ?
@@tinaharlow4508 this is for the Numbers app for Mac. There is no numbers app for PC. Are using the web version? That doesn’t have all of the features of the full proper app.
@@macmost oh ok, I use the app on my iphone but editing it seems easier on my computer so I emailed myself the link :) Do you know if I use it on an Ipad if it has all of the features?
Is there a way to message you with a question?
macmost.com/ask
There is no Pivot Table in the menu bar.
Which version of Numbers do you have?
I only give you a 99 for this tutorial. Why ? You didn't address, although you came close, to the boundary condition I would test against, that is more than one rule that is satisfied. Which rule wins ? First, last ? For instance if a >= and a
There's no "dynamic tool tip" function or anything like that.
@@macmost still at 99, you didn’t answer conflicting rules:)
@@pedropuckerstein4670 I could try it and see. You could try it and see. I won't be offended if you try it first.
@@macmost But I'm offended that I have to do the test, and I did, and it seems that processing stops when it hits the first rule that is satisfied. I'm supposed to be the dumb RUclipse and you the king of the geeks RUclipsr :) - perhaps I should un-retire and become an Apple support person - seems like an easy job ..... for someone playing with UNIX since the early 1970's ....
WAY to long for the answer for a simple question.
This video isn't meant to answer a simple question, sorry. I'm teaching about using conditional highlighting and showing many aspects of that topic.
not too many people in this world are helpful these days
Answered nothing
Did you have a question?