2-direction stacked bar chart

[Btibert]

Hello everyone,

Here is a problem that has been puzzling me. I am looking to create a column or bar chart that moves in two directions.

Essentially, I want to include 16 variables on my chart, 1 bar for each variable, and the four categories that make up each variable stack on each other. I am looking to have the variable name up the left side of the graph (ordinarily the Y axis), and the % axis on the bottom of the graph (x-axis.) For simplicity sake, lets call the categories of each variable C1-C4. Using 0% as the origin, I want the categories 1 and 2 to be to the left of the line (stacked on each other) and categories 3 and 4 to be to the right of the line, stacked on each other as well. The sum of all of the absolute %'s will equal 100 for the variable.

There are a few things I am hoping that you can help me with. First, I do not want to show the axis as ranging from -100% to +100%. Id prefer it to look like 100% counting down to 0% and then back up to 100%. Second, I want the label of each variable to be outside of the graph, or at least not on the "zero axis", rather I want to position each label so it can be clearly read.

Are there any special things I can do to trick excel into making the graph the way I want it to look?

[Bernard Liengme]

Make the right-side values negative.
Get back to us with more questions.
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Btibert" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone,
>
> Here is a problem that has been puzzling me. I am looking to create a
> column or bar chart that moves in two directions.
>
> Essentially, I want to include 16 variables on my chart, 1 bar for each
> variable, and the four categories that make up each variable stack on
> each other. I am looking to have the variable name up the left side of
> the graph (ordinarily the Y axis), and the % axis on the bottom of the
> graph (x-axis.) For simplicity sake, lets call the categories of each
> variable C1-C4. Using 0% as the origin, I want the categories 1 and 2
> to be to the left of the line (stacked on each other) and categories 3
> and 4 to be to the right of the line, stacked on each other as well.
> The sum of all of the absolute %'s will equal 100 for the variable.
>
> There are a few things I am hoping that you can help me with. First, I
> do not want to show the axis as ranging from -100% to +100%. Id prefer
> it to look like 100% counting down to 0% and then back up to 100%.
> Second, I want the label of each variable to be outside of the graph,
> or at least not on the "zero axis", rather I want to position each
> label so it can be clearly read.
>
> Are there any special things I can do to trick excel into making the
> graph the way I want it to look?
>
>
> --
> Btibert
> ------------------------------------------------------------------------
> Btibert's Profile:
> http://www.excelforum.com/member.php...fo&userid=8559
> View this thread: http://www.excelforum.com/showthread...hreadid=384973
>

[Jon Peltier]

You seem to be making a stacked tornado diagram. Here's how to make a
tornado diagram:

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

Use negative values for the left-pointing ones, and positive for the
right-pointing ones, and make a stacked bar chart.

> First, I do not want to show the axis as ranging from
> -100% to +100%. Id prefer it to look like 100% counting
> down to 0% and then back up to 100%.

Double click on the axis, select the Number tab, click on Custom, then
enter this:

0%;0%;0%

Which means use 0% for positive, 0% for negative (no minus sign), and 0%
for zero. More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

> I want the label of each variable to be outside of the graph,
> or at least not on the "zero axis", rather I want to position
> each label so it can be clearly read.

Double click on the axis, click on the Patterns tab, and on the bottom
right, select Low for Axis Tick Labels.

> Are there any special things I can do to trick excel into making the
> graph the way I want it to look?

Trick? No, my friend, these are "workarounds"! I hope these help, and I
have a zillion more on my web site.

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


Btibert wrote:
> Hello everyone,
>
> Here is a problem that has been puzzling me. I am looking to create a
> column or bar chart that moves in two directions.
>
> Essentially, I want to include 16 variables on my chart, 1 bar for each
> variable, and the four categories that make up each variable stack on
> each other. I am looking to have the variable name up the left side of
> the graph (ordinarily the Y axis), and the % axis on the bottom of the
> graph (x-axis.) For simplicity sake, lets call the categories of each
> variable C1-C4. Using 0% as the origin, I want the categories 1 and 2
> to be to the left of the line (stacked on each other) and categories 3
> and 4 to be to the right of the line, stacked on each other as well.
> The sum of all of the absolute %'s will equal 100 for the variable.
>
> There are a few things I am hoping that you can help me with. First, I
> do not want to show the axis as ranging from -100% to +100%. Id prefer
> it to look like 100% counting down to 0% and then back up to 100%.
> Second, I want the label of each variable to be outside of the graph,
> or at least not on the "zero axis", rather I want to position each
> label so it can be clearly read.
>
> Are there any special things I can do to trick excel into making the
> graph the way I want it to look?
>
>

[Btibert]

Thank you very much. The help you provided was more than I could have asked for. I knew Excel was powerful, I just didnt realize it could do what I needed everythings.

I