Five columns of data into a two bar chart

[Scott]

Hello,

I could really use some charting help. Here's my data and
what I'm trying to do. Cells A11 contain sales for
regions North,South,East,West and cell E1 is a total
budget. What I'd like to do is have a two bar chart. Bar
1 would be a stacked bar that would be the sum of A11
but each cell (or region N,S,E,W) would be a different
color. Bar 2 would be cell E1 for the total budget.

For example, my chart for this data would have bar 1 with
a total of 27 (5+10+8+4) but in 4 separate colors and
then bar 2 would merely be the total budget.

Cells:
A1: 5
B1: 10
C1: 8
D1: 4
E1: 40

Thanks in advance for your help.

-Scott

 

[Jon Peltier]

Scott:

Arrange the data like this:

5 10 8 4
27

Select the 2 row by 5 column range and make a stacked column chart, with
series in columns.

To get a nicer chart, add headers and a couple extra rows:

A B C D Sum
" " 0 0 0 0 0
Parts 5 10 8 4 0
Total 0 0 0 0 27
" " 0 0 0 0 0

A, B, C, and D are names for the individual parts of the total. The
cells with "" have a space character (an invisible category label); the
category axis labels that appear are Parts and Total. The top left cell
is really a blank cell.

Select the range and make the stacked column chart, with series in
columns. When the chart is done, double click one of the series, and on
the Options tab, change the Gap Width to zero. This will make the stack
and the single cell butt against each other. The zeros give you space on
either side of this cluster.

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

 

[Guest]

Dear Scott,

Your problem can easily be resolved by using an excel add on called CHARTrunner. CHARTrunner has this awesome Excel Add-in that allows you to create real time bar charts, real time in just 2 mouse clicks. You might find this to be a better solutions, and it's a pretty inexpensive solutions. I think if you check www.chartrunner.com there is a free trial version to test it out.

Thanks

Excel Stat's Junkie