Use Conditional Highlighting Across a Whole Row In Numbers

macmost.com/e-2051 Conditional highlighting in Numbers is simply a way to change the style of a cell based on the contents of that same cell. However, by using a trick you can have conditional highlighting affect other cells or an entire row.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ KZbin - macmost.com/j-youtube (And hit the 🔔)
▶︎ Weekly Email Newsletter - macmost.com/newsletter ✉️
▶︎ Twitter - / rosenz and / macmost 🐦
▶︎ Facebook - / macmost 👍
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
#macmost #numbers #macnumbers

[@oastie3]

That’s another new thing I have to thank you for. I’ve also modified it to conditionally highlight columns, based on the value of one cell. You’re causing my poor old iPad a considerable amount of work. Keep it up.

[@adarateranroldan]

Thanks! Here're written directions for those who like them. 1. Count the number of columns in your data set and add the same number of columns to the far right. These extra columns are proxies. 2. Under Proxy Column 1, enter the following formula: IF($ =“”,A2,”X”). 3. Highlight all the proxy cells and paste the result. 4. Highlight A2 and then click the Cell tab under the . Click and . Select Text and whatever operator you want. In the field below, click the cell icon in the corner, and then highlight the first cell under Proxy Column 1. Choose the highlight formatting. 5. Highlight all of your data set and return to the Cell tab under the . Click under . Then. 6. Hide all the proxy columns if you’d like.

[@Instasamps]

I have a spreadsheet for my budget. I have tick boxes and the cell turns green when I tick the box under conditional formatting. How do I get the 3 cells to the right of the cell to turn green also under conditional formatting? I’ve tried and I’m stuck. Thanks

[@davidrosemond9012]

Thank you Gary for this hidden gem! Your knowledge is amazing!

[@discountlandguys-nc6765]

Very clever. I have wished for a feature like this for years and couldn't figure out how to do it until watching this video. Thank you Gary! :-)

[@milesmurray6401]

Thanks for the tutorial - very helpful and delivered in a way that is understandable and paced nicely without being condescending

[@pensacoladigital4366]

Thanks so much for this tutorial. I've wanted this function for some time now!

[@derjr9888]

Thank you for this simple yet functional tutorial

[@arkanjohudiel]

Thank you very much, bud! You've saved my work!

[@PattyEacobacci]

Mine too. I'm trying to create a database for Contacts and Attendance for an instructor and these are amazing. I'm re-loving Mac products again.

[@gonzacastellvi]

I'm really surprised how complicated it is todo do it in numbers, it's a mess, I love numbers but I admit that excel has a more powerful conditional highlight option there. Hope they improve this thing, its very useful.

[@Robert44444444]

I figured out this "hack" for engaging Conditional Highlighting on a cell based on the value of another using the 'proxy' cell (and then hiding the column) on my own last year and thought I was pretty clever having done so… then today I find you've had a video on this since Nov '19… lol… oh well, maybe it was still clever on my part ;-). Thanks for all the great tutorial vids… I'm going to join your Patreon group mainly because even though I've been using the Mac for a long time at a fairly advanced level, I've still picked up numerous tips of value since I began watching your content :-)

[@alfredotoral5910]

THANK YOU FOR YOUR VIDEO...IT HELPED ME A LOT

[@shaikha6929]

thankyouu for your hardwork gary !

[@Trackhoe075]

Thanks Gary for sharing these hidden gems in Numbers! It seems that numbers is the spreadsheet program that will keep on giving! Thanks again for all your explanations of these hidden gems! Someone like me would never be able to find without your showing us

[@SIRcodename22]

thanks man! really helpful! I am new to MacBook so your chanel is really helpful!

[@RK-ow6zt]

Hi Gary, Fantastic Video and Massive Appreciation! Great video, it stopped me using my macbook as a frisbee in frustration. :) I have been tinkering with this on and off over the last couple of years. Off your instruction I was able to also use checkboxes to change row colours once selected. Thanks & Kind Regards RK

[@ruthfdezsoto811]

