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?, Scatter Chart or Line Chart?, 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 Chart Utility

Comments

  1. Hi – I have a question about brickyard charts. I solved my initial problem – I needed a brickyard chart where some of the bricks overlapped in both the x and y directions. I figured that out – there are just more “plants” (to use the terminology of your brickyard.xls example) than one would normally expect. But how do you change the axes to be not from 0 to n but rather from m to n?

    Thanks for any and all help – your web site is awesome!

    Eric Vanhove

  2. Eric – I suppose you could explicitly define X axis values that went from m to n, instead of letting Excel use 1, 2, 3, . . . .

  3. Hello,

    I have attached a link to a file where I am trying to create a dummy axis to label a band. This band was created from the data on the file. The “actual” values are a column chart created from the data in row 2, plotted on a secondary axis, and the band is a stacked column chart created from the data in rows 3:5, plotted on the primary axis. This stacked chart is formatted to only show the “OK” band, the “unacceptable” data (rows 3 & 5) are with no fill.

    I am trying to apply the labels in the yellow highlighted area to the green band, but not succeeding.

    Can you please help? It is a safe file without macros.

    http://www.keepandshare.com/doc/2309177/labeling-a-band-xls-october-18-2010-6-24-pm-25k?da=y

  4. 1. Where do you want the labels. X=0 is off-scale. Using an XY series, you need X values between 0.5 and 7.5.
    2. Aren’t these values redundant, given the numerical Y axis scales on both sides?

  5. Thank you for your prompt reply.

    1. I am trying to get the labels (only 2 – 80% and 95%) on the dummy Y axis to the left of the axis (is there any other place these could be applied?). I am not very good with XY charts, and as such not quite clear about your comments regarding off-scale. Can you pl explain a bit in detail?

    2. Actually these are micro charts on a dashboard, and there are 6 such charts on the report. As such the axes are hidden. The OK band has different values for different charts, and hence a general comment for all charts is not appropriate.

    Thank you once again.

  6. Manoj -

    You only need two points for your labels, both with X=0.5, one for each displayed Y value (80% and 95%). Why do you need 0.5? You have seven categories, and Excel assigns them values of 1 through 7. The position between the first two categories, i.e., between 1 and 2, has a value of 1.5, so extrapolate back to the Left axis, between the first category and where the one before that would go. This position has a value of 0.5, which the XY chart series will understand.

    Copy the range with the two points, select the chart, Paste Special as new series by column (categories in first column, series names in first row). Right click the series in the chart, choose (Change) Chart Type, select XY with markers only. Add data labels to these points using the Value option (which will show the percentages), position the labels to the left of the points, then hide the markers (format series with no lines and no markers). Now hide the Y axes. In fact, you should delete the secondary Y axis so all series will use the primary axis, and there will not be any risk that the two axes will not be aligned. You should pick a lighter shade for the horizontal band to increase contrast between the actual data and the band.

  7. Sir,

    You are an absolute genius! Thanks for your help.

    The concept of XY charts is a bit confusing. Is there some way to get some more knowledge? Maybe I missed some articles on your website.

    Once again thanks for the solution.

Subscribe without commenting

Trackbacks

  1. [...] This post was mentioned on Twitter by Jon Peltier, Excel Insider. Excel Insider said: Fake Line Chart (Dummy XY Series for X Axis): In Excel, the difference between Line charts and XY charts has nothi… http://bit.ly/bdFMNJ [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites