Numbers: Conditional Formatting

I am familiar with Excel, less so with Numbers. I am trying to use conditional formatting in Numbers and, it seems less well implemented? Perhaps someone can explain how this could work? To the point, I cannot get a cell that shows the result of a formula to work in conditional formatting. Neither can I select an areas of cells and combine the formatting as this is about a single cell value being compared and not a cell to cell relative comparison in the same way if I dragged out a cell to cell formula (it would populate the formula relative to all the cells in the new dragged out area).


I am trying to compare two columns. If the results in cell A2 are the same as in cell B2 then, there would be no added formatting. If on the other hand, cell A2 and cell B2 are not equal, then the formatting should make the text in cell B2 red. Once I get this done correctly then I also want to apply it to the rest of the cells in the two columns.


This is not working. The problem seems to be that column B is holding the result of a calculation and therefore is not able to be read in the conditional highlight formula? This makes no sense at all that it has been engineered this way. As I have searched at length before posting this, some speak of adding a third cell, but as the value I need must be calculated, I cannot see how this is supposed to work - even in a work-around?


This is a project where I am getting a handle on an overdraft account that got away from me. I am comparing the banks monthly interest charges in column A with the result of my formula in column B, which calculates what the interest should be, based on the banks own supplied formula. These two cells should match exactly and where it is off, I want to notice it. That is why once I get it working in two cells, I want to apply it to results in both columns. But again, there seems no way to do this?

Posted on Feb 2, 2016 8:41 AM

Reply
5 replies

Feb 2, 2016 8:54 AM in response to musicoflove

musicoflove wrote:


I am trying to compare two columns. If the results in cell A2 are the same as in cell B2 then, there would be no added formatting. If on the other hand, cell A2 and cell B2 are not equal, then the formatting should make the text in cell B2 red. Once I get this done correctly then I also want to apply it to the rest of the cells in the two columns.



This works here:


User uploaded file



To get that A2 "lozenge" click the box-and-arrow-symbol and then click cell A1.


User uploaded file



SG

Feb 2, 2016 9:36 AM in response to SGIII

Weird. I used your simple example and I get the same results. But in my working sheet, I don't. I tried testing to see if it matters if the calculation formula was a SUM or a multiplication of an addition, but it doesn’t seem to affect your example. However, this is not working in my actual sheet.


I2 is set to: D4×E4×F4

I2 compares with H2


If I type a number value in I2, the conditional formula works. If I use it as the result of the formula, it does not work. By "not works" I mean that I have used this on five rows in that column, two values match and should not e red, 3 do not match and should be read as the condition states that if values are not equal then text is red. As it is, all the values in column I are red.


To reiterate, typing in the actual value so as to avoid the calculation formula, makes all the values work.


Wish I knew?

Feb 2, 2016 10:55 AM in response to SGIII

Yes, that was it. Thanks very much. 🙂


Part two of my question is, how can I get this working across the entire two columns? I saw examples about selecting several cells and using merge all. But these examples were about comparing a single cell value against many cells. I want to copy a relationship between cell positions just like any other value you dragged out cell to cell. As I am just starting with this project I only had to redo it 5 times. But going forward it would be nice to see how I can apply this to the whole column.


Thoughts on this?


PS - I am spoiled as Excel has an excellent little tool called a formatting brush. It would be so nice to copy the cells where I had this working and paste the formatting over the other cells.


PPS - Whoa Nelly! I just discovered that if I highlighted more than one cell and dragged it all the way down then I did copy the formatting.


OK then, this problem is solved. Again, many thanks for your help.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers: Conditional Formatting

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.