complex column chart question

[Keith R]

I helped a co-worker create a column chart for her data; she wanted to graph
3 discrete populations on one chart (using different colors, but not
changing each bar color individually) so we set it up as three data series
as follows:


Graph:
|
|
| |
| | | | | |
| | | | | | | | |
|__|___|___|_____|___|___|_____|__|___|_____
1a 1b 1c 2a 2b 2c 3a 3b 3c

to get all the 1's the same color, all the 2's the same color, and all the
3's the same color, I set it up like this:

1a 2 0 0
1b 3 0 0
1c 2 0 0
( ) 0 0 0
2a 0 3 0
2b 0 3 0
2c 0 4 0
( ) 0 0 0
3a 0 0 1
3b 0 0 2
3c 0 0 3

then I set the columns to 100% overlap, and the graph looks just like she
wanted....but now....

her boss wants to put in another data series, to make this look like a
regular 2-series column chart, but keep the colors grouped as left third
blue, middle third green, right third red. the new series will be a lighter
color of each third, e.g. light blue on the left third, etc.

so it would look something like:
Graph: (square bracket indicates new column series (3) where the brackets
1's are light blue, 2's are light green, etc
|
|
|
| ]
| ] | ] | | | ]
| ]
| | ] | ] | ] | ] | ] | ] ] | ]
| ]
|__|_]__|_]__|_]____|_]__|_]__|_]____|_]_|_]__|_]____
1a 1b 1c 2a 2b 2c 3a 3b 3c

is there any way to add this new series in the existing chart based on how I
set it up? If not, what would be the optimal way to set up the source data
sheet to allow me to have what looks like a 2-series column chart, but where
the colors for each third are automatically grouped?
TIA,
Keith

 

[Jon Peltier]

Keith -

You can carry the arrangement further:

1a 2
1a] 1
1b 3
1b] 2
1c 2
1c] 3
( )
2a 3
2a] 1
2b 3
2b] 2
2c 4
2c] 3
( )
3a 1
3a] 1
3b 2
3b] 2
3c 3
3c] 3

In a column chart, zeros and blanks are equally good in the table.

- Jon

 

[Keith R]

Thank you Jon-

Now that I have my data in that configuration, how do I set up the chart so
the first three data columns to 100% overlap and appear as "series 1", and
the second set of three data columns to overlap 100% and appear as "series
2" next to series one, so it looks like there are two data series? At the
moment, they still (all) overlap 100%, so the 1a value of 2 hides the 1a]
value of 1 (sometimes the values are reversed, so series order doesn't look
sufficient (plus my coworker's boss wants these two "series" side by side)

I'm starting to think I'm going to have to play with a secondary X axis, and
figure out a way to have the two "series" both overlap 100% within that
"series", but use the different X axis to somehow space them so they dont
overlap...I'm not sure where to start...

Many thanks,
Keith

Keith -

You can carry the arrangement further:

1a 2
1a] 1
1b 3
1b] 2
1c 2
1c] 3
( )
2a 3
2a] 1
2b 3
2b] 2
2c 4
2c] 3
( )
3a 1
3a] 1
3b 2
3b] 2
3c 3
3c] 3

In a column chart, zeros and blanks are equally good in the table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

I helped a co-worker create a column chart for her data; she wanted to graph
3 discrete populations on one chart (using different colors, but not
changing each bar color individually) so we set it up as three data series
as follows:


Graph:
|
|
| |
| | | | | |
| | | | | | | | |
|__|___|___|_____|___|___|_____|__|___|_____
1a 1b 1c 2a 2b 2c 3a 3b 3c

to get all the 1's the same color, all the 2's the same color, and all the
3's the same color, I set it up like this:

1a 2 0 0
1b 3 0 0
1c 2 0 0
( ) 0 0 0
2a 0 3 0
2b 0 3 0
2c 0 4 0
( ) 0 0 0
3a 0 0 1
3b 0 0 2
3c 0 0 3

then I set the columns to 100% overlap, and the graph looks just like she
wanted....but now....

her boss wants to put in another data series, to make this look like a
regular 2-series column chart, but keep the colors grouped as left third
blue, middle third green, right third red. the new series will be a lighter
color of each third, e.g. light blue on the left third, etc.

so it would look something like:
Graph: (square bracket indicates new column series (3) where the brackets
1's are light blue, 2's are light green, etc
|
|
|
| ]
| ] | ] | | | ]
| ]
| | ] | ] | ] | ] | ] | ] ] | ]
| ]
|__|_]__|_]__|_]____|_]__|_]__|_]____|_]_|_]__|_]____
1a 1b 1c 2a 2b 2c 3a 3b 3c

is there any way to add this new series in the existing chart based on how I
set it up? If not, what would be the optimal way to set up the source data
sheet to allow me to have what looks like a 2-series column chart, but where
the colors for each third are automatically grouped?
TIA,
Keith

 

[Keith R]

To add one more thought...