Thank you for sharing. I used the same base but with a checkbox, and I can make a conditional highlighting that works with the checkbox.

[@lock_]

You're a genius! Many thanks. :)

[@PattyEacobacci]

I so appreciate your tutorials especially now with COVID I have the time to really study them. Is there a way for you to continue to offer a link to download the actual workbooks so we can study the formulas?

[@maikaimakena9424]

Thank you 🙏

[@metiniphone]

Thanks for great lesson. I have a related question. I would like to highlight a cell based different percentages. For example green highlight if over 80% blue highlight if between 79% to 70% and less than 70%. How I can do that. Thanks in advance

[@ramanshamoon7671]

Hi there, need help using google sheets to highlight rows based on part numbers. I have a parts list that is 40,000 lines long and need the rows to highlight whenever a part number changes. As an example, I have one group of a part no. 95 another with part no. 96 and so on. I want to highlight the rows with the same number grouping to be one color and the next part would be another color so I’d have two alternating colors whenever there’s a change in a part number. Any ideas?

[@focusclickrepeat]

I know this is a stetch because is an older video but I can't find the answer anywhere. I did the formulas to highlight the row and it worked (THANK YOU!) but now when I try to hide the proxy columns it only lets me hide 2 of them. When I right click the last one "hide column" is greyed out". Any idea around this? I know you did it in the video but maybe it has to do with the current version of numbers.

[@hgc2189]

Thank youu!!!

[@jonathanguillarme3118]

You are amazing, thank you so much for sharing your skills ;-)

[@nguyenTran-ju7lp]

Very amazing

[@anneciotola361]

Is it possible to crate a formula to highlight all dates that are 45 days out (or away) this would be used for lease renewals, which we have many of so I would like them to all become highlighted in time to send out. I have tried to figure it out but cannot sadly :(

[@chelsmoney]

yes there's a way to do this however its not a formula, you need to use conditional highlighting rule. first highlight the dates column or row. then add a conditional highlighting rule for Date -> Exactly... and then you can input 45 days from now. if that doesn't work make sure your date cells are set to date format! hope that helps

[@tonygoodin5099]

Another great video Gary. If it’s possible to do so in your videos can you confirm whether these techniques also apply to numbers in IPad. That would help a lot.

[@macmost]

It should. Give it a try.

[@scottbohart1592]

HI Gary, I'd like to do an "If cell A= 'Letter L', then x, if cell A= 'Letter S', then y" using this way of highlighting to give different colors to the same cell depending on what Cell A has in it. Is this possible?

[@elliemiese7193]

im looking for a solution to this as well

[@ifyouseekamimi]

Hi I am trying to set up conditional highlighting to my work numbers sheet. I need to have 6 different colors to be added when I type the word that corresponds with the color. IS there a way to do this?

[@PattyEacobacci]

Yes. In the Conditional Highlighting "Add Rule". Add a rule. Then from the drop down box you have some defaults. Scroll down to choose 1 of 6 colors -->at the bottom pick Customize view to change font style and pick more colors :).

[@DreJohnsonceo]

Does it work with a Checkbox? If Checkbox is check = Green if not = Red for example

[@rapaent]

Thanks for the great video! I can't shade every oth row. I highlighted the Table as Apples instruction and checked the bos in Table for color but, it doesn't change anything. Thanks again, Ray

[@macmost]

Keep trying. Try different colors and turning on and off Alternating Row Color. Or, just start a new document and see if it works there, etc.

[@rapaent]

@@macmost Thank you for the help.......... I played around until I got it. I needed to click on the Table choices. which turned out to be Table 1. Thanks again, I appreciate it. Ray 👍😊

[@MR1shank]

Hey man, can you help me out. What is the formula if I want to say Highlight cell 3 if > value of cell 4 and cell 1 contains the wording "covered call" using it track stock options, but only only applies to cells that are covered call and not put options in cell 1.

[@macmost]

Not sure what you want. Start by mastering what I am showing here in a sample document. Then try to apply that to your situation.

[@MrMosoani]

I’d like to learn Numbers. Do you have playlist for that?

