| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart format & data selection?
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? |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic chart format & data selection?
How about formulaically (without VBA)?
http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic chart format & data selection?
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. On Feb 15, 1:00 pm, "Jon Peltier" wrote: How about formulaically (without VBA)? http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic chart format & data selection?
Except that the formulas automatically adjust if the input data changes.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... 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. On Feb 15, 1:00 pm, "Jon Peltier" wrote: How about formulaically (without VBA)? http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic chart format & data selection?
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 On Feb 15, 4:00 pm, "Jon Peltier" wrote: Except that the formulas automatically adjust if the input data changes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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. On Feb 15, 1:00 pm, "Jon Peltier" wrote: How about formulaically (without VBA)? http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic chart format & data selection?
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... 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 On Feb 15, 4:00 pm, "Jon Peltier" wrote: Except that the formulas automatically adjust if the input data changes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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. On Feb 15, 1:00 pm, "Jon Peltier" wrote: How about formulaically (without VBA)? http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart empty with new data in the dynamic range chart. | Excel Discussion (Misc queries) | |||
| TIP: Dynamic chart selection without code | Charts and Charting in Excel | |||
| I would like to format a dynamic chart title | Charts and Charting in Excel | |||
| Data selection on line chart | Charts and Charting in Excel | |||
| re-selection of Chart data range using keyboard | Excel Worksheet Functions | |||
Linear Mode
