Flexibly specifying data series in charts

[hmm]

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.

 

[Del Cotter]

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.

 

[hmm]

Thanks, Del.

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

 

[Jon Peltier]

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