Conditional highlighting using a formula
In Word for Mac, we can highlight cells based on a formula. Is there a way to get the same in Numbers,too?
MacBook Pro with Retina display, OS X Mavericks (10.9.4), iPhone 4S
Apple Event: May 7th at 7 am PT
In Word for Mac, we can highlight cells based on a formula. Is there a way to get the same in Numbers,too?
MacBook Pro with Retina display, OS X Mavericks (10.9.4), iPhone 4S
Hi Naikosen,
You can't put a formula directly into a highlight rule, you would probably need to add a column for your formula.
What are your trying to do?
quinn
Sorry to say that the "Screen Shot 2014-09-07 at 1.52.03pm.png" couldn't be displayed.
Yes, we have a few glitches like that in these forums. It may appear shortly.
What I did was set rules for a column in the table
Numbers 3.2.2
Format Panel > Cell > Conditional Highlighting > Add a rule
Regards,
Ian.
Hi t quinn,
All the three columns in a row should be coloured Red whose sum total is zero.
Ex.:
The cells A1, B1 and C1 has no value, so the sum of A1+B1+C1 is zero.
Now with the Highlighting Rule, the cells A1, B1 and C1 must be turned Red, as it is possible in Word for Mac.
If it is not possible in Numbers, how could I get the same result using what method?
Hi Yellowbox,
The "Screen+Shot+2014-09-07+at+1.52.03+pm.png" is not displayed as yet.
What rule did you set in the table?
Regards,
Naikosen
Hi Naikosen,
Conditional Highlighting Rule 1
Greater than or equal to 10 shows Green Fill
Conditional Highlighting Rule 2
Less than 10 shows Red fill
Very easy to insert your own conditions into a rule.
Regards,
Ian.
Hi Yellowbox,
So nice of you to pay attention to my problem.
The problem about the present topic is made clear to t quinn. Please have a glance on it and suggest me what to do.
Regards,
Naikosen
Hi Naikosen,
Did I say column? I should have said table. This is a workaround that I have seen before on these forums.
Create a single cell table whose cell spans the 3 cells in your data table you want to highlight. Text in this table is white- will not show.
the formula in the cell is =IF(data::$A1:$C1=0,"T","F")
Conditional highlighting sets the fill and text to red using a Custom Style
Send the highlight table to the back with Menu>Arrange>Send to Back.
Avoid dragging the tables.
Will this work for you?
quinn
Hi t quinn,
Thank you very much.
The Screen Shot that couldn't display might be easier to comprehend your suggestion.
How to get display of your posted Screen Shot?
Regards,
Naikosen
Hi t quinn,
We can't put a formula directly into a highlight rule like as that in Excel for Mac.
Is it possible to get the same result using other way in Numbers?
Hi Naikosen,
You are correct, we can't put a formula directly into a highlight rule. It is possible to get the same result.
It is so frustrating to me that screenshots are currently problematic. I wish I knew what was the problem- it has been getting worse in my experience.
Let me try to describe my screen shot.
Data Table is the table you currently have.
Highlight Table is a table you create that is a single cell, 3 Data Table cells wide. When it is dragged over the Data Table it will match the cells you want highlighted. Set the text color to white in this cell.
The formula in this cell is
=IF(data::$A1:$C1=0,"T","F")
Conditional highlighting sets the fill and text to red using a Custom Style. If you need help finding the Conditional Highlighting sidebar let me know.
Text is:
T
Custom Style is the last offering in the drop down.
Set fill to red, set text to red.
--Here is where I paused and checked to see if things worked. Make an entry in one of your data table cells, the highlight table should turn red. If ok, then continue.
Drag the highlight table directly over the three cells in the Data Table you want to highlight.
Send the highlight table to the back with Menu>Arrange>Send to Back.
Avoid dragging the tables.
Let me know if anything is not clear.
quinn
Hi t quinn,
Your recent suggestion is very useful. But my problem is still mocking at me.
Below is the snapshot of a would be final result of Conditional Highlighting rule, where:
Regards,
Naikosen
Hi Naikosen,
Unfortunately your screenshot is not visible to me. I don't know it this will help us here but I have seen where copying and pasting of a table into the reply box can help illustrate what is needed.
However, if I understand what you are asking for, this is much simpler than before.
You are trying to hide a zero result, yes? This is simple.
Your conditional highlighting rule in each cell is:
Numbers>equal to 0
Custom format>white text (this is the color picker on the left side)
After you have made your rule in one cell, select it and the others.
Click "Show Highlighting Rules" and click "Combine Rules".
quinn
Hi t quinn,
The screenshot as copy pasted below may clear what I needed:
0 | 0 | 1 |
To make more precise:
In another word, =IF(SUM(A1:C1)=0,"Turn the font colour White","No change")
Regards,
Naikosen
Conditional highlighting using a formula