Conditional Formatting Hack

October 5th, 2007 | by Michael |

I would like to share a very interesting tip that forum member 5|=vv posted on the Apple boards.

The question posed was:

I was wondering if two columns have the same value can I establish a condition that would underline one column?

Example:
Orig. Price Current Price
$200,000 $250,000
$350,000 $350,000

This is, in fact, not possible currently in Numbers, as the conditional formatting options only allow for comparisons based on static numbers rather that formulas.

5|=vv’s brilliant work around, was to use a a feature that is unique to Numbers: the ability to overly tables. As 5|=vv explains:

In general, the idea is to use a second table containing a formula that can be used to compute the conditional format, an orange colored (read “coloured” in Britian, etc. ) cell background. In this case the formula is an IF() comparing the equality of the two columns in the first table (see the formula bar in the second screenshot). Note that both the text and the background conditional format rules have been set to orange. This causes the “TRUE”s that would otherwise spoil the effect to disappear.

Then, using the “Table Inspector” panel, set the cell background of the first table to “None”. The effect is completed by sending the second table to the back (select the table as a canvas item indicated with square selection handles and use menu item “Arrange”/”Send to Back”) and align the tables with mouse or arrow keys.