Using Slicer to Filter data on Scatter Chart

[Neelia]

I have been using slicers in order to filter the data that I have displayed on a scatter chart. I was wondering if it was possible that when selecting multiple selections in the slicer, the different selections would show up on the scatter chart in different colours/markers.

Or is there a different way to easily filter data? I cannot use pivot charts as I need the data in a scatter graph and I cannot put each selection as a different series as there is more than what is allowed on one graph.


Thanks!

 

[Worf]

Welcome to the Board

• Newer Excel versions have chart filters as shown on the picture below. What’s your version?
• Is your chart source data a real table? Are the slicers connected to this table? Please give more details on the data layout and setup. A link to your workbook would be ideal, with sensitive data removed.
• VBA code can be written to manipulate the chart elements, if you require a personalized solution based on the current slicer selections.

 

[Neelia]

Hi Worf,

Apologies, I do not know how to send a link to my workbook.

Basically I have an XY Scatter, lets say the data is dimensions in the X and Y axis. I would like to then compare these dimensions for each month say. If I select July on the slicer then only July data is shown. But if I select July and August then both months are shown however they are the same series so I cannot distinguish between the two months. My data is in a table format.

Thank you for your help

 

[Worf]

To share a file, you must upload it to a site like Drop Box and paste a link here.
See if this helps; clicking one button makes all markers the same colour, clicking the other one applies month based colour code.

' sheet module
Private Sub CommandButton3_Click()  ' all markers the same
Me.ChartObjects("Chart 8").Chart.SeriesCollection(1).MarkerBackgroundColor = RGB(230, 5, 10)
End Sub


Private Sub CommandButton4_Click()  ' color code by month
Dim s As Series, xv, i%, r%, g%, b%
Set s = Me.ChartObjects("Chart 8").Chart.SeriesCollection(1)    ' the first series
xv = s.XValues
For i = LBound(xv) To UBound(xv)
    Select Case Month(xv(i))
        Case 1: r = 35: g = 98: b = 156
        Case 2: r = 178: g = 65: b = 32
        Case 3: r = 213: g = 167: b = 218
        Case 4: r = 134: g = 95: b = 31
        Case 5: r = 21: g = 6: b = 218
        Case 6: r = 178: g = 217: b = 97
        Case 7: r = 145: g = 174: b = 175
        Case 8: r = 176: g = 5: b = 132
        Case 9: r = 6: g = 111: b = 96
        Case 10: r = 76: g = 214: b = 112
        Case 11: r = 79: g = 89: b = 65
        Case 12: r = 167: g = 112: b = 223
    End Select
    s.Points(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next
End Sub