Order of Points in XY and Line Charts

There has been a lot of discussion here lately about XY and Line charts:

• Line Charts vs. XY Charts
• Line-XY Combination Charts
• Category Axis Tricks for Line and Area Charts – 1
• Category Axis Tricks for Line and Area Charts – 2

One interesting thing about a line chart with a date scale X axis is the order of the plotted points. Consider a data set like this, in which the points are out of chronological order.

The line chart internally sorts the data by date, and connects the points in this order, while the XY chart connects them in the order they appear in the sheet.

Here are the same charts as above, with labels on each point indicating the order of each point. Points 1 and 3 of each point are the same, but the other points have different index values depending on the chart type, because the points were out of date order in the worksheet.

I used the following simple procedure to number the points in a series. Select a series, then press Alt-F to pop up the macros dialog (or access the dialog through Tools menu > Macros). Select the macro name from the list, and press run.

Sub NumberThePointsInASeries()
  Dim iPt As Long
  If TypeName(Selection) = "Series" Then
    For iPt = 1 To Selection.Points.Count
      With Selection.Points(iPt)
        On Error Resume Next
        .HasDataLabel = True
        .DataLabel.Characters.Text = CStr(iPt)
        On Error GoTo 0
      End With
    Next
  Else
    MsgBox "Select a series and try again.", vbExclamation
  End If
End Sub