+ Reply to Thread
Results 1 to 8 of 8

Stacked Column Chart for Project Risk

  1. #1
    Registered User
    Join Date
    06-09-2011
    Location
    Leeds, UK
    MS-Off Ver
    MS Office 2016
    Posts
    77

    Red face Stacked Column Chart for Project Risk

    Hi All,
    I need some help to create a stacked column chart to show the risk count (high, medium and low) for projects grouped by programme. I've attached a sample sheet to explain what I am exactly trying to do. I have created pivot from master data to represent the count of high, medium and low risk. The first pivot under "Chart 1" tab is the count of risk grouped by programme only. It's simple to do and the chart is also coming properly. But I want to show the project numbers on each column in a stacked manner. When I included the project numbers into the pivot (second table) the chart got messed up. The chart that I want to create should look like the one on the "Chart 2" tab (it's just an image edited on MS paint). Is there any way that can create a chart like that image.
    I hope I've explained what I'm trying to achieve. If not, please let me know. I'll explain further.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stacked Column Chart for Project Risk

    If I understood what you are trying to do, I changed your pivot table so that project was the column label (in the pivot table) and legend fields (series) in the pivot chart and moved the impact to the row labels/axis fields.

    Is that what you are trying to do?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-09-2011
    Location
    Leeds, UK
    MS-Off Ver
    MS Office 2016
    Posts
    77

    Re: Stacked Column Chart for Project Risk

    Thanks for the reply MrShorty, but not quite follow you. Can you please update the pivot and upload. I'll then have a look.
    Basically all I want is a chart with programme name on Y axis and each programme will have 3 column representing count of high, mid and low risk. Additionally I want the project code stacked on each column of the chart. It should look like the image on Chart 2 tab which is the 3rd tab on the spreadsheet.
    Untitled.jpg

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stacked Column Chart for Project Risk

    I assume you are familiar with the pivot table field list and how to use to manipulate the fields in your pivot table: https://support.office.com/en-us/art...1-80160adfebec

    All I did was move project from the row labels field to the column labels field, and move impact from the column labels field to the row labels field.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-09-2011
    Location
    Leeds, UK
    MS-Off Ver
    MS Office 2016
    Posts
    77

    Re: Stacked Column Chart for Project Risk

    Many thanks for the reply. I have couple of more questions..
    1. On Pivot table the order of the risk category is appearing in alphabetical order i.e. High Low Medium. And the same is appearing on the graph. Because it's a pivot chart, I'm not able to change to order to High Medium Low. Is there an any way I can do it?

    2. When I changed the chart type to stacked column chart, is got filled with multiple colour (representing each project). Is it possible to put a red circle above each high column so that in can draw the attention of the user. Same way I want to put a amber and green circle above each Medium and low risk column. is it something that can me done?

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Stacked Column Chart for Project Risk

    Refer attach file.
    In this file creating chart both way. With Pivot & Without Pivot (I mean with formula countifs) Refer sheet "With_Pivot" & "Without_Pivot"
    I think this is required you.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stacked Column Chart for Project Risk

    Because it's a pivot chart, I'm not able to change to order to High Medium Low. Is there an any way I can do it?
    I do not use pivot tables in my own work, so I am not very skilled with some of the details. According to this help file, https://support.office.com/en-us/art...__toc273370210 you right click on the cell and use the Move command to change the order of pivot table elements.

    Is it possible to put a red circle above each high column so that in can draw the attention of the user. Same way I want to put a amber and green circle above each Medium and low risk column. is it something that can me done?
    Short but useless answer -- Yes, I'm sure it can be done.

    As noted, I don't use pivot tables/charts, so I am not very skilled with them. I know that there are some limitations to pivot charts (see discussion here: http://peltiertech.com/regular-chart...-pivot-tables/ ). My first thought -- if a pivot chart will allow -- would be to add 3 additional "dummy" series as a line chart series (formatted as marker/circle without line). Something similar to this "clustered column plus line/scatter" chart tutorial: http://peltiertech.com/Excel/Charts/...olAndLine.html The only question would be whether or not the pivot chart would combine nicely with the line/scatter data taken from outside of the pivot chart. If the pivot chart refuses to do this, then build the chart as a regular chart rather than a pivot chart.

    I could also see adding such "dots" as data labels https://support.office.com/en-us/art...2-f467c9f4eb2d I expect it will be easy enough, but it will be somewhat tedious, since you have 18 data series and you may need to edit each data label separately (since you will not want a visible data label for each point/stacked column).

  8. #8
    Registered User
    Join Date
    06-09-2011
    Location
    Leeds, UK
    MS-Off Ver
    MS Office 2016
    Posts
    77

    Re: Stacked Column Chart for Project Risk

    Thanks MrShorty. Changing the order is easy...I just overlooked the option already there. Other tutorials are also very helpful.

    Hi Avk,
    Thanks for your effort too. I already came up with that chart. Actually my query was how to populate the project codes on the column (the one on the Pic)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. 100% Stacked Column Chart within a 100% Stacked Bar Chart
    By bbarth in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-08-2016, 04:08 PM
  2. [SOLVED] Convert Chart Creation Macro to Stacked Column Chart
    By JBeaucaire in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-24-2014, 01:08 PM
  3. How to add target line to stacked column pivot chart chart
    By oleg mirzaev in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-04-2013, 07:46 AM
  4. Stacked bar chart presented as timeline for phases in project
    By saslotteroy in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-20-2013, 05:57 PM
  5. How to set the distance between chart series in stacked column chart
    By Jan Zitniak in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 04-23-2013, 03:58 AM
  6. [SOLVED] How can I add a line chart in a stacked column chart style?
    By Guilherme Loretti in forum Excel General
    Replies: 0
    Last Post: 08-02-2006, 08:50 PM
  7. [SOLVED] To create a stacked column chart and group the stacked bars togeth
    By Jacqueline in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1