Quantcast
Skip to content

How to Create a Heatmap in Excel

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…

18 Comments

  1. Adrián Chiogna on December 9, 2018 at 1:21 pm

    A 3D Warehouse Heat Map in MS Excel (by Adrián Chiogna).
    https://es.slideshare.net/HECTORADRI/a-3d-warehouse-heat-map-in-ms-excel-by-adrin-chiogna

  2. Melisa on January 23, 2017 at 3:44 pm

    Thank you, this was so useful! (And a note, in Excel 2013, you need to use the Developer tab to find the VB and Macros tools . You may have to change your Excel preferences in order to be able to view said tab.)

  3. Edwin Q on July 7, 2016 at 6:35 pm

    Thank you so much. Finally an internet VB tutorial that works. So much useless stuff on the internet. You actually delivered on what you titled. Thank you again. I´ll remember this site.

    – Fast
    – Simple
    – Useful

    10 rating

  4. djr677 on February 11, 2013 at 12:33 am

    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.

  5. Ming Her on September 29, 2010 at 7:55 pm

    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?

  6. Ming Her on September 29, 2010 at 7:55 pm

    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?

  7. Paul N. Hengen on April 14, 2010 at 5:12 pm

    Willie,

    Absolutely. Just edit the “Case Is” part of the macro and give it a try.

  8. Willie Brickey on April 12, 2010 at 7:05 pm

    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.

Leave a Comment

You must be logged in to post a comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll To Top
Share via
Copy link