I’ve been playing around with Excel 2007’s conditional formatting. Here’s a 150×150 range of cells that uses one of the new color scale conditional formats. It’s a plot of SIN(x) x COS(y) for x and y values ranging from 1-4.
I used ;;; number formatting to hide the numbers in the cells, so all you see is a very cool gradient contour “chart.” This is a small version. Click the image to see a larger one (captured while the screen was zoomed to 10%).
The amazing thing is how fast it is. If I change the formula or the increments, the color refresh is almost instantaneous.
Hi John,
It is really amazing. I have downloaded the beta, but am afraid to install. I don’t know whether the files created in Office 2007 (read M S Excel 2007) will function properly in Office 2003 (presently used), if I had to go back to Office 2003. Whether there are any known dangers except the new functions added in Office 2007.
John, you mention how fast it refreshes in 2007 – how are you finding overall recalculation performance?
I haven’t done any real comparisons, but I certainly haven’t noticed any problems in terms of speed. I did load a very calc-intensive file, and noticed that the status line said “using two processors.”
could that be the much talked about thread spliting, or do you have a pentiume d? lol!
OO7-kanwal, you can save your files in the old XLS format, and Excel will tell you exactly what problems (if any) you may have. I also discovered that installing Excel 2007 also installed the compatibility Pack for Excel 2003. So I can open files saved with the new file format in Excel 2003.
You also need to aware that you’re working with a beta, and nothing is guaranteed. That said, I haven’t experienced a single file-format related problem, and I’ve been doing a LOT of back-and-forth with 2003 and 2007. Your mileage may vary.
This reminds me of the plasma effects we would write for the Amiga 500, except those were animated. hmm, that gives me an idea.
Rob, I have been toying with that idea for a while now, but using Excel 2003 (and XP before that) the results have been painstakingly slow (unbearable even).
I am thinking of installing Office 2007 solely for the reason of playing around with this actually… Yes, I am sick and need help.
John,
Didn’t know you could use ;;; to hide numbers. Neat trick. In a few years when my company installs 2007 (We just installed 2003 at the beginning of this year), people will go nuts for this kind of conditional formatting.
-Jason
The ;;; trick is in the Excel 2003 help (naturally this observation deserves no credit whatsoever after the event!). I tried a few combinations:
#;;;
Hides text but not positive numbers
;;;@
Hides all numbers but displays text
So I guess you could use:
#;;;@
To show text and positive numbers but hide negative numbers.
Maybe someone could do the donkey work on all the possible combinations and post results here. I can see a hell of a lot of IF formulas becoming redundant.
For what it’s worth…
Using a formula such as A1&” M” will render a Number formatted cell back into General format meaning that it will behave unpredictably if used in numerical calculations. Formatting the target cell with a custom cell format such as 0? M” leaves the Numeric part of the formatting intact while allowing text to be appended. Maybe eventually we can use formulas in custom cell formats?
One more thing while we’re on the subject…
If you use a custom cell format to conditionally amend the color of your cell, and the same cell has condtional formatting in effect to change the colour to something else then the conditional formatting wins the day. Does anyone have the official wording of this hierarchy?