# 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

Note that this method of calculating the heatmap scale has been superseded by the Color ramp library See Charts and color ramps for how to make interesting charts using color ramps

#### 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.

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

To smooth out the transition of colors, I just ask for more categories as follows (in this case 50)

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

that gives us this

Finally, here is how to get a different kind of heatmap

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

gives us

#### Code for chart automation

You will find the code and explanation for this in Charts and color ramps.