Thread: how do I create a stacked bar chart in excel

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

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 wrote:
> 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

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

Pat

"Jon Peltier" wrote:

> 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 wrote:
> > 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
>