Tjyylli

Is this a crack on the carbon frame?

How do I create uniquely male characters?

strToHex ( string to its hex representation as string)

How do we improve the relationship with a client software team that performs poorly and is becoming less collaborative?

Mathematical cryptic clues

How to write a macro that is braces sensitive?

In Japanese, what’s the difference between “Tonari ni” (となりに) and “Tsugi” (つぎ)? When would you use one over the other?

Python: next in for loop

What typically incentivizes a professor to change jobs to a lower ranking university?

Can divisibility rules for digits be generalized to sum of digits

What is the offset in a seaplane's hull?

The use of multiple foreign keys on same column in SQL Server

Mage Armor with Defense fighting style (for Adventurers League bladeslinger)

Have astronauts in space suits ever taken selfies? If so, how?

What's the output of a record cartridge playing an out-of-speed record

Font hinting is lost in Chrome-like browsers (for some languages )

Why dont electromagnetic waves interact with each other?

An academic/student plagiarism

Why are 150k or 200k jobs considered good when there are 300k+ births a month?

What would happen to a modern skyscraper if it rains micro blackholes?

Why do I get two different answers for this counting problem?

I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine

Can I make popcorn with any corn?

tikz: show 0 at the axis origin

Need help plotting complex chart in Excel

Excel x-axis scaleHow to plot 3D graphs in Excel from CSV data?How to plot complex data as a radar chart in MS Excel?How to do up a excel hair chart?How to make Excel chart intelligently select dataConnecting multiple series on Excel scatter plotPlotting daily values and weekly averages in the same chartexcel 2010 line chart issuePlotting time of day distribution in ExcelHow to plot event times in a LibreOffice-calc chart

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

0

I have a somewhat complex data set which I'm trying to plot in Excel. 
The goal is to have this:

There's a few more data points but if I can get help getting to this point I'm sure I can make it work.

For this arbitrary set of values:

            +-----+---------------------------+---------------------------+

            | day |        qty reports        |   qty ppl from each dpt   |

            +-----+------+------+------+------+------+------+------+------+

            |     | dpt1 | dpt2 | dpt3 | dpt4 | dpt1 | dpt2 | dpt3 | dpt4 |

            +-----+------+------+------+------+------+------+------+------+

            | n-1 |   1  |   4  |   2  |   3  |   5  |   4  |   3  |   1  |

            +-----+------+------+------+------+------+------+------+------+

            | n   |   4  |   2  |   3  |   1  |   4  |   5  |   3  |   2  |

            +-----+------+------+------+------+------+------+------+------+

how would I go about creating that plot?

microsoft-excel microsoft-excel-2010 charts

share|improve this question

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  looks like a stacked bar chart
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Super User.  It would appear that you have accidentally created two accounts.  This will interfere with commenting, editing your own posts, and accepting an answer.  You should use the contact form and select “I need to merge user profiles” to have your accounts merged.  In order to merge them, you will need to provide links to the two accounts.  For your information, these are superuser.com/users/999710/damien and superuser.com/users/1017530/damien.  You’ll then be able to comment on answers.  … … … … … … … P.S. Please register your merged account.
  
  – Scott
  yesterday
  
  
  

  
  
  
  

add a comment | 

0

I have a somewhat complex data set which I'm trying to plot in Excel. 
The goal is to have this:

There's a few more data points but if I can get help getting to this point I'm sure I can make it work.

For this arbitrary set of values:

            +-----+---------------------------+---------------------------+

            | day |        qty reports        |   qty ppl from each dpt   |

            +-----+------+------+------+------+------+------+------+------+

            |     | dpt1 | dpt2 | dpt3 | dpt4 | dpt1 | dpt2 | dpt3 | dpt4 |

            +-----+------+------+------+------+------+------+------+------+

            | n-1 |   1  |   4  |   2  |   3  |   5  |   4  |   3  |   1  |

            +-----+------+------+------+------+------+------+------+------+

            | n   |   4  |   2  |   3  |   1  |   4  |   5  |   3  |   2  |

            +-----+------+------+------+------+------+------+------+------+

how would I go about creating that plot?

microsoft-excel microsoft-excel-2010 charts

share|improve this question

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  looks like a stacked bar chart
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Super User.  It would appear that you have accidentally created two accounts.  This will interfere with commenting, editing your own posts, and accepting an answer.  You should use the contact form and select “I need to merge user profiles” to have your accounts merged.  In order to merge them, you will need to provide links to the two accounts.  For your information, these are superuser.com/users/999710/damien and superuser.com/users/1017530/damien.  You’ll then be able to comment on answers.  … … … … … … … P.S. Please register your merged account.
  
  – Scott
  yesterday
  
  
  

  
  
  
  

add a comment | 

I have a somewhat complex data set which I'm trying to plot in Excel. 
The goal is to have this:

There's a few more data points but if I can get help getting to this point I'm sure I can make it work.

