Heat Charts, Another Creative In-Cell Charting Technique

I recently completed a $50 Project for a client who wanted to create visual "heat charts" from data in a matrix. What's a "heat chart" you ask? Well, basically, it's a color-coded representation of the relative data values contained in a data set. What?

Ok, so say you've got four columns of data in four rows: The columns represent the four seasons of the year, and the rows represent four different cities around the world. The data in the matrix might be the average midday temperature in each of those cities over each of the four seasons. A heat chart would represent this data showing the hottest (highest) value as bright red, and the coldest (lowest) value as deep blue, and everything in between would fall on that spectrum from red to orange to yellow to green to blue. Now, obviously, a heat chart can be used to represent any data, not just temperatures...

Here's a picture so you can see what the heck I mean:


(Click on the image to open it up larger in a new window.)

How's it work? Well, basically I just use some formulas to convert the values in the matrix into an "index" from 0 to 100, where 0 is the lowest value in the chart (or another user-specified minimum value) and 100 is the highest value in the chart (or another user-specified maximum value), and all other values in the chart fall somewhere, proportionately, in between.

Using the values in this "index" version of the chart, (using VLOOKUP) I reference each the RED, GREEN, and BLUE values for the RGB color representing that index value from a predefined COLOR SPECTRUM table.

Then, with a macro in the ThisWorkbook object, I automatically set the background color (using the RGB property in VBA -- i.e. "cel.Interior.Color = RGB(RED_VALUE, GREEN_VALUE, BLUE_VALUE") every time the sheet is changed.

This together with some general cell formatting, data validation, and some other macros to help automate adding and subtracting rows or columns, and there you have it.

Pretty cool if you ask me.

Later,

Excel_Geek