How can I color dots in a xy scatterplot according to column value?
VbaExcelChartsExcel FormulaVba Problem Overview
Consider the following worksheet:
A B C D
1 COMPANY XVALUE YVALUE GROUP
2 Apple 45 35 red
3 Xerox 45 38 red
4 KMart 63 50 orange
5 Exxon 53 59 green
I have used the scatterplot function in Excel to create the following chart:
However, each point in the chart has an additional property: GROUP. There are four groups: red, orange, black and green. I would like to color each dot accordingly, so that I could perhaps see a pattern (group greenbeing almost always on the left side of the chart, for instance). Because my list is 500 rows long, I cannot do this manually. How can I do this automatically?
Vba Solutions
Solution 1 - Vba
Non-VBA Solution:
You need to make an additional group of data for each color group that represent the Y values for that particular group. You can use these groups to make multiple data sets within your graph.
Here is an example using your data:
A B C D E F G
----------------------------------------------------------------------------------------------------------------------
1| COMPANY XVALUE YVALUE GROUP Red Orange Green
2| Apple 45 35 red =IF($D2="red",$C2,NA()) =IF($D2="orange",$C2,NA()) =IF($D2="green",$C2,NA())
3| Xerox 45 38 red =IF($D3="red",$C3,NA()) =IF($D3="orange",$C3,NA()) =IF($D3="green",$C3,NA())
4| KMart 63 50 orange =IF($D4="red",$C4,NA()) =IF($D4="orange",$C4,NA()) =IF($D4="green",$C4,NA())
5| Exxon 53 59 green =IF($D5="red",$C5,NA()) =IF($D5="orange",$C5,NA()) =IF($D5="green",$C5,NA())
It should look like this afterwards:
A B C D E F G
---------------------------------------------------------------------
1| COMPANY XVALUE YVALUE GROUP Red Orange Green
2| Apple 45 35 red 35 #N/A #N/A
3| Xerox 45 38 red 38 #N/A #N/A
4| KMart 63 50 orange #N/A 50 #N/A
5| Exxon 53 59 green #N/a #N/A 59
Now you can generate your graph using different data sets. Here is a picture showing just this example data:
You can change the series (X;Y) values to B:B ; E:E, B:B ; F:F, B:B ; G:G respectively, to make it so the graph is automatically updated when you add more data.
Solution 2 - Vba
I answered a very similar question:
https://stackoverflow.com/a/15982217/1467082
You simply need to iterate over the series' .Points collection, and then you can assign the points' .Format.Fill.ForeColor.RGB value based on whatever criteria you need.
UPDATED
The code below will color the chart per the screenshot. This only assumes three colors are used. You can add additional case statements for other color values, and update the assignment of myColor to the appropriate RGB values for each.
Option Explicit
Sub ColorScatterPoints()
Dim cht As Chart
Dim srs As Series
Dim pt As Point
Dim p As Long
Dim Vals$, lTrim#, rTrim#
Dim valRange As Range, cl As Range
Dim myColor As Long
Set cht = ActiveSheet.ChartObjects(1).Chart
Set srs = cht.SeriesCollection(1)
'## Get the series Y-Values range address:
lTrim = InStrRev(srs.Formula, ",", InStrRev(srs.Formula, ",") - 1, vbBinaryCompare) + 1
rTrim = InStrRev(srs.Formula, ",")
Vals = Mid(srs.Formula, lTrim, rTrim - lTrim)
Set valRange = Range(Vals)
For p = 1 To srs.Points.Count
Set pt = srs.Points(p)
Set cl = valRange(p).Offset(0, 1) '## assume color is in the next column.
With pt.Format.Fill
.Visible = msoTrue
'.Solid 'I commented this out, but you can un-comment and it should still work
'## Assign Long color value based on the cell value
'## Add additional cases as needed.
Select Case LCase(cl)
Case "red"
myColor = RGB(255, 0, 0)
Case "orange"
myColor = RGB(255, 192, 0)
Case "green"
myColor = RGB(0, 255, 0)
End Select
.ForeColor.RGB = myColor
End With
Next
End Sub
Solution 3 - Vba
If you code your x axis text categories, list them in a single column, then in adjacent columns list plot points for respective variables against relevant text category code and just leave blank cells against non-relevant text category code, you can scatter plot and get the displayed result. Any questions let me know.
Solution 4 - Vba
I see there is a VBA solution and a non-VBA solution, which both are really good. I wanted to propose my Javascript solution.
There is an Excel add-in called Funfun that allows you to use javascript, HTML and css in Excel. It has an online editor with an embedded spreadsheet where you can build your chart.
I have written this code for you with Chart.js:
https://www.funfun.io/1/#/edit/5a61ed15404f66229bda3f44
To create this chart, I entered my data on the spreadsheet and read it with a json file, it is the short file.
I make sure to put it in the right format, in script.js, so I can add it to my chart:
var data = [];
var color = [];
var label = [];
for (var i = 1; i < $internal.data.length; i++)
{
label.push($internal.data[i][0]);
data.push([$internal.data[i][1], $internal.data[i][2]]);
color.push($internal.data[i][3]);
}
I then create the scatter chart with each dot having his designated color and position:
var dataset = [];
for (var i = 0; i < data.length; i++) {
dataset.push({
data: [{
x: data[i][0],
y: data[i][1]
}],
pointBackgroundColor: color[i],
pointStyle: "cercle",
radius: 6
});
}
After I've created my scatter chart I can upload it in Excel by pasting the URL in the funfun Excel add-in. Here is how it looks like with my example:
Once this is done You can change the color or the position of a dot instantly, in Excel, by changing the values in the spreadsheet.
If you want to add extra dots in the charts you just need to modify the radius of data in the short json file.
Hope this Javascript solution helps !
Disclosure : I’m a developer of funfun
Solution 5 - Vba
Try this:
Dim xrndom As Random
Dim x As Integer
xrndom = New Random
Dim yrndom As Random
Dim y As Integer
yrndom = New Random
'chart creation
Chart1.Series.Add("a")
Chart1.Series("a").ChartType = DataVisualization.Charting.SeriesChartType.Point
Chart1.Series("a").MarkerSize = 10
Chart1.Series.Add("b")
Chart1.Series("b").ChartType = DataVisualization.Charting.SeriesChartType.Point
Chart1.Series("b").MarkerSize = 10
Chart1.Series.Add("c")
Chart1.Series("c").ChartType = DataVisualization.Charting.SeriesChartType.Point
Chart1.Series("c").MarkerSize = 10
Chart1.Series.Add("d")
Chart1.Series("d").ChartType = DataVisualization.Charting.SeriesChartType.Point
Chart1.Series("d").MarkerSize = 10
'color
Chart1.Series("a").Color = Color.Red
Chart1.Series("b").Color = Color.Orange
Chart1.Series("c").Color = Color.Black
Chart1.Series("d").Color = Color.Green
Chart1.Series("Chart 1").Color = Color.Blue
For j = 0 To 70
x = xrndom.Next(0, 70)
y = xrndom.Next(0, 70)
'Conditions
If j < 10 Then
Chart1.Series("a").Points.AddXY(x, y)
ElseIf j < 30 Then
Chart1.Series("b").Points.AddXY(x, y)
ElseIf j < 50 Then
Chart1.Series("c").Points.AddXY(x, y)
ElseIf 50 < j Then
Chart1.Series("d").Points.AddXY(x, y)
Else
Chart1.Series("Chart 1").Points.AddXY(x, y)
End If
Next
Solution 6 - Vba
Recently I had to do something similar and I resolved it with the code below. Hope it helps!
Sub ColorCode()
Dim i As Integer
Dim j As Integer
i = 2
j = 1
Do While ActiveSheet.Cells(i, 1) <> ""
If Cells(i, 5).Value = "RED" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(255, 0, 0)
Else
If Cells(i, 5).Value = "GREEN" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(0, 255, 0)
Else
If Cells(i, 5).Value = "GREY" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(192, 192, 192)
Else
If Cells(i, 5).Value = "YELLOW" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(255, 255, 0)
End If
End If
End If
End If
i = i + 1
j = j + 1
Loop
End Sub