Creating heat maps with VBA/Excel

Today’s post is about how to create a palette to use for a heatmap, and how to apply it to either an Excel Table of values to be compared, or to create surface chart and apply the palette to the categories. A complete description of how this all works, along with the downloadable workbook and code can be found on the Excel Ramblings site.

The heatmap palette

A typical palette for a heatmap would look like this.

The tricky part is to calculate an appropriate RGB combination for the color based on the where the value lies between the min and max value of the other values in the table. The algorithm for this is in the downloadable example.

Using the palette on an Excel table
This example just applies the palette to values in an excel table. Something like this can be done using conditional formatting, but using the heatmap algorithm allows for easy automation in VBA.

Creating a surface chart to illustrate a heatmap.
Excel provides a nice chart that can be used for very effective heatmap presentation.

or this variant.

In each case. I have applied the heatmap template to the chart and automated its creation. As usual on this blog, the objective was to provide a capability to do the whole thing in one line of code, as below.


Public Sub heatMapExample()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet("matrixout"), , "matrixout", True, , , True, "matrix"), _
End Sub

For more details, and to download the full example please visit the ramblings site or contact me on our forum.

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

1 Comment

Comments are closed.