For this arbitrary set of values:

            +-----+---------------------------+---------------------------+

            | day |        qty reports        |   qty ppl from each dpt   |

            +-----+------+------+------+------+------+------+------+------+

            |     | dpt1 | dpt2 | dpt3 | dpt4 | dpt1 | dpt2 | dpt3 | dpt4 |

            +-----+------+------+------+------+------+------+------+------+

            | n-1 |   1  |   4  |   2  |   3  |   5  |   4  |   3  |   1  |

            +-----+------+------+------+------+------+------+------+------+

            | n   |   4  |   2  |   3  |   1  |   4  |   5  |   3  |   2  |

            +-----+------+------+------+------+------+------+------+------+

how would I go about creating that plot?

microsoft-excel microsoft-excel-2010 charts

share|improve this question

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

            +-----+---------------------------+---------------------------+

            | day |        qty reports        |   qty ppl from each dpt   |

            +-----+------+------+------+------+------+------+------+------+

            |     | dpt1 | dpt2 | dpt3 | dpt4 | dpt1 | dpt2 | dpt3 | dpt4 |

            +-----+------+------+------+------+------+------+------+------+

            | n-1 |   1  |   4  |   2  |   3  |   5  |   4  |   3  |   1  |

            +-----+------+------+------+------+------+------+------+------+

            | n   |   4  |   2  |   3  |   1  |   4  |   5  |   3  |   2  |

            +-----+------+------+------+------+------+------+------+------+

share|improve this question

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

share|improve this question

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

edited yesterday

Scott

16.2k113990

edited yesterday

Scott

16.2k113990

asked yesterday

DamienDamien

11

asked yesterday

DamienDamien

11

2 Answers
2

active

oldest

votes

4

The EASY solution is to break your chart into two. One for the open reports and the other for the people involved.

To get them both into the same chart, I had to do some data rearranging

In order to get the dashed line in, it would take some manual drawing on the chart. The text for day n and day n-1 would need to be added by text boxes manually as well.

Note when you right click on the chart and you select "SELECT DATA..." in the middle of that window there is a swap rows and columns option. Make sure you use that so that you are getting the right values where you need them.

Breaking it out into two separate graph would fit more into Excel's wheelhouse. I am using excel 2013. New chart options may be available in new versions of Excel.

SIDENOTE: Currently playing with a COMBO graph. Basically you can pick what type of data each column is. Doing it this way you can add an X-Y scatter plot option and then you can add a column off the side for your vertical line. takes a bit of playing but you can get a vertical line.

EDIT (Jon Peltier): I hope Ed doesn't mind me chiming in, but I thought my response wasn't distinct enough to write my own answer. I replicated his approach and staggered the data, so the people and reports are in different columns, so they are plotted as different series of bars. Then I formatted the People series as lighter shades of the colors used for the corresponding Reports series. I then removed duplicate legend entries.

share|improve this answer

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's a great way to start, thanks! Do you know if it would be possible to add a secondary axis for People Involved? So that on the left axis you'd have "Open Reports" and on the right axis "People Involved"? I've tried adding a secondary axis, but I can only do it to the departments...
  
  – Damien
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have not been able to play with secondary axis
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the series goes horizontally. ie its all the same colour that makes a series, not the stack of the bar. As a result its highly doubtful that you can have different bars an different vertical axis.
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the scales are the same (or similar) then a secondary axis will only serve to confuse; even if they're not, it will confuse. What you might do, though, is stagger the data, so you have different series for Open Reports and People Involved, and you can format them in different shades of the same colors. I did this and added it to @ForwardEd's answer.
  
  – Jon Peltier
  yesterday
  

  
  
  
  

add a comment | 

1

By staggering the data as described in my tutorial Clustered and Stacked Column and Bar Charts, you can get a stacked-clustered column chart to display the data in a similar way, clustering columns rather than fiddling with a vertical line.

share|improve this answer

answered yesterday

Jon PeltierJon Peltier

2,9621420

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1421883%2fneed-help-plotting-complex-chart-in-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

The EASY solution is to break your chart into two. One for the open reports and the other for the people involved.

To get them both into the same chart, I had to do some data rearranging

In order to get the dashed line in, it would take some manual drawing on the chart. The text for day n and day n-1 would need to be added by text boxes manually as well.

Note when you right click on the chart and you select "SELECT DATA..." in the middle of that window there is a swap rows and columns option. Make sure you use that so that you are getting the right values where you need them.

Breaking it out into two separate graph would fit more into Excel's wheelhouse. I am using excel 2013. New chart options may be available in new versions of Excel.

SIDENOTE: Currently playing with a COMBO graph. Basically you can pick what type of data each column is. Doing it this way you can add an X-Y scatter plot option and then you can add a column off the side for your vertical line. takes a bit of playing but you can get a vertical line.

EDIT (Jon Peltier): I hope Ed doesn't mind me chiming in, but I thought my response wasn't distinct enough to write my own answer. I replicated his approach and staggered the data, so the people and reports are in different columns, so they are plotted as different series of bars. Then I formatted the People series as lighter shades of the colors used for the corresponding Reports series. I then removed duplicate legend entries.

