how do I create a stacked bar chart in excel

[PatK]

This is probably going to seem like a really dumb question, but I have
searched everywhere in the help. I am trying to create a stacked bar chart.
I have three columns of data:
- Column 1 contains a region
- column 2 contains a sub region
- column 3 contains a cell with numbers of employees in the sub region

I want to create a stack bar chart that has these attributes:
- Each "bar" is a stack of sub-regions
- the "height" of the bar would show the number of employees in that region
(ie, the sum of the sub-regions).

Problem is, I don't even know how to start the selection. Every time I
select the data for the three columns, and, using the chart wizard, ask it to
create a stacked bar chart, I get a very flat (ie, no stacking of the sub
regions) chart.

Can someone tell me/give me hints

 

[Jon Peltier]

Pat -

Sounds like your data needs to be pivoted. You need each sub-region in a
different column.

Select your data (or a single cell in the data, and Excel will expand
the selection) and create a pivot table (Data Menu, Pivot Table Report).

Drag the region field icon/button to the row area, the sub region to the
column area, and the numbers to the data region. I imagine you have a
number of regions, and each has several subregions, so your table is
sparsely populated. No matter.

Here's my sample data:

Region Sub Number
New England MA 1500
New England RI 1250
New England CT 1700
New England NH 1100
New England VT 750
New England ME 900
Atlantic NY 2100
Atlantic NJ 1800
Atlantic PA 1650
Atlantic DE 1200
Atlantic MD 1300
Atlantic VA 1075
SouthEast NC 925
SouthEast SC 675
SouthEast GA 550
SouthEast FL 1300

Here's my pivot table:

Sum of Number Sub Region CT GA MA MD NC NH NJ
NY PA RI SC VA
Atlantic 1300 1800 2100 1650 1075
New England 1700 1500 1100 1250
SouthEast 550 925 675

My preference would be to create a regular chart, not a pivot chart,
because pivot charts are rather inflexible. The easiest thing to do then
is to copy the pivot table, and in a new sheet, use Paste Special -
Values to make a non-pivot table for your chart. Then delete the "Sum of
Number", "Sub", and "Region" labels from the cells, so that your sub
region labels (states in my example) are in the top row, the region
labels are in the first column. and the top left cell is blank:

CT GA MA MD NC NH NJ NY PA RI SC VA
Atlantic 1300 1800 2100 1650 1075
New England 1700 1500 1100 1250
SouthEast 550 925 675

Select this range, start the chart wizard. In step 1 choose the stacked
column type (I assume this is what you mean, but you could also use the
stacked bar type). In step 2 select the Series in Columns option. Press
Finish, and your chart is ready for formatting.

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

 

[PatK]

Hi, Jon...your solution worked just GREAT! Thanks so much for taking the
time to include such a comprehensive answer! Yer the best!

Pat