Data points between markers

Question

I would like to have markers on my scatter plot, but I have too many data points and the markers overlap.  Is there a way to use all the data, but have data points between markers?

Answer 1

Everybody from the respondents here could have proposed the same method as your brother did. There is only the question, whether this would be able to give the public true image of reality, as you had certainly intended. A regression line would be able to represent correct statistical treatment in average; it can be completed with standard error lines. Then the swarm of original points could optically reflect the character of scatter. A danger can occur that just the eliminated points could be those that draw the statistical parameters in an unexpected direction; this is a nuisance of solitary distant points. And this is the reason, why you were advised to use different size markers (I wonder why there is a simple macro on the net). Of course, the truth is that this danger is the lower the higher is the count of points. Your proposal to use only 1 out of 250 points suggests that you have to do with ten thousands; perhaps this would be OK. For a reasonable number it is sufficient to depress the marker + size into minimum 2 to 3 points.

Petr Bezucha

Answer 2

I asked my brother and he came up with a Macro that does the job

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-alt:"Calisto MT"; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-alt:"Century Gothic"; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} -->

Sub removeMarkers()

Dim c As Chart

Dim s As Series

Dim p As Point

Dim i As Long

i = 0

Dim j As Long

j = 250 'interval between markers

Set c = ActiveChart

For Each s In c.SeriesCollection

    For Each p In s.Points

        If i Mod j = 0 Then

            p.MarkerStyle = xlMarkerStyleAutomatic

        Else

            p.MarkerStyle = xlMarkerStyleNone

        End If

        i = i + 1

    Next

Next

End Sub

Answer 3

Excel 2007/2010 Chart

Turn on and format selected data point markers.

With macro.

http://c0718892.cdn.cloudfiles.rackspacecloud.com/08_26_10.xlsm

http://www.mediafire.com/file/cfmd2gg30jgu9f2/08_26_10.pdf

Answer 4

Thanks.  That would work except now I would have two lines in the legend, correct?

Answer 5

I think that all points in a data series of an XY (Scatter) chart type have the same marker (unless you tediously select individual points and format those points differently). Perhaps you could use a small-size marker for all points in the data series, and create a second data series (using a selected subset of the original data) that uses a larger marker.

-  Mike Middleton

http://www.DecisionToolworks.com

Decision Analysis Add-ins for Excel

http://www.MikeMiddleton.com