Most available heatmap programs I’ve encountered cost too much, come bundled in a huge application, don’t do what I want, or don’t offer enough flexibility.
In the previous article on heatmaps, I showed how you can use ASAP utilities to sort color-coded cells using this useful Excel Add-in.
What I didn’t tell you is how to create the heatmap in the first
place. So here I’ll show you how you can easily build a color heatmap within an Excel spreadsheet.
Here’s how to do it.
Set up the macro
(1) Copy my the entire text of my handy heatmap macro from this page.
(1) Open Excel
(2) Click on Tools>Macro>Visual Basic Editor (VBE)
(3) In the VBE, double-click on Sheet1 in the upper left corner,
(4) Click here get my handy Excel heatmap macro. Make sure you take all of the text.
(5)Paste the macro text into the code window and close that window by clicking the [X] in the upper right corner.
(6) Close the VBE the same manner
To use the macro
(1) Click on Tools>Macro>Macros (make sure that macros are enabled!)
(2) In the Macro window, you’ll now see the newly created heatmap macro listed
(3) Select it from the list and click the Run box
(4) When you see “Select a range of cells”, mouse over to select the range of cells you want to colorize, then
(5) Click the OK button to color your world.
To change the colors displayed in the heatmap
Go to this webpage (http://www.mvps.org/dmcritchie/excel/colors.htm), find the Excel color codes you want, and edit the macro accordingly.
To test the macro
Make a series of number from 1 to 8 in adjacent cells cells and try to color code them. If you find the number range doesn’t suit your particular purpose, simply edit the macro to change the range of values to be colored.
Good Luck! …and let us know if you have any other macros on hand in your toolbox…