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:

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:

This data is perfectly suited for a 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.

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.

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

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.

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.

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

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?

I’ve written about Excel chart error bars in Error Bars in Excel Charts for Classic Excel and in Error Bars in Excel 2007 Charts for New Excel. Both articles contained instructions for adding custom error bar values for individual points, but judging from the emails I receive, a separate article on custom error bars is needed.

Manually Defining Custom Error Bars

Sample Data and Charts

Suppose we have the following data: X and Y values, plus extra columns with positive and negative error bar values for both X and Y directions.

Read more »

In Plotting Values Against Limits, I showed a few ways to compare some values (in this case, hourly wages) against recommended limits. In this post I’ll show a Box Plot approach to displaying this same data. The box plot will include the familiar boxes for the inner quartiles and whiskers for the outer quartiles calculated from the actual data, and it will be embellished with the actual values and with recommended limits on the values.

The Data

The data is rearranged as shown below. The blue rectangle at the left contains the wages data; the column of 1.125 are the X values that set the position of all of the points. The gray box contains percentile data calculated from the wage values using the formula =PERCENTILE(range,percentile) or =PERCENTILE($B$2:$B$11,D2) with the percentile as a percentage or decimal fraction.The red highlighted range contains the recommended limits on the hourly wage; the column of 1.25 values are used to position these data points.

Read more »

In a comment to another post, Lynn buttered me up a bit, then asked how to make a certain chart for her boss. Lynn has to plot ten employees’ hourly wages against recommended maximum, minimum, and medium wages. Only four actual wages fall within the recommended limits. I can think of many ways to plot actual values against limits, and I’ll show a few here. If you have any favorite approaches, tell us about them in the comments.

Lynn didn’t provide the actual values, so I constructed this little table.

Read more »

New and Improved

Announcing the new and improved Peltier Tech Chart Add-Ins for Microsoft Excel. They were pretty good to begin with, but there have been some changes. I’ve updated all of the add-ins, plus tested them in umpteen versions and languages of Excel, repackaged them, and uploaded them.

If you have purchased one of these add-ins and are experiencing problems, let me know and I’ll send you a link to the new version as soon as I can.

If you’re not having problems, but want to use the newer version, let me know and I’ll shoot you a link, within a week.

Excel 2010 Compatibility

The charting add-ins already worked in Excel 2000 through 2007, and I recently had made the minor changes to the installer that detected and installed in Excel 2010. But that was the 32-bit version of Excel 2010.

Read more »

Last weekend I transferred my email to a new system in the cloud (did I really just say “in the cloud”??) and I came across an old email from my last “real” job. That was my only job ever where I worked in a cubicle, and it was awful for that and for so many other reasons.

Anyway, the email had a Top Ten list from David Letterman, from about September 2003. I hunted for the original list, and I found the list reposted many times, but not the original. And I decided I don’t like people who add a few more items to the list, because their items are never funny.

Without further ado, here’s…

Top Ten Drawbacks to Working in a Cubicle

Read more »

A very common task you may have is to take a chart you’ve painstakingly formatted and use it with new data. I described a few ways to handle this in Make a Copied Chart Link to New Data.

Most commonly you have a worksheet with a bunch of data and a corresponding chart, and you have another sheet of data you want to add a chart to. Copying and pasting the chart onto the new sheet requires you to change links in the chart, usually series by series. This is tedious and error-prone. But the article above describes an easier way:

1. Make a copy of the worksheet with the old data and chart;
2. Copy the new data;
3. Paste the new data over the old data on the copied worksheet.

Read more »

For the past two months or so, I have been absent from the blog. Sure, I’ve answered questions and replied to comments people have left. But new articles have been scarce.

I have plenty of excuses for this, and none of them involve not having any topics worth posting. I’ll describe a few of them, and you can judge how valid they are.

Read more »

This blog has been silent for three weeks, due to a number of other activities. To re-energize the blog, we turn to Chart Busters to clean up some unattractive and hard-to-read charts.

In Ugly Real Economic Growth Chart from Singapore Statistics, an Excel training service shows an ugly chart, then proposes another equally ugly chart as an alternative. Chart Busters will describe these charts, and propose better alternatives.

Ugly Original Chart

Read more »

Excel Charting and Dashboarding

Westborough MA, June 2

Jon Peltier (Peltier Technical Services) and Alex Kerin (Data Driven Consulting) have put together an intensive one-day Excel Charting and Dashboarding session. Topics include:

• Intentions and approaches
• Data quality
• Effective graphic techniques
• Designing a dashboard
• Combination charts
• Dynamic and interactive charts
• Advanced chart formatting
• Charting and dashboard tools for Excel

If you have any questions, contact Jon Peltier.