One effective way to present charts is to array them on a worksheet. You can lay them out manually, and even line them up nicely: by holding ALT while dragging and resizing charts, you constrain them to stick to the grid of cell boundaries.

However, if you have a lot of charts, this is tedious. I’ve arranged the charts in the view below using a VBA procedure.

The VBA procedure is not very complicated, and it lets you specify the height and width of each chart in cell units, as well as the top left cell under the first chart, and the number of rows and columns between adjacent charts.

Sub MakeGridOfCharts()
  ' Jon Peltier (3/19/2008)
  ' http://peltiertech.com/WordPress/

  ' chart size - adjust as desired
  Const nRowsTall As Long = 6
  Const nColsWide As Long = 3

  ' chart layout - adjust as desired
  Const nChartsPerRow As Long = 3
  Const nSkipRows As Long = 2
  Const nSkipCols As Long = 1
  Const nFirstRow As Long = 3
  Const nFirstCol As Long = 2

  Dim iChart As Long
  Dim chtob As ChartObject
  Dim dWidth As Double
  Dim dHeight As Double
  Dim rData As Range
  Dim dFirstChartTop As Double
  Dim dFirstChartLeft As Double
  Dim dRowsBetweenChart As Double
  Dim dColsBetweenChart As Double

  With Worksheets("Charts").Cells(1, 1)
    dWidth = nColsWide * .Width
    dHeight = nRowsTall * .Height
    dFirstChartLeft = (nFirstCol - 1) * .Width
    dFirstChartTop = (nFirstRow - 1) * .Height
    dRowsBetweenChart = nSkipRows * .Height
    dColsBetweenChart = nSkipCols * .Width
  End With

  For iChart = 1 To Worksheets("Charts").ChartObjects.Count

    Set chtob = Worksheets("Charts").ChartObjects(iChart)

    With chtob
      .Left = ((iChart - 1) Mod nChartsPerRow) * (dWidth + dColsBetweenChart) + dFirstChartLeft
      .Top = Int((iChart - 1) / nChartsPerRow) * (dHeight + dRowsBetweenChart) + dFirstChartTop
      .Width = dWidth
      .Height = dHeight
    End With

  Next

End Sub
 

Related Posts:

• Creating Charts in a Grid
• Build an Excel Add-In 2 – Enhanced Functionality
• Build an Excel Add-In 1 – Basic Routine
• Forms Controls and ActiveX Controls in Excel
• Build an Excel Add-In 3 – Auxiliary Modular Functions
• Bad Bar Chart Practices, or Send in the Clowns
• Variable Width Column Charts (Cascade Charts)
• Physics Lesson
• Quick VBA Routine: XY Chart with Axis Titles
• Regular Charts from Pivot Tables