Grouped scatterplot, categorical X-axis in Excel

I am trying to create a grouped scatterplot with categorical X-axis. My data and my current graph attempt:

I'm trying to group all the letters as points with the same x-value, with no letter being repeated... e.g. all D's vertically spread:

How can I accomplish this?

1 Answer

Here's one solution: Excel - Plotting different y-values on top of same x-values

It seems that the trick is in converting the labels to numbers (they probably haven't heard of nominal & ordinal data on ms, eh). That's pretty easy to achieve through sort and cumsum counter. Problem is, the X axis is then numerical, and manually playing with spacing as suggested yonder may not be viable on a large scale.

Another approach is merging the data to a single row per category - that's pretty straightforward, either via formula or vba - and then applying the scatter plot to the table. This will create a series for every column, each with a rainbow coloured shape. Correcting these simultaneously, I imagine, should be doable in vba with the right array property (haven't examined the documentation). Moreover, having blanks whilst generating the plot seems to confuse it, so I've replaced these with placeholder values initially (something like -412613) and then, once the plot is generated, replaced all placeholders with blanks, conveniently removing them from the chart without further consequences. So that's a larger one time expenditure to code, and then 2 mins to apply hence.

Or if you've access to better plotting software, well...

Edit (30/06/19): Upon request, the technical details shall be elaborated below. My randomised data is originally in A+C.

Cumsum:

B1=1 ; B2=if(a2=a1,b1,b1+1)

Merge using formula:

D1=1 ; D2 = if(a2=a1,0,1)
E1=if(index($a2:$a$999,column(a1),1)=$a1, _ value(index($c2:$c$999,column(a1),1)),-0.413612)

Paste E along all the rows and for as many columns as the greatest quantity of observations - you'll know when a filter of the column shows only placeholder. Filter according to D=1, paste values to another sheet, and delete D. I prefer this method personally since it's slightly more 'cross platform', works in libre.

Alternative - merge using vba:

Const BASESHEET As String = "Base"
Const MERGESHEET As String = "Merge2"
Sub MergePairs()
Dim rowcnt As Long
Dim srcsht
Dim trgsht
Dim pid As String
Dim stidx As Long
Dim trgidx As Long
Set srcsht = Sheets(BASESHEET)
Set trgsht = Sheets(MERGESHEET)
' Sort the data.
srcsht.Sort.SortFields.Clear
srcsht.Sort.SortFields.Add Key:=Range("$A:$A"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With srcsht.Sort .SetRange Range("$A:$C") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply
End With
' Copy unique ids.
rowcnt = srcsht.Cells(srcsht.Rows.Count, 1).End(xlUp).Row
srcsht.Range(srcsht.Cells(1, 1), srcsht.Cells(rowcnt, 1)).Copy
trgsht.Range("A1").PasteSpecial
trgsht.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
' Copy each set of points.
pid = ""
For i = 1 To rowcnt + 1 If pid = "" Then pid = srcsht.Cells(i, 1).Value stidx = i trgidx = 1 ElseIf pid <> srcsht.Cells(i, 1).Value Then srcsht.Range(srcsht.Cells(stidx, 3), srcsht.Cells(i - 1, 3)).Copy trgsht.Cells(trgidx, 2).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True pid = srcsht.Cells(i, 1).Value stidx = i trgidx = trgidx + 1 End If
Next i
End Sub

Don't forget to create a chart selecting all the values, then replace all placeholders with blanks.

Change ALL the markers: Seems the axis labels on a scatter chart aren't easily pliable (possibly even no permission to do so at all); in contrast, it's simple enough to get rid of the lines on a line chart. (Note, this changes all charts on the sheet so make sure it's alone or modify the loop to a parm.)

Sub XChart()
Dim chrt As ChartObject
Dim ser As Excel.Series
For Each chrt In Worksheets("Merge1").ChartObjects chrt.Chart.ChartType = xlLine For Each ser In chrt.Chart.SeriesCollection ser.Format.Line.Visible = msoFalse ser.MarkerStyle = xlMarkerStyleX ser.MarkerForegroundColor = RGB(0, 0, 0) ser.MarkerBackgroundColor = RGB(255, 255, 255) ser.MarkerSize = 7 Next ser
Next chrt
End Sub

A few images for demonstration next.

Selection phase:

Chart generated with this data (pay attention to the bottom line of placeholders):

Replacing placeholders with blanks:

Here's the final result, with every ounce of liveliness sucked right out of its bones.

Edit (24/04/20): As has been brought to my attention, it would appear excel 2013+ versions changed the line chart settings to value type axis, ostensibly without any means of altering it. However, column / bar chars do (still) use a category type axis; One possible low tech hack is creating a scatter / line plot as described, then a second bar chart of the labels + 0 values only - this creates an empty grid, which can be cleaned up of legends, ticks etc to leave the horizontal axis only. Then, alignment of the two charts is doable, albeit unsightly, by adjusting the the scatter's numerical range (roughly from 0 / 1 to the number of labels+) and column's width & position.It's possible to get a reference to the (excel.)axis through Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary), where the value / category differences are quite clear; notwithstanding, it wouldn't respond to any property change requests I tried.