• how do I create a stacked bar chart in excel

    From Charles Friedo@21:1/5 to PatK on Thu Jul 8 06:37:30 2021
    On Tuesday, July 5, 2005 at 6:33:03 PM UTC+5, PatK wrote:
    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



    Many data visualization tools are available to create charts including stacked bar chart.

    I would recommend you ChartExpo™ because it is both an Google Sheets and Excel add-on.
    You can use it with both or your favorite spreadsheet.

    ChartExpo™ has 50+ custom charts library; No coding skills required.

    Try it for Free:

    ChartExpo™ for Excel & Office 365: https://chartexpo.com/utmAction/MTErY29tbXVuaXR5K3hsK1NCK0dHKw==

    ChartExpo for Google Sheets: https://chartexpo.com/utmAction/MTErY29tbXVuaXR5K2dzK1NCK0dHKw==

    Thanks.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)