Solve an iWork/Excel data paste and charting glitch

Mar 03, '05 08:26:00AM

Contributed by: robg

I've been spending a lot of time recently with iWork -- both Keynote and Pages. During that time, I've continued to find things in the programs that amaze me ... and other things that frustrate me. The amazing features are just that -- amazing (some of the things you can do with images, transparency, and grouping are quite impressive). However, the frustrating items are very frustrating. Here's one such example, along with a workaround.

If you're trying to use Pages or Keynote to make a simple chart (such as that seen at right; click the chart for a larger version), and you're using data from Excel, you may run into this interesting iWork 'feature.' The chart at right is a simple stock history chart, and if you're using Excel, your data would more than likely look something like the image below-left.

Below the two header entries, the first column contains the year, and the second, the stock price. The years have simply been typed in. From Excel's perspective, these are numbers -- excluding the last year, 2005, which has an 'e' at the end to indicate it's an estimate. You can see the difference as Excel has left-aligned the text, and right-aligned the numbers.

Read the rest of the hint for an explanation of how Pages and Keynote will treat this data when you paste it, as well as the workaround solution...

Typically, to plot this data in Keynote (the rest of this hint applies equally to Pages, but I'll just use Keynote for the example), you would first select and copy the whole range in Excel, including the header row. Upon switching to Keynote, you'd add a chart to the slide, then click on "Region 1" in the Chart Data Editor, and hit paste. But if you do that with this data set, you'll get this result:


Notice how the years have not been pasted into the left-column label area, but have rather become the first data column in your chart. This is clearly not what you wanted to have happen; you wanted the years as the tag, with only the price as a variable to be plotted.

The cause of this problem seems to be some 'intelligence' that Keynote is applying to the copied data. Since the first entry in column number one (below the header row) is a value, Keynote assumes that it's something that you'd like to plot, and not the labels for the X or Y axis. You will not see this problem if the data you've copied contains text in the first column. To prove that, change the years to something like "Q1," "Q2," "Q3," etc., and re-copy and paste. When you do that, you'll see that Keynote correctly pastes the quarter numbers as labels (into the "Region" column in the Chart Data Editor). Since there were no values there, Keynote assumed that those were the labels for the data you wish to plot.

This tidbit provides the workaround solution to the problem. It seems that Keynote only examines the first entry in the list when making its determination of "plot value" versus "label." So to fool Keynote into doing what we want, just change the first year (1997) in Excel into text. The easiest way to do this is to just add a character after the year -- "1997b," for instance. Now re-select the range, copy, and paste into the same Region 1 cell in Keynote, and you'll get the results you wanted in the first place:


Just edit the first entry to remove the 'b,' and you're done (well, nearly done -- click the "Plot Row vs. Column" button to make the years the X-axis, and delete the extra columns that Keynote provided automatically). I spent the better part of an hour on this issue yesterday, trying to figure out why my pastes weren't working right ... so hopefully this will save someone the same frustrating experience.

Comments (4)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20050303052654580