Stacked Chart

[jweisman]

I have created a stacked chart in Excel for the following type of data:

Project ProjectManager1 ProjectManager2 Project Manager 3
Project 1 Funds Funds Funds
Project 2 Funds Funds Funds
Project 3 Funds Funds Funds

x-axis has the project managers. The y-axis has dollars. For each project manager, you can see the stacks of funds for each project.

I would like to add another dimension. For each project, there can be multiple sources of Funds. For example, Project Manager 1 can have Funds from Pot1 and Pot2 for Project1. I would like to show this on the stack. Possibly have Pot1 hatched. I want the to keep the same color for both sources of funds, but differentiate them somehow. Is there a way to do this in excel?

Thanks,
Jeff

 

[SkipVought]

Hi,

FIRST, you must change the form of you data source table
[tt]
Proj Mgr Fund Amt
Project 1 ProjectManager1 Pot1 100
Project 1 ProjectManager2 Pot1 200
Project 1 ProjectManager3 Pot1 300
Project 2 ProjectManager1 Pot1 200
Project 2 ProjectManager2 Pot1 300
Project 2 ProjectManager3 Pot1 400
Project 3 ProjectManager1 Pot1 300
Project 3 ProjectManager2 Pot1 400
Project 3 ProjectManager3 Pot1 500
Project 1 ProjectManager1 Pot2 100
Project 1 ProjectManager2 Pot2 200
Project 1 ProjectManager3 Pot2 300
Project 2 ProjectManager1 Pot2 200
Project 2 ProjectManager2 Pot2 300
Project 2 ProjectManager3 Pot2 400
Project 3 ProjectManager1 Pot2 300
Project 3 ProjectManager2 Pot2 400
Project 3 ProjectManager3 Pot2 500
[/tt]
Then use PivotTable Chart, drag Pron & Mrg into the ROW area, Fund into the COLUMN area and Amt into the DATA area and VOLA!

BTW, the PivotTable Wizard can ALSO help you reformat your table quite easily. Let me know if you want to go this route.

;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jweisman]

Thanks Skip.
The pivot table is almost what I want. I made a stacked graph with it. For the actual data, there is many more projects, project managers, and fund types than 3. Is there a way that I can stack the data with one color for each project and one hatch for each fund type; instead of a color for each combination. For example, Pot1 will always show as diagonal hatching even though it is for a different project. So for Project1-Pot1 the stack is green with diagonal hatching, Project2-Pot1 is blue with diagonal hatching, etc. Pot 2 will have a different hatching style.

Jeff

 

[SkipVought]

Jeff,

Do not believe that it can be done without VBA code.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jweisman]

I am pretty good at VBA. Would I have to completely code the chart? Do you know how I would go about setting it up?

Jeff

 

[SkipVought]

No. Let the PivotChart Wizard set up ithe chart. Code a procedure to format the bars. Run the code in...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

of the PivotTable SHEET (not the PivotChart sheet)

Evaluate the PivotTable to determine which PivotChart bars to format how.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

Here's a sample of what can be done. This is coded in the Chart Sheet Object Code Window.

Sub FormatBars()
   For Each ser In ActiveChart.SeriesCollection
      With ser
         Select Case .Name
            Case "Pot1"
               .Interior.Pattern = xlPatternHorizontal
            Case "Pot2"
               .Interior.Pattern = xlPatternVertical
         End Select
         i = 1
         For Each dp In ser.Points
            With dp
               Select Case i
                  Case 1 To 3: .Interior.Color = vbBlue
                  Case 4 To 6: .Interior.Color = vbGreen
                  Case 7 To 9: .Interior.Color = vbRed
               End Select
            End With
            i = i + 1
         Next
      End With
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jweisman]

Thanks.

I will work on modifying that code for my chart.