[@macmost]

Yes. kzbin.info/aero/PL1bsBsPtN-9S2xQDWp43cwTcGhxPAR1Hp and also a course at courses.macmost.com

[@Rubiksguy]

Is there a way to highlight the entire row in BOLD that you have a cell selected in? I want to just click down on the arrows and have the next row be highlighted. I get it outlines the active row, but it's very faint and hard to see. I want my eyes to jump right to it!

[@macmost]

No, there's nothing like that.

[@rajeshpddr]

HI, how to create a data entry form in mac for numbers or excel. unlike its available in windows excel.

[@macmost]

Not easily. But you can sorta do it with a script. I did a video on that for my Patreon if you are on there. I'll probably be doing it as a normal KZbin video at some point too.

[@randomrandy3059]

Question, if I have columns of just two different teams, is it possible to change one of the teams to a 1 without manually changing each row? I feel it is possible, I just don't know what it's called. If someone could help me out that would be great.

[@randomrandy3059]

example: Ten @ Dal. Tennessee loses so I want to change Dallas to a1, without manually typing in 1 in each row. Sounds like its possible I just don't know how to do it. I want to convert text to a 1

[@TheJulian090909]

At 3:27 when you said you will copy this, what did you copy and what did you paste?

[@macmost]

I have cell E2 selected when I say that. I am copying E2 and pasting it to cells E2:G5.

[@KeystyleProperties]

Any chance of a work around with a form in IOS that can be shared, we made a form that works great for logging when we check properties but the form won't share with other team members, frustrating as should be such an easy thing to do, when we inspect we are using iPhones so need the simplicity of a form

[@macmost]

Forms just aren't supported with shared Numbers documents. Maybe use Google forms instead?

[@alexiamckinney7548]

I love this but is there a way to do this with a checkbox instead of a word such as "member"?

[@macmost]

Yes. Checkboxes are just true/false values. Set the condition to "Cell" and "true" or "false."

[@Don-gm2um]

Great video. I hope I can explain this properly. I want to highlight an entire row based on the value of one cell being the max number of a column of numbers. Is that possible?

[@macmost]

kzbin.info/www/bejne/boacppV4fNiom5o

[@larbikamla8541]

I want to know if there’s a method to use key number in a MacBook keyboard without using Capitalization key every time?

[@macmost]

I can't think of a way to do that, sorry. You can always get a numeric keypad if you really need to enter a lot of numbers very often.

[@andrewmack4830]

Again....another useful video. Your videos are a great reference library !! How can I highlight a date, if it is Sunday ????

[@macmost]

One way would be to create a custom cell format so that the day of the week is shown, like Sunday, July 31, 2022. Then use the Conditional Highlighting for "Text" looking to see if it "Contains" "Sunday."

[@andrewmack4830]

@@macmost thank you. Your recommendation works!!!!

[@timjenkins8943]

Is there a way to save the conditional highlight rules and apply them to different spreadsheets?

[@macmost]

You can use copy and paste. Copy one cell with the rule, paste it into another document. Select that cell and others and use the ability to combine rules for those cells to apply those rules to all selected.

[@timjenkins8943]

@@macmost Wow, thanks! You just saved me about 10 hours on very important family documents.

[@deborahv.3402]

Can I use conditional highlighting to highlight a countifs result?

[@macmost]

You can use Conditional Highlighting to highlight any value in a cell that meets a criteria. Doesn't matter what functions you use to arrive at that value. Try it.

[@kellerwilliamssignaturetra5242]

I have a column of address numbers and a column next to it with the street names (Column A contains home address number / Column B contains street name) and this is true for hundreds of rows. My goal is combine the data in columns A & B while keeping the separation between rows. How may I do this?

[@macmost]

Combing two columns of names is easy enough, using CONCATENATE. So CONCATENATE(A2," ",B2) will give you the number and street name with a space in between. But I have no idea what you then mean by "while keeping the separation between rows"

[@kellerwilliamssignaturetra5242]

