ordering series one completely after each other (multiple X-axis?)

#1 05-27-2005, 09:05 AM

Hello,

I have a set of 250 items describing the same event for different items over
the last years.
I would like to chart these items and still be able to differenciate them by
year.

ex:
Year Item Value
1995 A 10
1995 B 10
1995 C 9
1995 D 8
1996 E 10
1996 F 8
1996 G 6
.... and so on

If I leave a blank line between 1995 and 1996 items then I can then spot the
gap from one year to another on the chart but it is not very practical as all
the data markers are the same color. Of course I can manually change the
color for each of the 250 items but then every time I add a new item or sort
the items differently in my table I need to change the colors again.

Is there any other way to represent the data with Excel 2000?

Maybe with multiple X-axis (for year and item), maybe by creating series for
each year and then ordering the series one completely after each other.

Thanks for your help-
Benjamin

#2 05-27-2005, 11:05 AM

Have you tried using a stacked bar chart? It's easier to do if the data is
in this format:
A B C D E F G
1995 10 10 9 8
1996 10 8 6

"Benjamin" wrote:

> Hello,
>
> I have a set of 250 items describing the same event for different items over
> the last years.
> I would like to chart these items and still be able to differenciate them by
> year.
>
> ex:
> Year Item Value
> 1995 A 10
> 1995 B 10
> 1995 C 9
> 1995 D 8
> 1996 E 10
> 1996 F 8
> 1996 G 6
> ... and so on
>
> If I leave a blank line between 1995 and 1996 items then I can then spot the
> gap from one year to another on the chart but it is not very practical as all
> the data markers are the same color. Of course I can manually change the
> color for each of the 250 items but then every time I add a new item or sort
> the items differently in my table I need to change the colors again.
>
> Is there any other way to represent the data with Excel 2000?
>
> Maybe with multiple X-axis (for year and item), maybe by creating series for
> each year and then ordering the series one completely after each other.
>
> Thanks for your help-
> Benjamin
>

#3 05-27-2005, 01:05 PM

Indeed, that's exactely what I'm looking for!
With some minor arrangements in my table I can easily get there.
Thanks for the help-

"Barb R." wrote:

> Have you tried using a stacked bar chart? It's easier to do if the data is
> in this format:
> A B C D E F G
> 1995 10 10 9 8
> 1996 10 8 6
>
> "Benjamin" wrote:
>
> > Hello,
> >
> > I have a set of 250 items describing the same event for different items over
> > the last years.
> > I would like to chart these items and still be able to differenciate them by
> > year.
> >
> > ex:
> > Year Item Value
> > 1995 A 10
> > 1995 B 10
> > 1995 C 9
> > 1995 D 8
> > 1996 E 10
> > 1996 F 8
> > 1996 G 6
> > ... and so on
> >
> > If I leave a blank line between 1995 and 1996 items then I can then spot the
> > gap from one year to another on the chart but it is not very practical as all
> > the data markers are the same color. Of course I can manually change the
> > color for each of the 250 items but then every time I add a new item or sort
> > the items differently in my table I need to change the colors again.
> >
> > Is there any other way to represent the data with Excel 2000?
> >
> > Maybe with multiple X-axis (for year and item), maybe by creating series for
> > each year and then ordering the series one completely after each other.
> >
> > Thanks for your help-
> > Benjamin
> >

#4 05-27-2005, 03:05 PM

I know I'm late to the dance, but this page shows how to do this kind of
conditional chart formatting with formulas do make it less burdensome to
make changes later:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Benjamin wrote:

> Indeed, that's exactely what I'm looking for!
> With some minor arrangements in my table I can easily get there.
> Thanks for the help-
>
>
> "Barb R." wrote:
>
>
>>Have you tried using a stacked bar chart? It's easier to do if the data is
>>in this format:
>> A B C D E F G
>>1995 10 10 9 8
>>1996 10 8 6
>>
>>"Benjamin" wrote:
>>
>>
>>>Hello,
>>>
>>>I have a set of 250 items describing the same event for different items over
>>>the last years.
>>>I would like to chart these items and still be able to differenciate them by
>>>year.
>>>
>>>ex:
>>>Year Item Value
>>>1995 A 10
>>>1995 B 10
>>>1995 C 9
>>>1995 D 8
>>>1996 E 10
>>>1996 F 8
>>>1996 G 6
>>>... and so on
>>>
>>>If I leave a blank line between 1995 and 1996 items then I can then spot the
>>>gap from one year to another on the chart but it is not very practical as all
>>>the data markers are the same color. Of course I can manually change the
>>>color for each of the 250 items but then every time I add a new item or sort
>>>the items differently in my table I need to change the colors again.
>>>
>>>Is there any other way to represent the data with Excel 2000?
>>>
>>>Maybe with multiple X-axis (for year and item), maybe by creating series for
>>>each year and then ordering the series one completely after each other.
>>>
>>>Thanks for your help-
>>>Benjamin
>>>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)