Specifying Chart Sizes

Megan has a monthly report that she creates in Excel. She has most of the report automated, except for one annoying problem that she must handle manually. The report includes four pie charts used to illustrate some values from the report. Each pie chart comes out a bit different in size, and the manual task is that Megan needs to make them all the same size. She wants each of them to be 5 centimeters square and would love a way to remove the manual drudgery of formatting them each month.

The reason that each of the pie charts is a little bit different in size is because when you create a chart with the default settings, Excel decides it can adjust the chart size as it sees fit. This sizing can depend on several factors, such as available space, label sizes, number of data points, etc. One way to improve the chances that each chart will be the same size is to create your first chart and then use Ctrl+C and Ctrl+V to copy the chart the other three times. Each should be identical, and then you can adjust the data ranges reflected in the charts so that they display the desired ranges.

If it is not practical to copy and paste the charts (for instance, if the charts are created by macros), then you may be interested in just using a quick macro to adjust the size of all the charts in the worksheet. The following macro will step through each chart and adjust the Height and Width properties to 5 centimeters.

Sub AdjChartSizes()
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Chart.ChartArea.AutoScaleFont = False
        cht.Height = Application.CentimetersToPoints(5)
        cht.Width = Application.CentimetersToPoints(5)
    Next cht
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2429) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Specifying Chart Sizes.