Heat Maps are an excellent way to present a lot of data in a single view, and can be created using Excel.  It can also be used to identify trends in data that you would lose if you tried to average or aggregate the data to make it presentable.  By using a Heat Map you avoid destroying some of the value of your data.

The way to create a Heat Map with Excel is to select the Surface Chart type.  Then you want to remove the lines from between the series, and adjust the number of increments in the scale.

By default, Excel's surface charts display black lines between each color gradient, which makes the chart look strange (and not like a heat map).

Before Converting to a Heat Map

You can remove those lines, but it is manual and tedious.  Here's how to do it:

1. Click the chart legend to select it
2. Click the first legend key (that is the small colored square)
3. Right-click and choose Format Legend Key
4. In the Format Legend Key dialog box, click the Patterns tab
5. Select None for the Border option, and click OK

Repeat those steps for every series in your chart.

If you want to do it automatically, you can use the following macro:

Sub RemoveLinesandSetColors()
Dim LE As Integer
Dim red As Integer
Dim green As Integer
Dim blue As Integer
Dim a As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim q As Integer

red = 0
green = 0
blue = 255

LE = 1

If ActiveChart Is Nothing Then Exit Sub
If MsgBox("Remove lines from surface chart and set colors?", vbYesNo) = vbYes Then
Application.ScreenUpdating = False
ActiveChart.HasLegend = True
a = ActiveChart.Legend.LegendEntries.Count
x = a / 4
y = Int(255 / x)

For z = 1 To 4
For q = 1 To x
ActiveChart.Legend.LegendEntries(LE).LegendKey.Border.LineStyle = xlNone
ActiveChart.Legend.LegendEntries(LE).LegendKey.Interior.Color = RGB(red, green, blue)

If z = 1 Then green = green + y

If z = 2 Then blue = blue - y

If z = 3 Then red = red + y

If z = 4 Then green = green - y

LE = LE + 1
Next q

q = 1
Next z

End If

End Sub
To use this macro, copy it and paste it to a VBA module. Then, select a surface chart and press Alt+F8 to display the Macro dialog box. Execute the RemoveLinesandSetColors macro, and your chart will be changed. Note that the macro also changes the color scale to a blue-to-red spectrum.

The number of colored gradients in a surface chart is determined by the Major Unit setting for the vertical axis. Right-click the legend and select the Scale tab.  Change the value for the Major Unit. The larger the number, the fewer the colors.  Set the scale to an increment that, for your values, will give you a good range of colors.

One note:  VBA is pretty picky about some strange things.  For example, the macro will fail if the Legend Key displayed on the screen does not show all of the legend key entries.  If you have 100 entries but a few of them are outside of the Legend Key box, the macro will fail.  One way to get around this is to change the font size to 1 for the legend, and make the legend box as big as possible.  Then run the macro, then change the Legend Key Font back to whatever you want.

This VBA macro above not only removes the lines, but sets the color scale to a spectrum from blue to red, with blue being low values and red being high.  It increments the colors based on the number of Legend Key entries.

I hope this helps you as you try to create a Heat Map in Excel.

Linda - http://how.best-free-information.com - The "How" Blog