While writing last week’s introductory article about Excel 2013 charting, My First Excel 2013 Chart, I discovered a very cool bit of intelligence built into the function that inserts XY charts.
In prior versions of Excel, if you selected a rectangular range with N columns and inserted an XY chart with data in columns, the chart would use the first column for the X values, and the other columns for Y values for N-1 series. If your series didn’t all use the same X values, you had to define each series separately.
While playing with the Insert Chart dialogs, I discovered that Excel 2013 will offer several additional options if the data is arranged a certain way.
I’ll illustrate this coolness with the following data. There is an 8×8 grid of numerical data, with text labels (Greek letter names) in the row above the grid, and text labels (Latin letters) in the column to the left of the grid. Thedata is better suited for a line or bar chart because of the non-numerical categories in the first row and column.
On the Insert tab, click on the XY Chart icon and you’ll see this dropdown.
Click on “More Scatter Charts” at the bottom, and you’ll see this dialog.
There are thumbnails for three options, which will be discussed shortly.
If you mouse over any of the options, you’ll see a preview of how that chart would look. Double clicking on the thumbnail or preview inserts the chart into the worksheet.
The first XY chart option is what you would get in prior versions of Excel. The first column is used for the X values, and the other N-1 columns are the Y values. In this case, the first column contains text labels, which an XY chart cannot accommodate, so it uses the counting numbers 1, 2, 3, etc. instead.
The second option available in this case shows one of the intelligent behaviors. Excel realizes that the first column doesn’t contain numerical values, so it ignores that column, and makes a chart with the number in the rest of the data range. The first numerical column is used for X, and the rest for Y.
I tested this with one, two, and three leading columns of non-numerical data, and Excel ignored them all. Presumably it will ignore any arbitrary number of leading columns of text.
That’s pretty smart. But the next trick, as we say here in Baaston, is “wicked smaat”.
Many times I’ve been asked how to select a range with alternating X and Y values and create an XY chart with one series for each pair of columns. In Quick Chart VBA Examples I presented code that parses such a range and spits out the desired chart. In that tutorial I showed how to process several different arrangements of X and Y, including alternating X and Y columns.
If your usable numerical data is contained in an even number of columns (either standalone, or with some data ignored as above), Excel will give you the option to treat the data as alternating columns of X and Y data. The result is this Wicked Smaat XY Chaat:
When the chart is selected, there is no highlighted data range, because the chart’s data is “too complicated” to display. This is because the series don’t share their X values.
If you select the first series, however, you can see that it uses the first column of the range for X and the second for Y.
And if you select the second series, you can see that it uses the third column of the range for X and the fourth for Y. And so on, for the rest of the series i the chart.
I discovered this intelligent behavior by accident, using some arbitrary data from a totally different example. But knowing about this intelligence, I can design a data range to take advantage of it.
Here is an even number of columns of numerical data, alternating X and Y values.
Here’s my wicked smart XY chart created using this data. The default formatting has markers but no lines.
You can use the Change Chart Type feature to change from a markers-only to a lines-and-markers format.
A lot of people are going to be very happy with this new functionality.
It’s okay, I suppose, but the general problem is still that Excel tries to “intelligently” pick the data series, ignoring the intelligence on the other side of the screen. I’m pleased they’re going a little way out of the universal “block” assumption for charts, but I’d be more pleased if they adopted the sort of wizard format you have in your applications, where you first ask the users how they would like the selected data to be arranged.
Derek -
You mean, like the 2003 Chart Wizard? Yeah. The first thing people would ask, when making a chart in 2007 for the first time, is “Where can I find the Wizard?”
In fact, I thought that the “old” Chart Wizard, upgraded in Excel 97, left out some of the good stuff from the earlier version. For example, when selecting the data in Excel 5/95, the Chart Wizard would make the same assumptions about the data range, but you could override the selection and indicate whether to use one or more rows for category labels and series names.
One improvement in the workflow of this new Chart Type feature, though, is that Excel recognizes different options (like data in rows or in columns, or the three XY data options shown in my first example above) and shows them to you for you to select one, instead of imposing one and forcing you to fix it.
As always, excellent insight into nuances of Excel.
With Microsoft dropping support for Windows XP and Excel 2003 in April of 2014, we’re all going to have to embrace the new directions of Excel and Windows 7/8.
I have 20+ charts of analytical data in an excel 2010 file. Each XY chart illustrates 3 to 6 sampling locations with about 50 daily data points each. The vertical lines indicate “events” are are drawn by inserting 2 data points on the same day, one at 0 and one at 10,000. The problem is that when the events are added, Excel churns because (I think) the 0-10000 range is so far outside of the data range needed to specify the desired parameter. The chart posted is for pH, but there are several other parameters, most of which are less than 10. Is there another way to show the event? I tried it as a single dot, but the line is much better to illustrate the differences from one event to another. The events are in a separate worksheet from the analytical data for both ease of entry and the ability to eyeball a running list of what happened when. Any guidance would be very much appreciated. Excel XY Chart for pH is here: http://share.shutterfly.com/share/received/welcome.sfly?fid=a83cae9ded7010355b2f2841c4d4a52e&sid=0QaNHLFmxbtnFA
Laura -
If it’s a small handful of data points at 10000, a single value way out of range shouldn’t be a problem; try with 10 or 100 to compare. You also have only dozens of points, so the chart isn’t overloaded.
Nice post,
Don’t forget you can also further customise your charts (recommended or otherwise) with three great new tools in Excel 2013.
http://www.microsofttraining.net/b/exceltraining/2013/05/excel-2013-charts/
Terence -
Well, two great new tools. The styles (middle button) should probably be avoided. Too much gratuitous formatting. And the first tool is not new, it simply places the chart element controls next to the chart (where they were on the context menu in 2003 & earlier) rather hidden on the ribbon. But that third one, for filtering the chart data without removing data in the source data dialog or hiding rows and column, is genius.