Tuesday, March 19, 2013

Heat map plot in excel using conditional formatting

Heat map plot using conditional formatting 

The simple way to generate heat map plot is conditional formatting of cells. 

(1) If your data is long form you can generate table by using pivot table function.

Save the file as csv and load back to get rid of pivot table format, but reorganized data.

Now we can conditionally format the cells. In 2003 we can conditionally format only to three levels. If you want to hide number in the cells, select the color for text exactly same as the cell color.

Remove everything unnecessary features and print the sheet as pdf. 

In Excel 2007:

You can hide the cell values if you like to: Select the data you want to hide. In home tab  go the dialog box launcher (next to the Number). In category box click custom and in type box type three semincolons (;;;) and then clik OK.

The result would look like the following. You can print the plot as pdf or take a screen shot of it.

Here is another bigger example:


  1. Thank you for your very useful blog. I want to make heatmaps like yours too. How do you add legend and how do you set value when setting 3-color scale? I used "value" instead of "lowest" and it didn't change a thing.

    Thank you for your time.

  2. Thank you so much for your kind sharing

  3. Thank you so much for sharing

  4. I am trying to create a surface plot but some of my cells do not have data, I can't delete the cell because they correspond to a physical location. If I go to SELECT DATA => HIDDEN AND EMPTY CELLS the "connect data points with line" option is not available to be selected.
    I have tried #N/A which should be plotted but anything I try graphs these "empty" cells as having value 0 ( most of my data is between 5.00-600 range, that is my ideal scale ).

    Any help appreciated