How to Create a Heatmap in Excel |
 |
Paul Hengen |
To enable tagging you will need to register on Bitesize Bio. We're sorry for the inconvenience, but it's free, only takes a few seconds, and it will enable you to view our seminars for free, ask questions from the professional community, and take part in the lively community of Bitesize Bio
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…
Finally, Useful Heatmaps in Excel
Help! Excel Transmogrified My Gene Names!
The Best of Bitesize Bio 2009
To leave a reply you now have to register on Bitesize Bio. We're sorry for the inconvenience, but it's free, only takes a few seconds, and it will enable you to view our seminars for free, ask questions from the professional community, and take part in the lively community of Bitesize Bio
Ian
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
Michelle
You can also create heatmaps in R (which is free).
Augustine
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!
MrAnon
Excel 2007 also has the ‘Conditional Formatting’ tab on the ‘Home’ ribbon. It can achieve some aesthetically pleasing results and is also customizable.
Brijesh
Gr8!! Thank you…
Array10
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
)
Peace…and Happy New Year!!
Ming
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
Paul N. Hengen
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.
Ming
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
Paul N. Hengen
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, , , , , ,
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.
Willie Brickey
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.
Paul N. Hengen
Willie,
Absolutely. Just edit the “Case Is” part of the macro and give it a try.