If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to create a line chart with two X-axes in Excel | JaVaughn | General Discussion | 2 | June 29th, 2005 04:52 PM |
How to create combination stacked area and line chart? | BarbAug | Charts and Charting | 2 | May 19th, 2005 01:18 PM |
How to make a stacked bar chart using time? | Jon Peltier | Charts and Charting | 0 | February 3rd, 2005 05:56 AM |
Excel Chart problem need two different stacked columns in same chart | chefmom | Charts and Charting | 3 | August 25th, 2004 02:18 PM |
how to create a 2 y-axis bar chart in excel | reallyf | Charts and Charting | 1 | May 19th, 2004 05:38 PM |