How can I color dots in a xy scatterplot according to column value

Question

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

User · Answer

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  quot red quot   C2 NA     IF  D2  quot orange quot   C2 NA     IF  D2  quot green quot   C2 NA    3  Xerox     45       38       red          IF  D3  quot red quot   C3 NA     IF  D3  quot orange quot   C3 NA     IF  D3  quot green quot   C3 NA    4  KMart     63       50       orange       IF  D4  quot red quot   C4 NA     IF  D4  quot orange quot   C4 NA     IF  D4  quot green quot   C4 NA    5  Exxon     53       59       green        IF  D5  quot red quot   C5 NA     IF  D5  quot orange quot   C5 NA     IF  D5  quot green quot   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

User · Answer

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   lt  gt       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

User · Answer

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  lt   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  lt  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

User · Answer

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  lt  10 Then             Chart1 Series  a   Points AddXY x  y          ElseIf j  lt  30 Then             Chart1 Series  b   Points AddXY x  y          ElseIf j  lt  50 Then             Chart1 Series  c   Points AddXY x  y          ElseIf 50  lt  j Then             Chart1 Series  d   Points AddXY x  y          Else             Chart1 Series  Chart 1   Points AddXY x  y          End If     Next

User · Answer

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

User · Answer

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

[vba] How can I color dots in a xy scatterplot according to column value?

Non-VBA Solution:

Examples related to vba

Examples related to excel

Examples related to charts

Examples related to excel-formula