Flexibly specifying data series in charts
 

I have tables with many columns. I want to create a chart that will allow me
to specify which columns to use for x- and y-axes, preferably with two
pull-down menus. Basically, I want to avoid the present situation where I
create a new chart every time I want to observe the correlation between two
variables.

Another refinement: Sometimes one column will indicate a category for data
points (e.g., column title could be element name; categories: oxygen,
hydrogen, aluminum, calcium, etc.). Can I make the charts to plot one or
more categories as separate data series? (Again, preferably with a pull-down
menu.)

Thanks.

Flexibly specifying data series in charts
 

On Sun, 13 Jan 2008, in microsoft.public.excel.charting,
hmm said:

I have tables with many columns. I want to create a chart that will allow me
to specify which columns to use for x- and y-axes, preferably with two
pull-down menus. Basically, I want to avoid the present situation where I
create a new chart every time I want to observe the correlation between two
variables.

The method I use is to create an x and y column which consists of
INDEX() functions, which refer to the column of interest via a cell at
the top of the column

INDEX(range,row,column)

You can refine this system as much as you want, depending on how much
effort you want to put into it.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Flexibly specifying data series in charts
 

Thanks, Del.

Just one follow-up questions: I have never used pivot charts. How can pivot
charts help me here?

"Del Cotter" wrote:

On Sun, 13 Jan 2008, in microsoft.public.excel.charting,
hmm said:

I have tables with many columns. I want to create a chart that will allow me
to specify which columns to use for x- and y-axes, preferably with two
pull-down menus. Basically, I want to avoid the present situation where I
create a new chart every time I want to observe the correlation between two
variables.

The method I use is to create an x and y column which consists of
INDEX() functions, which refer to the column of interest via a cell at
the top of the column

INDEX(range,row,column)

You can refine this system as much as you want, depending on how much
effort you want to put into it.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Flexibly specifying data series in charts
 

You can rearrange pivot fields easily in a pivot table, dragging the field
you want as X values to the rows area, dragging the others to the data area,
and dragging ones you don't want off the pivot table.

Another option, related to Del's suggestion using INDEX or OFFSET to fill
dedicated X and Y value worksheet columns with the desired data, uses
dropdowns or listboxes to let you decide easily by clicking on the desired
column:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hmm" wrote in message
...
Thanks, Del.

Just one follow-up questions: I have never used pivot charts. How can
pivot
charts help me here?

"Del Cotter" wrote:

On Sun, 13 Jan 2008, in microsoft.public.excel.charting,
hmm said:

I have tables with many columns. I want to create a chart that will
allow me
to specify which columns to use for x- and y-axes, preferably with two
pull-down menus. Basically, I want to avoid the present situation where
I
create a new chart every time I want to observe the correlation between
two
variables.

The method I use is to create an x and y column which consists of
INDEX() functions, which refer to the column of interest via a cell at
the top of the column

INDEX(range,row,column)

You can refine this system as much as you want, depending on how much
effort you want to put into it.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.