I upgraded to Office 2010 this week and was today playing around in Excel. (I consider myself somewhat of an Excel power-user.)
Apparently there’s a bunch of new stuff you’re supposed to be able to do with Conditional Formatting, but I would’ve been very happy with them just working for starters.
Scenario A: Need to color cells in columns J:O based on the values in B:G on the same sheet.
This seemed simple enough and I quickly worked out that I could just use the normal format the cell depending on a formula. 3 rules to format in 3 colors. The formula itself was quite simple, an IF() to compare value1 with value2. Value1 and 2 fetched with INDEX().
Like this:
=IF(INDEX($A$1:$O$38;ROW();COLUMN())>INDEX($A$1:$O$38;ROW();COLUMN()-8);TRUE;FALSE)
=IF(INDEX($A$1:$O$38;ROW();COLUMN())=INDEX($A$1:$O$38;ROW();COLUMN()-8);TRUE;FALSE)
=IF(INDEX($A$1:$O$38;ROW();COLUMN())<INDEX($A$1:$O$38;ROW();COLUMN()-8);TRUE;FALSE)
Short explanation: INDEX(A1:O38; …; …) this grabs the values so far as an array. ROW() and COLUMN() just return the current row and column numbers so index can return the content of the cell.
Scenario B: Same thing as above, but use Icon Sets with arrows up, down and sideways instead of coloring the background.
I first tried to set the > and >= to use Formula. This resulted in the formulas disappearing from the input field half the time. Apparently they did not disappear, they just showed blank though the formula was in there. Reading invisible stuff is a bit of a challenge for me at least.
Switched to using type Number instead and just fetching the correct cell with INDEX($A$1:$O$38;ROW();COLUMN()-8), but this resulted in most of the arrows pointing in incorrect directions. Very odd, I would have imagined that this one would work right off the bat.
About 15 minutes later I realized that if I use INDEX() on just a single cell like I described above, it worked! Meaning I could now use the format painter to paint one cell at a time and it would show the correct arrow. At this point everything was more or less hunky-dory, until I gave the formula to a colleague who has Excel 2007… and of course that did not work at all.
*&%#¤!!
Scenario A worked in 2007 though. It also seems that if you’re not careful when entering the formula as Number, it will add =” ” around INDEX. This caused some of the problems I was seeing.
One really weird thing I saw in 2010 was that when I looked through the list of Conditional formatting rules, the “Applies to”-section would become blank as I scrolled down and back up. See the screenshots below.
Conditional Formatting rules, before scrolling
Conditional formatting rules, scrolled down a few steps
Conditional formatting rules, back to the top
