Fake Line Chart (Dummy XY Series for X Axis)

In Excel, the difference between Line charts and XY charts has nothing to do with formatting the data with or without lines, and everything to do with different behavior of the X axes in the charts. I’ve written about these differences numerous times, in X Axis: Category or Value?, Line Charts vs. XY Charts, Line-XY Combination Charts, and in innumerable forum and newsgroup posts.

Comparison of Line Charts and XY Charts

Essentially, the difference is that Line charts plot X values as nonnumerical categorical values, like {A, B, C}; XY charts treat X values as continuously varying numerical values. Here is a brief comparison of the two chart types:

Line Charts XY Charts
Nice date scaling (e.g., first of each month) No special date scaling
Integer values only: Data plotted directly on category or on integer day numbers (midnight at start of each date) Continuous values: Data plotted anywhere along axis (e.g., any fractional time of day, not just midnight)
All series use same X values (same dates or categories) Each series uses independent X values
Identical series formatting:
lines or no lines, markers or no markers
Identical series formatting:
lines or no lines, markers or no markers
Only vertical (Y) error bars can be applied Vertical (Y) and horizontal (X) error bars may be applied

A Typical Line Chart

A user is simply trying to create an XY scatter plot in Excel where the X axis values as shown in the example below can be maintained as-is on the final graph’s X axis points:

Line Chart Data

This data is perfectly suited for a line chart.

Line Chart

When you try to use the data in an XY chart, Excel ignores the non-numerical X values, and substitutes counting numbers, 1 for the first category, 2 for the second, etc.

XY Chart Step 1

For some reason, the user is insisting that a scatter plot is used, and all they want is the X axis to end up exactly as the line graph is formatted. I suspect this is due to a lack of understanding of the axis differences above, but I’ll never know, because this user is someone else’s client.

Making an XY Chart Mimic a Line Chart

An XY chart can be used to display this data, but it is a poor second choice. The tricks that are needed to make an XY chart display the nonnumeric labels like a line chart make the fake labels static, and they must be rebuilt if the amount of data expands, or if rows are inserted or deleted.

But I understand users, so here is the second option, which is what he thinks he wants, not what he needs.

We need to adjust the data. Since the Line chart X values are unsuited for an XY chart, we must insert a column of valid X values. To accommodate the Line chart style axis labels, we will use a dummy XY series along the X axis, which serves as placeholders for data labels which will look like the Line chart labels. The dummy series uses the column of zeros.

XY Chart Data

Select the yellow shaded range, and make an XY chart.

XY Chart Step 2

Hide the standard X axis labels but maintain the margin beneath the axis by using custom number format of ” ” (space character). If you simply format the axis with no labels, the plot area will be too close to the bottom of the chart, without leaving room for the dummy labels.

XY Chart Step 3

Use Rob Bovey’s Chart Labeler (a free utility that is indispensable for creating and manipulating custom labels) to label the dummy series. Apply the labels in the first column of the data (not shaded yellow in my example) to the “Label” series.

Each label remains linked to its cell. If the chart stays at 4 points using the same range, the labels will update as these cells are changed. Stretching the range or inserting/deleting rows will force you to rebuild the axis.

XY Chart Step 4

Finally hide the “Label” series by formatting it without markers or lines.

XY Chart

That’s a long way to go to mimic a completely dynamic Line chart with an XY chart which may need subsequent maintenance if the data changes. The wrong wrench, as we say, to hammer in the wrong screw.

Peltier Tech Update

Last week I had the pleasure of attending the Juice Analytics Viva Visualization tour at the Juice Boston Tea Party. They served breakfast, then presented their take on data visualization. Basically, they remind us to send a message, keep the visualization simple, put it in context, and follow good design fundamentals. We’ve heard the message many times before, but it’s worth retelling, especially by these experts who build solutions for big clients. Among the takeaways are:

  • Don’t let novelty obscure the data.
  • Don’t let visuals obscure the data.
  • Choose the right chart type.

There is a lot of great content on the Juice web site and blog, including a guide for choosing the right chart.

The Learning As You Go blog has a nice article about plotting highly skewed data, at Graphing Highly Skewed Data. The article covers use of secondary axes (a bad idea), breaks in the axis (also a bad idea), logarithmic axis scales (okay if users understand log scales), and multiple charts. This article is a response to a discussion started by Chandoo in How do you make charts when you have lots of small values but few extremely large values?

 

Peltier Tech Charts for Excel

Order of Points in XY and Line Charts

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

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
 

 

Peltier Tech Charts for Excel

Excel Category Axis Types

In Line Charts vs. XY Charts I described the difference between these two Excel chart types. There is no difference in the ways you can format the series, so perhaps the names of the chart types are confusing. The differences are in the ways their respective X axes treat the X data. I discussed the differences in that post, and in Line-XY Combination Charts I showed how to make a combination Line-XY chart that combines the nice date formatting of a Line chart’s date scale axis with the more flexible plotting that an XY chart allows along the X axis.

In this post I will illustrate the behavior of the different category types in more detail. [Read more…]

 

Peltier Tech Charts for Excel

Line Charts vs. XY Charts

In Line-XY Combination Charts I showed how to make a combination Line-XY chart. It is probably important to discuss the differences between line charts and XY charts. The documentation is not clear, and the names of the chart types are not helpful; in fact, they lead to confusion.

The icons in the Chart Type dialog and in the Chart Wizard do not help to clarify the situation. These are the icons for line charts and XY charts in the Excel 2003 dialogs; the Excel 2007 icons are not substantially different.

Chart Types

It is important to note that:

  • The formatting options of XY chart series and line chart series are identical.
  • If you want markers connected by lines, you DO NOT have to use a line chart type.
  • If you want markers without connecting lines, you DO NOT have to use an XY chart type.
  • XY and line charts treat X data differently and thus have different X axis styles.

After selection of a chart type, the user is presented with a set of choices, and the default sub-type for the selected chart type is highlighted. Not all available formatting for these chart types is available through the Chart Type dialog. [Read more…]

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0