​@@macmost Your swift response is a blessing Gary, thank you. I used that formula for one cell, and I received an orange triangle with a warning sign within it. I have several rows of house numbers and street names divided into their own column. Here is a link to a loom of a screen recording I made to show you what I did: www.loom.com/share/82baecca005245899e836d5017272583

[@macmost]

@@kellerwilliamssignaturetra5242 So AW and AX are the columns? Just just put AW," ",AX You forgot the whole function. It is CONCATENATE(AW," ",AX")

[@kellerwilliamssignaturetra5242]

@@macmost You just saved me countless hours of work. Thank you for spreading inspiration to do more good for others. I appreciate your help. When you're ever needing to buy or sell property, it would be my honor to refer you someone in your area and make sure you are discounted. Thanks Gary!

[@sunshinebest4432]

How do I add conditional highlighting for a range of values?

[@macmost]

Use the "between" option in the rules. You can see it briefly at 3:49.

[@sunshinebest4432]

@@macmost You're awesome. Thanks for responding! I figured it out shortly after posting the question. Cheers!

[@TheJulian090909]

Hi there. I am doing a church member list and would like the row colours to alternate between blue and yellow per family. Whats the best way to do this?

[@macmost]

Select the table, go to the Format sidebar, select Table at the top of the sidebar, then look for Alternating Row Color settings.

[@TheJulian090909]

@@macmost Hi thank you for your response. I do not want every row to alternate. I want the families to alternate. The number of members per family varies. Can I send you the document to have a look?

[@macmost]

@@TheJulian090909 That will be more difficult. If there aren't that many, you can just change the style of the rows manually. If there are a lot and manual isn't possible, then you'll need to come up with a much more complex system that will take a lot of time and effort, learning lots of techniques along the way. Not sure how important this would be to you and if you would want to do that.

[@TheJulian090909]

Is that possible?

[@macmost]

@@TheJulian090909 Do you mean is a "complex system that will take a lot of time and effort, learning lots of techniques along the way" possible? Depends on how much time and energy you have and how good you are at this sort of thing.

[@elital1248]

Hello, How can you add a rule to all cells when each one of them already have their own rule?

[@macmost]

Try it. You'll see. You get a prompt asking you to clarify what you want to do.

[@ianrjm969]

Can this be done on an iPad? I can’t find it.

[@macmost]

Yes, it is there. support.apple.com/guide/numbers-ipad/highlight-cells-tan93f5de5c3/ipados

[@johncipolla8335]

I really need help with a project. Can I use conditional highlighting when I check a box and put a date in another cell. and retain the date in that cell each day? Like completing tasks. on each day?

[@macmost]

No, there's no way to do that. Just type the date.

[@johncipolla8335]

@@macmost well I know how to accept the date but I have to cut and paste special to save the date. or it changes to all the next days. any way to stop that?

[@macmost]

@@johncipolla8335 Not sure what you are asking here.

[@johncipolla8335]

@@macmost I create season lists for tv shows I watch. I want to be able to hit a check box and fill a date and retain the date for the next days watchings. can that be done? This is to remind me that I have watched the episode and when

[@macmost]

@@johncipolla8335 Just type the date. There is no way to have a checkbox fill it in for you.

[@flyingdutchman5976]

How can I made multiple cells to highlight the lowest number of the 3 cells A2 1 B2 2 C2 3 I want to highlight The cells if the the number changed to the lowest of all 3

[@macmost]

Use the MIN function to put the minimum value in cell D2. Then use conditional highlighting on each of those 3 cells to look for the value equal to D2.

[@bereshyitbara7586]

Quite a hidden hack! Could you make one more video to show how 2D heat transition equitation works? Thanks in adv.

[@macmost]

I have no idea what a "2D heat transition equitation" is.

[@bereshyitbara7586]

Search two dimensional heat conduction equitation and Excel Solver, please. It may provide this video with a sort of science in my book. Differential equitation is the language of science. Thank you for your soonest reply.

[@ShadowPoet]

@@bereshyitbara7586 doesn't need a video. you can use >= or

[@bereshyitbara7586]

@@ShadowPoet fantastic. thx

[@timothybruckner4260]

When a cell is blank, I would like to put the "-" symbol. How would I do this?

[@macmost]

Not sure what you mean by "blank" -- if there is nothing in the cell then it is empty. If you want a dash in the cell, put a dash.

[@timothybruckner4260]

@@macmost What I am asking is, if a cell is empty, is there a formula to enter the dash symbol automatically? For example, if cell A1 is empty, I would like a dash to appear automatically, rather than the cell turning red or another color with conditional highlighting.

[@ShadowPoet]

@@timothybruckner4260 you can do that with a normal if function based on some sort of data somewhere else like a true/false, 1/0, checkmark etc. so like if false = - or if 0 = - etc

[@eyeojo]

Sneaky:) Did you have a hack to sort/filter by highlighted cells/rows? Thanks!

