Create a heatmap in Excel

Creating heatmaps

An easy way to create heatmaps is to use conditional formatting in your spreadsheet.

However, we are going to look at creating heatmaps as an Excel Chart, and also to automatically create a heatmap in a table of data. As usual this code is implemented in the cDataSet.xlsm module downloadable from Download Complete Projects

The HeatMap scale

When I was researching this topic, I had some problems figuring out how to use RGB codes to create a smooth transition from blue to red. Finally I came across this write up http://paulbourke.net/texture_colour/colourramp/ from which I was able to create my first simple VBA function to calculate an RGB code from a value's position in a range, and which will become smoother the bigger the range.

Here is the code for that. Note that this method of calculating the heatmap scale has been superseded by the Color ramp library so this is for informational purposes only.

Private Function heatMapColor(min As Variant, _

max As Variant, Value As Variant) As Long

Dim spread As Double, ratio As Double, red As Double, _

green As Double, blue As Double

spread = max - min

Debug.Assert spread >= 0

ratio = (Value - min) / spread

If ratio < 0.25 Then

blue = 1

green = 4 * ratio

ElseIf ratio < 0.5 Then

green = 1

blue = 1 + 4 * (min - Value + 0.25 * spread) / spread

ElseIf ratio < 0.75 Then

green = 1

red = 4 * (Value - min - 0.5 * spread) / spread

Else

red = 1

green = 1 + 4 * (min - Value + 0.75 * spread) / spread

End If

heatMapColor = RGB(red * 255, green * 255, blue * 255)

End Function

Automating heatmap to a table of data

The first option is simply to use the values in a table and color the cell with the appropriate heatmap color. As usual, the data is abstracted using Data Manipulation Classes which makes the mechanics pretty simple.

In this case I colored the heading row with the heatmap scale, and used the values in the table to determine the 'hotness' of the background cell color.

Here is the code, to read the data and color the heading row and the data.

Public Sub testHeatMapScale()

Dim dsout As New cDataSet, dc As cCell

Dim mx As Variant, mn As Variant, dr As cDataRow

With dsout.populateData(wholeSheet("heatmapcolors"), , , , , , True)

mx = .max.Value

mn = .min.Value

' do the heading as a heatmap scale

For Each dc In .HeadingRow.Headings

dc.where.Interior.color = heatMapColor(1, .ColumnCount, dc.Column)

Next dc

'now the data

For Each dr In .Rows

For Each dc In dr.Columns

dc.where.Interior.color = heatMapColor(mn, mx, dc.Value)

Next dc

Next dr

End With

End Sub

Creating a heatmap chart

Excel provides a couple of surface charts that are pretty nice for heatmaps. Here is one, or see Charts and color ramps for a generalized version of this.

I have provided a generalized chartmaker in cDatSet.xlsm Download Complete Projects which makes the task just one line of code as follows.