Dynamic chart format & data selection?

  • Thread starter Thread starter jg70124
  • Start date Start date
J

jg70124

I have 3 columns of data: "Type", "Sales", "GM%".

I would like to create a series of scatters (one for each value of
"Type") with "Sales" on the x axis and GM% on the y axis. In the past
when I've had to do this, I've sorted by "type" then manually selected
the appropriate cells. But this time I have 40k rows with 35
different values of "Type". Furthermore, each chart will have two
different series ("Types") that will be differentiated by color.
Doing it by hand will take hours and hours - and the data is likely to
change later.

So is there a way to programatically select and color data series in
Excel 2007?
 
8^)

Should have put in my first post: "Already checked peltiertech.com"...

I have used that method in the past. In this case, with 35 different
types, that's a lot of helper columns - doesn't really save much time.
 
Of course you are correct.

Out of curiosity, is there a way to embed conditional formulae in the
chart's data definition - or some other way to do it without helper
columns?

Thanks
 
You could probably construct Names that have clever Refers To formulas, as
in dynamic charts:

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

but I usually find that the more clever your formula is, the harder it is to
debug when it's misbehaving. A couple dozen helper columns are easier to
work with and maintain than a half dozen Name definitions.

- Jon
 
Back
Top