Macro to Resize Charts in a Workbook

These VBA macros make every chart on a sheet, or in the whole workbook, the same size, so dashboards and reports look neat. A third macro matches every chart to the size of the selected one.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Resize every chart on the sheet

Sub ResizeChartsSheet()
    Dim co As ChartObject
    For Each co In ActiveSheet.ChartObjects
        co.Width = 480
        co.Height = 288
    Next co
End Sub

Sizes are in points: 72 points is one inch, so 480 × 288 is about 17 × 10 cm.

Resize every chart in the workbook

Sub ResizeChartsWorkbook()
    Dim ws As Worksheet, co As ChartObject
    For Each ws In Worksheets
        For Each co In ws.ChartObjects
            co.Width = 480
            co.Height = 288
        Next co
    Next ws
End Sub

Match the selected chart's size

Click one chart that’s the size you want, then run:

Sub MatchSelectedChartSize()
    Dim co As ChartObject, w As Double, h As Double
    If ActiveChart Is Nothing Then Exit Sub
    w = ActiveChart.Parent.Width
    h = ActiveChart.Parent.Height
    For Each co In ActiveSheet.ChartObjects
        co.Width = w
        co.Height = h
    Next co
End Sub

Without a macro

  1. Click the first chart, then hold Ctrl and click the others.
  2. Go to Shape Format and type the Height and Width in the Size group.

Things to know

  • The macros change size only, not position.
  • Charts on separate chart sheets aren’t affected.
  • To line charts up, select them and use Shape Format → Align.

Related macros