share|improve this answer

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's a great way to start, thanks! Do you know if it would be possible to add a secondary axis for People Involved? So that on the left axis you'd have "Open Reports" and on the right axis "People Involved"? I've tried adding a secondary axis, but I can only do it to the departments...
  
  – Damien
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have not been able to play with secondary axis
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the series goes horizontally. ie its all the same colour that makes a series, not the stack of the bar. As a result its highly doubtful that you can have different bars an different vertical axis.
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the scales are the same (or similar) then a secondary axis will only serve to confuse; even if they're not, it will confuse. What you might do, though, is stagger the data, so you have different series for Open Reports and People Involved, and you can format them in different shades of the same colors. I did this and added it to @ForwardEd's answer.
  
  – Jon Peltier
  yesterday
  

  
  
  
  

add a comment | 

4

The EASY solution is to break your chart into two. One for the open reports and the other for the people involved.

To get them both into the same chart, I had to do some data rearranging

In order to get the dashed line in, it would take some manual drawing on the chart. The text for day n and day n-1 would need to be added by text boxes manually as well.

Note when you right click on the chart and you select "SELECT DATA..." in the middle of that window there is a swap rows and columns option. Make sure you use that so that you are getting the right values where you need them.

Breaking it out into two separate graph would fit more into Excel's wheelhouse. I am using excel 2013. New chart options may be available in new versions of Excel.

SIDENOTE: Currently playing with a COMBO graph. Basically you can pick what type of data each column is. Doing it this way you can add an X-Y scatter plot option and then you can add a column off the side for your vertical line. takes a bit of playing but you can get a vertical line.

EDIT (Jon Peltier): I hope Ed doesn't mind me chiming in, but I thought my response wasn't distinct enough to write my own answer. I replicated his approach and staggered the data, so the people and reports are in different columns, so they are plotted as different series of bars. Then I formatted the People series as lighter shades of the colors used for the corresponding Reports series. I then removed duplicate legend entries.

share|improve this answer

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's a great way to start, thanks! Do you know if it would be possible to add a secondary axis for People Involved? So that on the left axis you'd have "Open Reports" and on the right axis "People Involved"? I've tried adding a secondary axis, but I can only do it to the departments...
  
  – Damien
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have not been able to play with secondary axis
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the series goes horizontally. ie its all the same colour that makes a series, not the stack of the bar. As a result its highly doubtful that you can have different bars an different vertical axis.
  
  – Forward Ed
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the scales are the same (or similar) then a secondary axis will only serve to confuse; even if they're not, it will confuse. What you might do, though, is stagger the data, so you have different series for Open Reports and People Involved, and you can format them in different shades of the same colors. I did this and added it to @ForwardEd's answer.
  
  – Jon Peltier
  yesterday
  

  
  
  
  

add a comment | 

The EASY solution is to break your chart into two. One for the open reports and the other for the people involved.

To get them both into the same chart, I had to do some data rearranging

In order to get the dashed line in, it would take some manual drawing on the chart. The text for day n and day n-1 would need to be added by text boxes manually as well.

Note when you right click on the chart and you select "SELECT DATA..." in the middle of that window there is a swap rows and columns option. Make sure you use that so that you are getting the right values where you need them.

Breaking it out into two separate graph would fit more into Excel's wheelhouse. I am using excel 2013. New chart options may be available in new versions of Excel.

SIDENOTE: Currently playing with a COMBO graph. Basically you can pick what type of data each column is. Doing it this way you can add an X-Y scatter plot option and then you can add a column off the side for your vertical line. takes a bit of playing but you can get a vertical line.

EDIT (Jon Peltier): I hope Ed doesn't mind me chiming in, but I thought my response wasn't distinct enough to write my own answer. I replicated his approach and staggered the data, so the people and reports are in different columns, so they are plotted as different series of bars. Then I formatted the People series as lighter shades of the colors used for the corresponding Reports series. I then removed duplicate legend entries.

share|improve this answer

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

share|improve this answer

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

edited yesterday

Jon Peltier

2,9621420

edited yesterday

Jon Peltier

2,9621420

answered yesterday

Forward EdForward Ed

1,064215

answered yesterday

Forward EdForward Ed

1,064215

1

By staggering the data as described in my tutorial Clustered and Stacked Column and Bar Charts, you can get a stacked-clustered column chart to display the data in a similar way, clustering columns rather than fiddling with a vertical line.

share|improve this answer

answered yesterday

Jon PeltierJon Peltier

2,9621420

add a comment | 

1

By staggering the data as described in my tutorial Clustered and Stacked Column and Bar Charts, you can get a stacked-clustered column chart to display the data in a similar way, clustering columns rather than fiddling with a vertical line.

share|improve this answer

answered yesterday

Jon PeltierJon Peltier

2,9621420

add a comment | 

By staggering the data as described in my tutorial Clustered and Stacked Column and Bar Charts, you can get a stacked-clustered column chart to display the data in a similar way, clustering columns rather than fiddling with a vertical line.

share|improve this answer

answered yesterday

Jon PeltierJon Peltier

2,9621420

share|improve this answer

answered yesterday

Jon PeltierJon Peltier

2,9621420

answered yesterday

Jon PeltierJon Peltier

2,9621420

answered yesterday

Jon PeltierJon Peltier

2,9621420