CODEWITHSUNDEEP
excelchartsstacked
Darren Guy
Darren Guy

Reputation: 1153

Grouped Stacked Chart in Excel

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

Upvotes: 0

Views: 2043

Answers (1)

user3514930
user3514930

Reputation: 1717

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 ...)

Upvotes: 1

Related Questions