Any Excel Chart Wizards here?

I’m trying to create a custom bar chart in Excel, but I’ve run into a problem, so I thought I’d ask the Teeming Millions for advice.

Here’s what I’m trying to do. I’ve got sets of data with 5 values (Cell B1, C1, D1, E1, and F1). I want to set up a bar chart showing each of those values, to compare how tall each bar is. That’s not a problem - figured out how to do that.

I also want each of those bars to have a different colour, to help illustrate the differences at a glance. No problem - figured that out.

Next, I want to create an additional value: a bar that is the sum of all the other values. No problem doing that on the spreadsheet: A1 = B1 + C1 + D1 etc. And I want that A1 value to be an additional bar on the chart. Again, no probs.

Here’s what I’m stumped on. I want the bar for A1 to be divided up, showing the contribution each of the other 5 values made to it, by using the same colours I assigned to the bars for B1, C1, etc. So the bar for A1 will have 5 different colours. Essentially, I want to stack B1, C1, D1 etc all on one bar, A1.

Is this possible?

One thing you can try doing is having a two dimensional array. It will look like the following:
1 2 3 4
A 4 0 0 0
B 0 2 0 0
C 0 0 4 0
D 0 0 0 6
E 4 2 4 6

(in other words, the values are in A1, B2, C3, D4, etc. and then the last row has all the values).

You can create a stacked bar graph that will do what you want. The disadvantage of this is it will skew your individual bars so it won’t be easy to tell the difference between each bar. A way around it is to create two seperate graphs.

Repeat the same sequence of values horizontally and vertically and create a chart for the whole rectangular range. I am emailing you an example because it is easier to understand.

Let’s see:


1
3
5
2
6
1  3  5  2  6 

The first 5 bars only have one section as the rest of the cells are empty. The last bar has five sections, each one the same individual value as the others. I hope this is a bit more clear.

sailor, the one disadvantage of putting the values in the first column is that the color of the bars doesn’t change. If there’s one value in each column, the bar will change color automatically, and correspond with the color in the last bar.

You should be able to change the color of bars by right-clicking on a bar, or possibly the X-axis line, and looking for Edit or ???

-Tcat

Before getting into the mechanics of whether you can do this in Excel, could you provide some additional information on what information you’re trying to convey to the reader? In other words:

[ul]
[li]Why do you want to have the stacked bar on the same set of axes as the other bars?[/li][li]Why do you want the B1, C1, etc. bars to be different colors?[/li][/ul]