Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a pivot table with two sets of data as follows:

           2011    2012
Slice A      45      20
Slice B      33      28
Slice C      22       2

I am trying to present two pie charts side-by-side, one with the 2011 data and one with the 2012 data. I want the relative size of each pie chart to reflect the totals, i.e. the pie chart with the 2011 data (totalling 100) should be twice the size of the pie chart with the 2012 data (totalling 50).

The 'pie of pie' chart type seems to be closest to what I am looking for but this breaks out data from one slice and presents it in a second diagram so it isn't appropriate here.

share|improve this question
    
You can use VBA to resize graph objects in Excel (it would be easy to make their size a function of the sum of the data). –  Breakthrough Aug 10 '11 at 10:46
    
Try out Andy Pope's pie resizer vba code, He provides a downloadable Excel file with pre-installed code and example data. –  chuff Jan 7 '13 at 18:32
add comment

4 Answers

I modified this bubble chart to do the same thing. It might give you some ideas.

http://www.andypope.info/charts/piedatamarkers.htm

share|improve this answer
add comment

If your pressed for time I'd look to Doughnut or Radar diagrams instead, however this link seems to ask the same question... http://www.andypope.info/charts/pies.htm - not mine just off a forum? All the best Dave

share|improve this answer
add comment

You can change the size of a pie by selecting the plot area and dragging a corner to suit:

SU321317 example

If 'by eye' is not exact enough then measure the diameter of one pie as below:

  1. Position one diameter of each pie on a column boundary (eg the left of 2011 is at F/G).
  2. Drag the G/H boundary to the right side of the pie (here 219 pixels) but return to original position.

Then calculate the required diameter of the other pie (here 155 pixels) and align the Plot Area boundary as required (with some trial and error).

Edited to add macro version:

Sub Macro1()
Range("C2:C4").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[1]*SUM(R2C[-1]:R4C[-1])/SUM(R2C[1]:R[2]C[1])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C4"), Type:=xlFillDefault
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Range("A1:C4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1 (2)'!$A$1:$C$4")
ActiveChart.ChartType = xlPie
Columns("A:C").Select
Selection.EntireColumn.Hidden = False
Range("A1:B4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1 (2)'!$A$1:$B$4")
ActiveChart.ChartType = xlPie
End Sub
share|improve this answer
    
Thank you for the effort in putting your answer together. I'm really looking for a programmatic solution. Seems like it can't be done. –  Andrew Doran Jan 7 '13 at 18:12
add comment

Please try the following macro:

It works nicely for me, sorry I don't have time to provide a fuller answer

Sub graph()
'
' Pie chart Resizing Macro by David Birch
' will resize and centre a chart relative to a maximum value entered below. This maximum MUST be changed per set of graphs
' USAGE :
' 1) simply set the "Max Tota Over All Graphs" variable
' 2) select a graph
' 3) run the macro
'
' Keyboard Shortcut: Ctrl+g
'

' CONFIG
    MaxTotalOverAllGraphs = 20000 ' THIS VALUE MUST BE CHANGED for each use of this macro (each set of charts)
    MaxChartDiameter = 200 ' This value may also be changed


' start of macro

    XPieChartMargin = (ActiveChart.ChartArea.Width - MaxChartDiameter) * 0.5
    YPieChartMargin = (ActiveChart.ChartArea.Height - MaxChartDiameter) * 0.5

    ThisGraphTotal = Application.WorksheetFunction.Sum(ActiveChart.SeriesCollection(1).Values)

 '   MsgBox ThisGraphTotal

    MaxChartArea = 3.14159 * (MaxChartDiameter / 2#) * (MaxChartDiameter / 2#)
    MaxChartAreaPerUnit = MaxChartArea / MaxTotalOverAllGraphs

    ThisGraphArea = ThisGraphTotal * MaxChartAreaPerUnit
    ThisGraphDiameter = 2# * ((ThisGraphArea / 3.14159) ^ (0.5))

'MsgBox ThisGraphDiameter

    ActiveChart.PlotArea.Width = ThisGraphDiameter
    ActiveChart.PlotArea.Height = ThisGraphDiameter
    ActiveChart.PlotArea.Left = XPieChartMargin + (0.5 * (MaxChartDiameter - ThisGraphDiameter))
    ActiveChart.PlotArea.Top = YPieChartMargin + (0.5 * (MaxChartDiameter - ThisGraphDiameter))


End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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