Referring to “this cell” using Excel conditional formatting
Since writing this post, three simpler, better ways of solving the problem have been submitted in the comments section. Feel free to read this post, but look to the comments for the solution!
If you already know about conditional formatting and navigated here via Google, please jump straight to the hack. If not, I hope the following introduction is useful. You might also like to check out the WikiHow introduction to conditional formatting in Excel. This post is actually concerned with an interesting hack that lets you reference the value of a cell itself when setting up formula-based conditional formatting rule.
Conditional Formatting
Excel’s conditional formatting feature is a boon to heavy spreadsheet users like me. It is a flexible and powerful tool that (among other things) lets me highlight data according to a set of rules so that I can easily spot the interesting bits in what would otherwise be an almost impossibly dense and meaningless cloud of numbers. Here’s an example; a table of the correlations between 32 different statements (taken from some ongoing work looking at a simple blogger typology.)
I’m sure you’d agree that your eyes would go squiggly if you looked for interesting data points in that mess (and that’s the processed table!) Instead, I use Excel’s conditional formatting to look through it for me. Here’s the same table with interesting points coloured red, orange, and yellow (in decreasing order of interest.)
Useful, eh? However, there are some very tight restrictions. Among them are:
- The rule must evaluate to TRUE or FALSE. Either the cell is more than x, for example, or it isn’t.
- Like wishes, you can only apply three conditional-formatting rules to any cell.
- Once Excel has evaluated a condition as TRUE it stops processing any further rules. It evaluates Condition 1, and if that’s FALSE will move on to Condition 2 and if that’s TRUE will never evaluate Condition 3 (however interesting Condition 3 may be.)
The Problem
Here’s a much reduced version of the table of correlations example I used above. I still wouldn’t want to look through a table like this very often, but it’s easier to make my point when you can read the numbers.
I did this by applying the following conditional formatting rules.
For the sake of this analysis, any correlation of 0.5 or over is seen to be “possibly interesting”, 0.6 or over “interesting”, and 0.7 or over “really quite interesting indeed.” I should probably come up with a better scale before I share this stuff in future.) But as it happens, correlations can go two ways, positive (“people who agree with this statement tend to agree with that statement”) or negative (“people who agree with this statement tend to disagree with that statement”.) So I also want to highlight any correlation lower than -0.5, -0.6, or -0.7. You can see from the following screen grab that this isn’t working.
But I only have three rules. What am I to do?
As it happens, Excel lets one specify a formula as a rule rather than simply a cell value. So, for example, I could check to see if:
=OR(A1<= 0.5, A1>=0.5)
But I’d have to code the conditions for each separate cell in the table. I am notoriously lazy and therefore unlikely to do something like this, particularly for the 32×32 matrix in the first example.
What I need to do is refer to ‘this cell’ in the formula. Now in normal Excel use this would lead to recursion, a circular reference, and therefore an error. For this logical reason, Excel’s developers never saw the need to let a cell refer to itself. But that’s exactly what I need to do!
The Excel Conditional Formatting Hack
After much faffing, I finally worked out a way around the problem.
=ABS(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE))>=0.5
Which you will see works very nicely.
Let’s look at how it works (I’ve put the bit that matters in bold face.)
=ABS(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE))>=0.5
That probably doesn’t help all that much. On the assumption that for many of you it’s the first time you’ve seen these functions, let’s pull it apart a bit.
INDIRECT is a way of linking to a cell using a “reference text” – that is, a text string that looks like a cell reference (“A1″ for example, or “R1C1″ which is the same thing – pointing to Row 1 Column 1)
We actually employ it like this:
INDIRECT(ref_text,a1)
Where ref_text is the “reference text” and a1 is a TRUE or FALSE value that tells Excel whether to expect an A1-style reference (TRUE or omitted) or an R1C1-style reference (FALSE)
ROW() tells Excel to insert the value for the current Row, and…
COLUMN() tells Excel to insert the value for the current Column, so:
INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE) gives us the R1C1-style reference to the current cell. This cell! Hooray!
These didn’t work…
Incidentally, I also tried these which I think should work but don’t. I include them for completeness’s sake.
=ADDRESS(ROW(),COLUMN())
=INDIRECT(ADDRESS(ROW(),COLUMN()),FALSE)>=0.5
=INDIRECT(TEXT(ADDRESS(ROW(),COLUMN())),FALSE)>=0.5
I believe the same can be vachieved via:
1. Select with your cursor the entire range to be covered with conditional formatting; keep in mind that odd-shaped ranges such as triangles can later be achieved by deleting CF from cells.
2. With the range selected, activate Conditional Formatting using the left-most, upper-most cell refenerence in the range as a general/”thi cell” reference: =IF(D3=…
Note: CellRef *must* be given in Relative terms; do *not* anchor.
3. Use the same structure for subsequent onditions. The above work fill a conditional format range whose wpper-left most cell was D3
It has been a while but I believe that this works
Specifying a number after R and C gives an absolute reference to the cell (e.g.) R2C4 would refer to $D$2.
If that number is in square brackets, it is a relative reference from the cell it is entered in (e.g.) R[1]C[-2], if entered in the cell D2, would refer to the cell B3.
Since you’re doing conditional formatting, your code could actually be simplified by just specifying INDIRECT(“RC”,FALSE) to refer to the current cell.
INDIRECT(ADRESS(ROW(),COLUMN()),TRUE) must be work
@jon — you’re right: that’s probably the simplest way of doing things, and I’m kicking myself for not working it out myself!
@starfire, @glauber — the R1C1 approach occurred to me, but (A) I didn’t know the square-bracket modifier approach, and (B) I’d never have worked out @starfire’s elegant hack. I should though have been able to work out @glauber’s version, given how close I was with the =INDIRECT(ADDRESS(ROW(),COLUMN()),FALSE) version.
All of these are better (and less processor and time consuming) than my own way of solving the problem — thank you so much!
I tried your methods in openoffice. Doesn’t seem indirect likes the use of “r”&Row()
however, I can put =”r”&row() in a cell, and it lists a value.
I did notice, that “c”&column, returns the column’s # and not letter designation. Does this matter?
Also, I tried the advice by the commentors, nogo.
I got it. I can’t highlight a range. I have to select one cell. Set the formula with the default reference to the cell. I.e. c4. Then copy and paste it, the relative position of the cell changes with the new cells.
Do you know if a cell in Excel can detect if another cell is selected? (not highlighted, just selected with the mouse) Can this be accomplished using Conditional Formatting?
This is why what you think should work won’t actually work:
———————————————————–
=ADDRESS(ROW(),COLUMN())
This returns a string in A1 reference style, such as $A$5
Since it’s a string, Excel doesn’t see it as a cell reference.
———————————————————–
=INDIRECT(ADDRESS(ROW(),COLUMN()),FALSE)>=0.5
The FALSE says to use an R1C1 reference style.
Since ADDRESS returns strings in the A1 reference style, this will return an error.
———————————————————–
=INDIRECT(TEXT(ADDRESS(ROW(),COLUMN())),FALSE)>=0.5
TEXT requires two arguments, one of which is a format.
To get what you think you want, you could put in “@” to return the Text format or “General” to make it the General format.
Since ADDRESS already returns a text string, you’re just turning a string into a string.
This has the same problem as the previous in that the FALSE still tries to force an R1C1 reference even though ADDRESS will return an A1 reference.
———————————————————–
Previous solutions in the comments are accurate but, if you want every cell to have the exact same conditional formatting formula, starfire has it right.
=ABS(INDIRECT(“RC”,FALSE))>=0.5