We're working on a large-scale project with a client who must remain nameless. Our main contact had a high-level presentation; they wanted to make a lot of complex relationships clear, more-or-less instantly.
For client reasons, "heatmap" was the visualization of choice. (Don't get me wrong - I understand the appeal of heatmap-style visuals. I'm particularly fond of the iPad/iPhone app Stocktouch because it makes such elegant use of heatmap visualization. The whole stock market, at a glance, on a single iPhone screen. Wow.) But markets are, by definition, a bunch of like measurements of unlike things. "Like Measurements" is the key concept there - if AAPL is more red than GOOG, it's "as measured on the same scale."
OK, that's the fun & challenge of helping clients. You wanna heatmap, we gotta heatmap inna back. We knew that the data was (cough) in flux, so that ruled out hand-rolling color scales in Illustrator or InDesign. The magnitude of cross-comparisons probably would have ruled those out anyway - 10 groups, 30 different measures, 3 different comparison-range variable PER MEASURE. (You want to try hand-calibrating 9000 colored blocks against each other, knock yourself out. Not me.)
So the tool we turned to: big data geeks may sniff at this, but i'll meet 'em at the OK Corral any day with Excel in my holster. It wasn't a perfect solution, but the Conditional Formatting tool in Excel does include color scales, and we were able to engineer all of the visuals directly in Excel. Here's a snapshot - variables and data changed, of course.
There are actually 27 color scales here - one per row. Unlike the stock market, each row had different units of measure and a different basis for comparison; high wasn't always Tempus. Where Excel cooked on this heatmap problem was in enabling all of the calibrations and comparisons, and letting us change formulas/relationships quickly.
The color scale conditional formatting isn't a perfect tool by any stretch. Really fundamental Excel tools can be manipulated with & against each other - range references, functions, that kind of stuff. The color scale conditionals are more-or-less 'hard coded' on each row - for example, the LOW and HIGH references are absolute cell references. (Meaning, move the scale and the whole thing breaks...) Plus - and I know this is a nit - the only way to manipulate the conditional formatting is to click through the stupid dialog boxes.
All that said - if you want to take a heatmap view of something out for a run, it's not a bad way to go. Yeah, Processing or R could probably do it better, but deadlines and data-changes make coding solutions difficult. If the format gets fixed/frozen, we'll give that a shot.
If you want the actual Excel sheet...enjoy!
--md
Another Excel heat map post here.
P.S. Great post on heatmaps and color blindness from our favorite technical partner, Wistia.