[@macmost]

So you only see those rows? No need for anything special there. The regular Number filter function does it.

[@eyeojo]

@@macmost I don't think there a simple option to filter by highlighted cells. I understand I can filter by the value triggering the conditional highlight, but its not the same....

[@macmost]

@@eyeojo Filtering by the same value triggering the highlight is exactly the same thing. There should be no difference at all.

[@AlergicToSnow]

That’s quite the hack. I’m frustrated that it’s necessary.

[@gordianfritz8349]

That's great. But it doesn't work if your cells are formatted as currency and numbers and text. The currency format is the problem because the if-formular just copy the amount but not the currency

[@macmost]

Not sure what you mean. Definitely does work with cells formatted as currency. Make sure the cell actually contains a number (not text) though.

[@gordianfritz8349]

@@macmost The issue is: in my cell I have numbers and the currency name or sign like "€" or "UAH". I haver to use different formulas for cells with text and currency. It works, but its a little bit more work to do.

[@macmost]

@@gordianfritz8349 Ah, you didn't mention that. Always enter the value as a pure number, like 5.35. Then use Cell Formatting to add the symbol. Otherwise, the value is text and not a number. So the call is 5.35 but the Cell Format is Currency, UAH.

[@JackWeaver]

This is such a hack. I wished they would expand the capabilities of conditional highlighting rules, and we wouldn't need "helper cells" to begin with.

[@RawFitChris]

I just wanted to highlight across ENTIRE specific rows. Isn't there a simple way to do that?

[@macmost]

That's what I show in this video.

[@moxtr]

Over my head. I have no idea what I'm doing, I was just trying to highlight a row across. Period

[@EmerlyNickel]

I don't understand why this isn't a feature in Numbers already. Why do we have to do this workaround just to get it working? That being said, I'm really impressed that you came up with this! Thank you so much for sharing it! I'm trying to help my grandma organize her eBay spreadsheet so that sold items are highlighted in gray because she has a tendency to forget to check the "sold" column when listing new items.

[@nikolailic6821]

I type literally the same formula and I get the "The formula contains a syntax error" message

[@macmost]

Then either you have a mistake in the formula, or the cells it is referring to don't have values or the proper values. Follow along carefully with what I am showing,

[@Jonas-mg5wx]

Thank you! But why is this even necessary T_T

[@PattyEacobacci]

because some of us aren't as savvy as yourself....?

[@Jonas-mg5wx]

Why does Numbers even make such a hacky workaround necessary...Not: why is this explanation necessary. I obviously needed it myself

[@ShadowPoet]

@@Jonas-mg5wx Numbers was designed to be an efficient, free, and simple part of the Apple productivity suite available to Apple users for casual spreadsheeting needs. Wasn't meant for power users, large businesses/enterprises, the scientific community etc. As a simple example, Numbers only supports 1 million rows.... paltry number for large datasets that make it literally impossible to use for anything substantive. Most casual users have no clue what conditional highlighting is let alone how to use it across rows and with circumstantial data 🤣

[@RickyBaby5568]

I’m sorry, you don’t leave a formula up long enough and with better, much slower instructions.

[@macmost]

Just pause the video. Also see kzbin.info/www/bejne/opq9hayfrp6BpLc