0

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

chart

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?

Improve this question
  • looks like a stacked bar chart – Forward Ed Apr 5 '19 at 13:32
  • 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 Apr 5 '19 at 16:55
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

Example

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.

example 2

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.

Staggered Data to Allow Different Formats

Improve this answer
  • 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 Apr 5 '19 at 14:13
  • I have not been able to play with secondary axis – Forward Ed Apr 5 '19 at 14:45
  • 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 Apr 5 '19 at 14:53
  • 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 Apr 5 '19 at 23:02
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.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.