More than 255 data series

[Guest]

Is there any reasonable workaround for including more than 255 data series in
an Excel chart? Ideally, I need to be able to show over 1000 data series in
a chart.

Now, I know that you might question the legibility of a chart with that many
data series, so here is a chart with 255 data series:
http://www.cs.washington.edu/homes/deibel/pics/timeline-from-excel.gif

This chart shows time series data for a person working on a problem and the
problem-solving stages they engage in during the process. It's basically 8
parallel timelines. I have written a macro that generates the timeline by
using essentially floating columns. However, this requires a large number of
data series to display the entire timeline... hence my question.

Altogether, the choice of 255 series seems arbitrary. While having an upper
limit theoretically prevents people from making bad charts, I've seen plenty
of bad charts with only 5 series.

kate

 

[Jon Peltier]

Hi Kate -

The series limit is an old one, one of the 2^n-1 limits from the old days of
tight RAM, small hard drives, and slow processors. It still makes sense in
most cases.

I have an alternate way to generate this kind of chart. Use an XY chart. For
X use whatever is the value along your horizontal axis. For Y use 1 through
8, the stage number. Use no marker for the points. Instead use Y axis error
bars with a small value: try 0.25 and adjust it until you like it.

You can even get the entire chart in a single XY series if you want, or one
series per stage, or however you want to handle it. But you're no longer
throwing away one series per plotted point (or per small number of points).

- Jon

 

[Guest]

Thanks, but that visualization doesn't quite work. The data that I have
gives start times and stop times for each duration in a stage. I'd have have
to generate a data point for each time point in the durations (or at least
enough to make the bars look solid). Given the length of the durations in
question plus how the higher-ups want the timelines to look, this just won't
work.

What would be nice if it was possible to do stacked columns/bars by defining
a data series for each bar instead of the current way of having one data
series per layer.

Oh well, we'll just use Excel to export the data and then render it in a
separate application that I'm writing.

kate

 

[Del Cotter]

Thanks, but that visualization doesn't quite work. The data that I have
gives start times and stop times for each duration in a stage. I'd have have
to generate a data point for each time point in the durations (or at least
enough to make the bars look solid). Given the length of the durations in
question plus how the higher-ups want the timelines to look, this just won't
work.

Have you considered an adaptation of Jon's matrix chart technique for
Excel, using a Date x-axis? If your timescale is less than a day, you'll
have to fake it; the Date scale option was added for business users, and
is the only interval scale format possible in many Excel chart types,
but it was only enabled for days at the minimum.

The matrix chart technique is here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=508

I hope you see how to adapt it for your needs. If the chart never shows
simultaneous work on more than one stage at a time, then two series are
all that is needed: one for the bars and one for the float (float up the
stages instead of along the times). If the chart sometimes shows more
than one stage being worked on at a time, then you need sixteen series:
eight stages and eight floating intervals underneath each stage.

 

[Jon Peltier]

Actually, this example shows how the stacked area chart would work in this
context:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=533

- Jon

 

[Jon Peltier]

Also, you can fake the time scale axis to get whatever resolution you need.
The area series don't need to know the exact time or units or whatever the
axis is based on, they just need a way to know where to plot their values.
This technique uses a time scale axis, with the scale max set to 1001 and
the min to 1; 1000 gives plenty of resolution.

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

- Jon