Line chart, multiple series, data is inter-mixed

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I'd like to avoid having to sort and arrange my data, so I'm wondering
if it is possible
to create a category-based line chart with several series, but with
the source data intermixed. The category to which each data point
belongs appears in a cell directly
adjacent to the data cell.

In other words, can I tell Excel that I'd like to chart a several
series from a data
range, and provide a 2nd range which defines the categories of the
corresponding
data points?

For example, my data might be in four categories (calories, speed,
temp, weight) with
five data points in each category, and might appear in the worksheet
like this:

calories 3500
speed 65
temp 98.6
weight 185
calories 4000
speed 68
temp 99.0
weight 170
calories 3320
speed 46
temp 102.5
weight 192
calories 3400
speed 50
temp 98.6
weight 187
calories 4000
speed 68
temp 99.0
weight 170

Previously, I've solved this by sorting into four separate regions,
then define the
chart's four series out of each region.

But this doesn't scale easily, as I will be routinely creating charts
containing
over 30,000 data points distributed among 14 categories.

Thanks,
-Bob
Andover, MA
 
This is one time you can't skip steps; you have to explicitly tell Excel
which data to plot. These are all Y variables; what are you using for X? And
why aren't you creating your table in record format, with one column per
field?

- Jon
 
For example, my data might be in four categories (calories, speed,
temp, weight) with
five data points in each category,
Click to expand...
Previously, I've solved this by sorting into four separate regions,
then define the
chart's four series out of each region.

But this doesn't scale easily, as I will be routinely creating charts
containing
over 30,000 data points distributed among 14 categories.
Click to expand...

If you populated the column to left with a formula like
IF(B3="calories",A2+1,A2) you would have a range like so:

A B C
1 calories 3500
1 speed 65
1 temp 98.6
1 weight 185
2 calories 4000
2 speed 68
2 temp 99
2 weight 170
3 calories 3320
3 speed 46
3 temp 102.5
3 weight 192
4 calories 3400
4 speed 50
4 temp 98.6
4 weight 187
5 calories 4000
5 speed 68
5 temp 99
5 weight 170

Can you make a pivot table or pivot chart out of that to look like this?

A calories speed temp weight
1 3500 65 98.6 185
2 4000 68 99 170
3 3320 46 102.5 192
4 3400 50 98.6 187
5 4000 68 99 170
 
Back
Top