Grouped Stacked Chart in Excel

Question

I have the following data in Excel.

Project | Month | Calls Created | Open Calls | Closed Calls

Requests | Dec 2012 | 15 | 11 | 7

Requests | Jan 2013 | 6 | 8 | 9

Requests | Feb 2013 | 6 | 5 | 2

dotcom | Dec 2012 | 1 | |

dotcom | Jan 2013 | | 1 |

dotcom | Feb 2013 | 1 | 2 | 1

The data is only a small subset. For the full dataset, there will be four projects, and the dates range from 2012 to June 2014

I am trying to create a chart, that for month and each project the data is bar stacked, while the overlying X axis is the Month

I have attached an mockup of the chart that I am looking to produce Chart Image Required

I can create stacked bar chart if I only want to use one of Calls Created, Open Calls, Closed Calls. However I need a chart that will combine all three

It's a little bit close method... If you agree to recombine data (with VBA), a possibility it's to use a normal Bar chart... — user3514930
I will try the VBA route. But my knowledge of VBA is limited. Can you link to any article / blog post that can give me a heads up on how to do this — Darren Guy
For future reference, I have written a tutorial showing how to generate a clustered-stacked column (or bar) chart in Excel: peltiertech.com/clustered-stacked-column-bar-charts — Jon Peltier

user3514930 user3514930 · Accepted Answer · 2014-06-19T14:32:16

I show you what I think. If are ok, it's simple to adapt...
Following the scheme:

enter image description here

you have something like you need, but use a simple Chart Bar. The trick is to convert the data in the correct way. To do that I have Used VBA because it's more flexible...
Using this code inside a module connected to a button (Update) ... :

Dim N0(1 To 100) As String
Dim N1(1 To 100) As String
Dim N2(1 To 100) As String
Dim N3(1 To 100) As String

Range("B14:H44").Clear

Range("B1").Select
e = 0
For i = 1 To 9999
    If ActiveCell.Offset(i, 0).Value = "" Then Exit For
    e = e + 1
    N0(e) = ActiveCell.Offset(i, 0).Value
    N1(e) = ActiveCell.Offset(i, 1).Value
    N2(e) = ActiveCell.Offset(i, 2).Value
    N3(e) = ActiveCell.Offset(i, 3).Value
Next

Range("B15").Select
For i = 1 To e
    If (i > 1) And (N0(i) = N0(1)) Then Exit For
    ActiveCell.Offset((i - 1) * 4, 0).Value = "["
    ActiveCell.Offset((i - 1) * 4 + 1, 0).Value = N0(i)
    ActiveCell.Offset((i - 1) * 4 + 2, 0).Value = "]"
    ActiveCell.Offset((i - 1) * 4 + 3, 0).Value = ""
Next
nRep = i - 1
Debug.Print nRep

nrow = 0
For i = 1 To e
    If (i > nRep) And (N0(i) = N0(1)) Then nrow = nrow + 1
    For k = 1 To 99
        If ActiveCell.Offset((k - 1) * 4 + 1, 0).Value = "" Then Exit For
        If ActiveCell.Offset((k - 1) * 4 + 1, 0).Value = N0(i) Then
            ActiveCell.Offset((k - 1) * 4, 1 + nrow).Value = N1(i)
            ActiveCell.Offset((k - 1) * 4 + 1, 1 + nrow).Value = N2(i)
            ActiveCell.Offset((k - 1) * 4 + 2, 1 + nrow).Value = N3(i)
        End If
    Next
Next

The macro generate the NEW range Data for the Chart... The blank lines are used to visual divide the bars in base of the Mounth...
After, manually or if you prefer via VBA, you can adapt the chart (Decreasing the gap Width, add Labels ...)

Grouped Stacked Chart in Excel

1 Answers