Using a scatter graph to create a heatmap - how can I easily add multiple series

[micksandals]

Hello,

I'm attempting to create a risk heat map in Excel (something like this, but with a more rigid scale). Currently I do this in PowerPoint and manually move the circles between boxes as and when the scores change, but I'm sure there must be an easier, more automated way to do it.

My initial thought was a scatter graph, but I'm learning that scatter graphs don't seem to like my table of data.

I have the Risk ID in A, Likelihood Score in B and Impact Score in C:

[table="width: 500"]
[tr]
[td]Risk ID[/td]
[td]Likelihood[/td]
[td]Impact[/td]
[/tr]
[tr]
[td]001[/td]
[td]3[/td]
[td]4[/td]
[/tr]
[tr]
[td]002[/td]
[td]3[/td]
[td]3[/td]
[/tr]
[tr]
[td]003[/td]
[td]2[/td]
[td]4[/td]
[/tr]
[tr]
[td]004[/td]
[td]3[/td]
[td]3[/td]
[/tr]
[/table]
etc.

I want the scatter graph to plot the points using Column B as the X axis values, Column C as the Y axis values and Column A as the Series Name.

I also want all the markers to be circles, all the same size, with data labels showing the Series Name inside the circle. I'm planning to use a formula to "tweak" the Impact and Likelihood scores to avoid the markers overlapping where the score is the same.

Obviously I can do this all manually, but that defeats the purpose. This is also something I'm hoping to share with a colleague who is having the same issue so it needs to be as simple as possible. I'm happy with a VBA (or partial VBA) solution, but be aware that my VBA skills are limited.

Can anyone help?

I'm using Excel 2010, and I'm not in a position to download any add-ins or separate programs unfortunately.

Thanks in advance

 

[pgc01]

Hi

Do you need multiple series?

Why not just 1 series, X values in B and Y values in C?

 

[micksandals]

Hi

Do you need multiple series?

Why not just 1 series, X values in B and Y values in C?

Thanks for the response.

I need to display all of the risks we have in our log, so I need a point on the graph for each of them based on their Impact and Likelihood. Without treating each risk as a different series I wouldn't be able to identify which point on the graph related to which risk - that's why I want to use the series name in the data label.

That's assuming there isn't another way to do that, which there may well be.

 

[pgc01]

Hi

You just need to add the labels to the points. To make the labels the names in column A you can write a simple vba snippet to do it, but the usual solution is to use Rob Bovey's XY Chart Labeler:


The XY Chart Labeler Add-in

 

[micksandals]

Hi

You just need to add the labels to the points. To make the labels the names in column A you can write a simple vba snippet to do it, but the usual solution is to use Rob Bovey's XY Chart Labeler:


The XY Chart Labeler Add-in

Thanks, I managed to find some VBA to achieve the same thing (here, if anyone's interested) and it's working like a charm.

Thanks again

 

[pgc01]

You're wellcome. I'm glad your problem is solved.

 

[Jon Peltier]

Note: I wrote this an hour ago, but somehow it got into a loop and wouldn't post. But here it is again....

You could write a macro to do each row as its own series:

Sub OneSeriesPerRow()
  Dim cht As Chart
  Dim rng As Range
  Dim iRow As Long
  
  ' define range, something like one of these
  'Set rng = ActiveSheet.Range("A1").CurrentRegion
  Set rng = Selection ' select 3-column range
  
  Set cht = ActiveSheet.Shapes.AddChart.Chart
  With cht
    .ChartType = xlXYScatter
    Do While cht.SeriesCollection.Count > 0
      .SeriesCollection(1).Delete
    Loop
    
    For iRow = 2 To rng.Rows.Count
      With .SeriesCollection.NewSeries
        .Values = rng.Cells(iRow, 3)
        .XValues = rng.Cells(iRow, 2)
        .Name = "=" & rng.Cells(iRow, 1).Address(, , , True)
        .HasDataLabels = True
        With .DataLabels
          .Position = xlLabelPositionCenter
          .ShowSeriesName = True
          .ShowValue = False
        End With
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 24
      End With
    Next
  End With
End Sub

Or you could do it with just one series, and link each point's data label to the cell in the first column:

Sub OneSeriesTotal()
  Dim cht As Chart
  Dim rng As Range
  Dim iPt As Long
  
  ' define range, something like one of these
  'Set rng = ActiveSheet.Range("A1").CurrentRegion
  Set rng = Selection ' select 3-column range
  
  Set cht = ActiveSheet.Shapes.AddChart.Chart
  With cht
    .ChartType = xlXYScatter
    .SetSourceData rng.Offset(0, 1).Resize(, 2)
    With .SeriesCollection(1)
      .MarkerStyle = xlMarkerStyleCircle
      .MarkerSize = 24
      .HasDataLabels = True
      .DataLabels.Position = xlLabelPositionCenter
      For iPt = 1 To .Points.Count
        .Points(iPt).DataLabel.Text = "=" & rng.Cells(iPt + 1, 1).Address(, , , True)
      Next
    End With
  End With
End Sub