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…
thanks for this Paul. I’ve used spotfire for this in the past (when I worked for a big company that could afford it). I’ve made some pretty lame heatmaps in excel before – this is way better
Ian
You can also create heatmaps in R (which is free).
There is also a handy website called called matrix2png located at the following address: http://www.bioinformatics.ubc.ca/matrix2png/
This takes a labeled tab delimited excel-type data matrix and allows you to set all sorts of options to get you a pretty nice looking heat-map. Its worth a try!
Excel 2007 also has the ‘Conditional Formatting’ tab on the ‘Home’ ribbon. It can achieve some aesthetically pleasing results and is also customizable.
Gr8!! Thank you…
Hey….thanks!!I would have probably wasted hours trying to figure this out…you have saved me alot of time. Now I only hope this will make my data look better :o)
Peace…and Happy New Year!!
thanks for the tools.
i have a question, how to change the macro if i want to separate values (0.5 / 1/ 1.5 / 2 / 2.5 . . .. )?
thanks
Ming,
I think it might be easier for you to use conditional formatting in Microsoft Office Excel 2007, which wasn’t available to me when I wrote the macro. If you still want to use the macro, I think all you need to do is choose a maximum value and then change the values 8,7,6 etc. to 8.5,8.0,7.5 etc. If it doesn’t work for you, please let me know what you mean by “separate values”.
-Paul.
Hi Paul,
The value of my data range from 0 to 4 in which i would like to separate them every “0.5″. I am using window vista and i don’t know how to change it.
Thanks,
Ming
Ming,
Enable macros within MS Excel.
Create a new macro or edit and existing macro.
Use this as the macro text:
Sub heatmap_for_Ming()
Dim myrange As Range
Dim colchoice As Integer
Set myrange = Application.InputBox(“Select a range of cells”, rangetocheck, , , , , , 8)
For Each cell In myrange
Select Case cell.Value
Case Is >= 4
colchoice = 1
fontchoice = 2
Case Is >= 3.5
colchoice = 3
Case Is >= 3
colchoice = 45
Case Is >= 2.5
colchoice = 6
Case Is >= 2
colchoice = 19
Case Is >= 1.5
colchoice = 20
Case Is >= 1
colchoice = 8
Case Is >= 0.5
colchoice = 41
Case Is < 0
colchoice = 5
fontchoice = 2
End Select
cell.Interior.ColorIndex = colchoice
cell.Font.ColorIndex = fontchoice
fontchoice = 1
Next
End Sub
In MS Office Excel 2007, make sure to save your file as “macro-enabled”.
-Paul.
I have a range of -10 to +10 profiling values. I’ve successfully identified and changed colors for subsets of expression values > 0. Can subsets less than 0 be identified and colored (i.e. set A with normalized ratio values <-2; set B with values <-4; set C with values less than -6; set D with values less than 0 but greater than -2; etc)?
Thanks.
Willie,
Absolutely. Just edit the “Case Is” part of the macro and give it a try.
Paul,
I tried to use Excel 2007 “Conditional Formatting” to generate 3-color scale heat map. The problem is how to change the color of the numbers so they will “merge” into the cells to give a real heat map instead of a colored map with numbers inside each cell. I guess the function I need is to do “conditional formatting” on font, so the color will go along with the color of the cell. But cannot find out how to do it in Excel 2007. Any advice?
Paul,
I tried to use Excel 2007 “Conditional Formatting” to generate 3-color scale heat map. The problem is how to change the color of the numbers so they will “merge” into the cells to give a real heat map instead of a colored map with numbers inside each cell. I guess the function I need is to do “conditional formatting” on font, so the color will go along with the color of the cell. But cannot find out how to do it in Excel 2007. Any advice?
Thanks Paul, this is very helpful! I will beusing this soon to show my new acetylscan (acetylation) data at a symposium soon. You have made me a very happy scientist.