Home/ Questions/Q 11474
Next
Answered
computing
  • 1
Asked: 2021-12-08T04:14:36+00:00 In:

Density Plot From Scatter Plot In Excel

  • 1

Hey Guys,

I’ve had some really good help from this site in the past so I’d like to ask another question.

I have a set of 5000 data points for 2 conditions put in columns A and B. I use the normal scatter plot in Excel to generate a graph but all the data point overlap making it impossible to see if there is an area of greater overlap within the data.

What I’d like to do is create a density plot of the same data, so that the more points that piled on top of each other the brighter the colour. Is that possible in Excel?

Many thanks for your help

Lucas

Share

    1 Answer

    1. Best Answer
      You can plot as bubble chart and either color the interior of each point or change transparency level for each point. You can also assign size to the bubbles…

      I use the following to make data point 1 solid and data point n transparent… the size of the bubbles also vary… you can modify to iterate over RGB values instead of the static black color i’ve assigned.

      k = ActiveSheet.ChartObjects.Count

      For j = 1 To k
      ActiveSheet.ChartObjects(j).Activate
      n = ActiveChart.SeriesCollection(1).Points.Count

      For i = 2 To n
      ActiveChart.SeriesCollection(1).Points(i).Select
      With Selection
      .Format.Fill.Solid
      .Interior.ColorIndex = 1
      .Format.Fill.Transparency = (1 – (n – i) / (n + i))
      End With
      Next i

      • 0