I tried using stacked charts (thinking that the first "series" would be one
stacked group, and the second "series" would be another stacked group, but
apparently stacked column charts don't have a setting to allow you to show
data values with those data labels showing up outside (above) the column-
and these charts (which scale from 0-100) do need data labels showing above
the columns.

If there is a trick or technique to automatically show stacked column data
labels above the column (since alternate series values will be null, and
therefore not show a label, there won't be any overlap) I could use that
method instead.....

Thanks!
Keith

Keith -

You can carry the arrangement further:

1a 2
1a] 1
1b 3
1b] 2
1c 2
1c] 3
( )
2a 3
2a] 1
2b 3
2b] 2
2c 4
2c] 3
( )
3a 1
3a] 1
3b 2
3b] 2
3c 3
3c] 3

In a column chart, zeros and blanks are equally good in the table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

I helped a co-worker create a column chart for her data; she wanted to graph
3 discrete populations on one chart (using different colors, but not
changing each bar color individually) so we set it up as three data series
as follows:


Graph:
|
|
| |
| | | | | |
| | | | | | | | |
|__|___|___|_____|___|___|_____|__|___|_____
1a 1b 1c 2a 2b 2c 3a 3b 3c

to get all the 1's the same color, all the 2's the same color, and all the
3's the same color, I set it up like this:

1a 2 0 0
1b 3 0 0
1c 2 0 0
( ) 0 0 0
2a 0 3 0
2b 0 3 0
2c 0 4 0
( ) 0 0 0
3a 0 0 1
3b 0 0 2
3c 0 0 3

then I set the columns to 100% overlap, and the graph looks just like she
wanted....but now....

her boss wants to put in another data series, to make this look like a
regular 2-series column chart, but keep the colors grouped as left third
blue, middle third green, right third red. the new series will be a lighter
color of each third, e.g. light blue on the left third, etc.

so it would look something like:
Graph: (square bracket indicates new column series (3) where the brackets
1's are light blue, 2's are light green, etc
|
|
|
| ]
| ] | ] | | | ]
| ]
| | ] | ] | ] | ] | ] | ] ] | ]
| ]
|__|_]__|_]__|_]____|_]__|_]__|_]____|_]_|_]__|_]____
1a 1b 1c 2a 2b 2c 3a 3b 3c

is there any way to add this new series in the existing chart based on how I
set it up? If not, what would be the optimal way to set up the source data
sheet to allow me to have what looks like a 2-series column chart, but where
the colors for each third are automatically grouped?
TIA,
Keith

 

[Jon Peltier]

Keith -

Two ways to draw the chart. Make a stacked column chart, with gap width
set to zero, or make a clustered column chart with gap width set to zero
and overlap set to 100.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Thank you Jon-

Now that I have my data in that configuration, how do I set up the chart so
the first three data columns to 100% overlap and appear as "series 1", and
the second set of three data columns to overlap 100% and appear as "series
2" next to series one, so it looks like there are two data series? At the
moment, they still (all) overlap 100%, so the 1a value of 2 hides the 1a]
value of 1 (sometimes the values are reversed, so series order doesn't look
sufficient (plus my coworker's boss wants these two "series" side by side)

I'm starting to think I'm going to have to play with a secondary X axis, and
figure out a way to have the two "series" both overlap 100% within that
"series", but use the different X axis to somehow space them so they dont
overlap...I'm not sure where to start...

Many thanks,
Keith

Keith -

You can carry the arrangement further:

1a 2
1a] 1
1b 3
1b] 2
1c 2
1c] 3
( )
2a 3
2a] 1
2b 3
2b] 2
2c 4
2c] 3
( )
3a 1
3a] 1
3b 2
3b] 2
3c 3
3c] 3

In a column chart, zeros and blanks are equally good in the table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Keith R wrote:

I helped a co-worker create a column chart for her data; she wanted to

graph

3 discrete populations on one chart (using different colors, but not
changing each bar color individually) so we set it up as three data

series

as follows:


Graph:
|
|
| |
| | | | | |
| | | | | | | | |
|__|___|___|_____|___|___|_____|__|___|_____
1a 1b 1c 2a 2b 2c 3a 3b 3c

to get all the 1's the same color, all the 2's the same color, and all

the

3's the same color, I set it up like this:

1a 2 0 0
1b 3 0 0
1c 2 0 0
( ) 0 0 0
2a 0 3 0
2b 0 3 0
2c 0 4 0
( ) 0 0 0
3a 0 0 1
3b 0 0 2
3c 0 0 3

then I set the columns to 100% overlap, and the graph looks just like

she

wanted....but now....

her boss wants to put in another data series, to make this look like a
regular 2-series column chart, but keep the colors grouped as left third
blue, middle third green, right third red. the new series will be a

lighter

color of each third, e.g. light blue on the left third, etc.

so it would look something like:
Graph: (square bracket indicates new column series (3) where the

brackets

1's are light blue, 2's are light green, etc
|
|
|
| ]
| ] | ] | | | ]
| ]
| | ] | ] | ] | ] | ] | ] ] | ]
| ]
|__|_]__|_]__|_]____|_]__|_]__|_]____|_]_|_]__|_]____
1a 1b 1c 2a 2b 2c 3a 3b 3c

is there any way to add this new series in the existing chart based on

how I

data

where