Creating Multiple-Series Charts with as few steps as possible

[ecl]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I routinely create charts with more than one series (e.g. several columns of data with x-axis labels in the first column and series names in the first row).

I can't get Excel to automatically recognize my first column of data as the x-axis and the first row of data as either the legend labels or y-axis (in 3D charts like a surface chart). The way I think it should work is:

1. I select the rectangle of data including x-axis labels in the first column and series names for the legend or y-axis (in 3D charts) in the first row.

2. I click the Chart button and select any chart format (e.g. Line, Scatter, 3D Surface, etc...) and it automatically recognizes the 3 regions of data to create the chart.

Anyone figured out how to do this without manually entering the data one series at a time or manually going to the Select Data dialog box and manually fixing every Series name and x-axis labels?

Thx
Ed

 

[Mike Middleton]

Ed -

Your data arrangement seems OK, i.e., labels in the left column and labels in the top row.

But, to achieve what you want, it is important that the top left cell of your selection be empty (Edit Clear Contents).

If so, the 3D Surface and 3D Column charts will use the left column and top row for axis labels.

And, if so, a Line chart will use the left column for the X axis and create separate data series for each of the other columns.

For an XY (Scatter) chart type, I arrange X data in a column on the left and Y data in an adjacent column on the right. Then I select only the data values, e.g., A2:B101, and insert an XY (Scatter) chart. I add axis titles later.

- Mike
http://www.MikeMiddleton.com


Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I routinely create charts with more than one series (e.g. several columns of data with x-axis labels in the first column and series names in the first row).

I can't get Excel to automatically recognize my first column of data as the x-axis and the first row of data as either the legend labels or y-axis (in 3D charts like a surface chart). The way I think it should work is:

1. I select the rectangle of data including x-axis labels in the first column and series names for the legend or y-axis (in 3D charts) in the first row.

2. I click the Chart button and select any chart format (e.g. Line, Scatter, 3D Surface, etc...) and it automatically recognizes the 3 regions of data to create the chart.

Anyone figured out how to do this without manually entering the data one series at a time or manually going to the Select Data dialog box and manually fixing every Series name and x-axis labels?

Thx
Ed

 

[ecl]

okay, thanks, that must be the problem, since I usually generate the data using the Data:Table... function.

Of course to use Excel's 2D Data:Table... function that corner cell is never empty. I guess the folks working on the Charts part of the software development didn't talk to the developers working on the Data part of the software development. Bummer!

 

[Mike Middleton]

Ed -

My workaround is to Copy the two-variable data table, Paste Special Values, and Clear the top left cell. Then create the 3-D chart.

Step-by-step instructions are on page 26 of "Sensitivity Analysis Using Excel," available as a free PDF download from the Chapters web page at http://www.treeplan.com.

- Mike
http://www.MikeMiddleton.com


okay, thanks, that must be the problem, since I usually generate the data using the Data:Table... function.

Of course to use Excel's 2D Data:Table... function that corner cell is never empty. I guess the folks working on the Charts part of the software development didn't talk to the developers working on the Data part of the software development. Bummer!