A recent post to the Excel Charting Newsgroup asks "I would like the color of my data points to vary based not on their x or y value, but rather on a third column running alongside them. How can I do it?" A worksheet solution would be to create an XY series for each option that appears in the third column. For example, the screenshot below shows a worksheet with the original XY values in columns A and B. The colors of each point depends on the name entered into column C. Columns E through J contain formulas that determine the series that the data point falls into. If the value in column C is "Jim", the data corresponding to Jim appears in columns E and F. If the value in column C is "Frank", the data corresponding to Frank appears in columns G and H. Finally, if the value in column C is "Kim", the data corresponding to Kim appears in columns I and J.

The formulas are as follows:

The formula in cell E3 is:

=IF($C3="Jim",$A3,NA())

The formula in cell F3 is:

=IF($C3="Jim",$B3,NA())

The formula in cell G3 is:

=IF($C3="Frank",$A3,NA())

The formula in cell H3 is:

=IF($C3="Frank",$B3,NA())

The formula in cell I3 is:

=IF($C3="Kim",$A3,NA())

Finally, the formula in cell J3 is:

=IF($C3="Kim",$B3,NA())

Each of these formulas is copied through their respective ranges. For example, the formula in cell E3 is copied through the range E3 to E11.

Conditional formatting of the range E3:J11 is used to mask the errors. To do so, go to Format -> Conditional Formatting. Enter the formula in the Conditional Formatting dialog box as shown below:

The formatted color is set to white to mask the error messages. Copy the format through the range E3:J11.

The three series that act as the source for the XY Scatter Chart are entered into the Chart Source Data dialog box. An example showing the first series "Jim" is shown below:

The example showing the second series "Frank" is below:

Finally, the last example showing the third series "Kim" is below:

A second option would be to use VBA to color each point. An example of a VBA routine to do so is below:

Sub ChangeXYColors()
    Application.ScreenUpdating = False
    Dim Rng As Range
    Cnt = 1
    For Each Rng In Range("C3:C11")
    ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.SeriesCollection(1).Points(Cnt).Select
        If Rng.Value = "Jim" Then
            With Selection
                .MarkerBackgroundColorIndex = 5
                .MarkerForegroundColorIndex = 5
            End With
        ElseIf Rng.Value = "Frank" Then
            With Selection
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 3
            End With
        ElseIf Rng.Value = "Kim" Then
            With Selection
                .MarkerBackgroundColorIndex = 10
                .MarkerForegroundColorIndex = 10
            End With
        End If
    Cnt = Cnt + 1
    Next Rng
ActiveChart.Deselect
End Sub

The worksheet option and the VBA option both return a chart that looks like this:

You could simply share the X range among all series, and just make the Y ranges conditional.

The code can be punched up to avoid selection flicker, and substituting Select Case, which I find more readable than If-ElseIf-Then:

In fact, I usually read in the range as an array and cycle through it using Cnt. But that’s too much work for Friday morning.