Arranging Charts in a Grid
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
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
Posted: Wednesday, March 19th, 2008 under Dashboards, Formatting, VBA.
Comments: 4
Comments
Comment from Tony
Time: Thursday, March 20, 2008, 8:24 pm
Nice post Jon! I use this setup, often called ’small multiples’ as coined by Edward Tufte, and this is definitely a helpful tip. I can confirm that doing this manually is tedious. Keep ‘em coming!
Comment from Jon Peltier
Time: Thursday, March 20, 2008, 9:48 pm
Tony -
Thanks. I have another one in the works, which starts with a table, and produces one chart per row of data. But the site went down, actually it was suspended for an as-yet unexplained reason, and I spent too much time running around reinstating the site.
Comment from derek
Time: Friday, March 21, 2008, 6:28 am
I knew about snapping them to the grid, and I like the sound of a VBA routine, but I’ve recently found that shift-clicking on one embedded chart, or clicking on more than one, treats them like pictures, with all the facilities that implies. You can tell this has happened when the drag handles are white, not black. This means I can select a number of charts and type the size in manually in Format Object, and they’ll all have the same size. Then I can use the Draw toolbar to align and distribute them, just like Powerpoint objects or Autoshapes.
(it also means they cut and paste into other applications as pictures and not embedded spreadsheets, which is a useful alternative to Copy Special)
An annoying feature of Excel is that the actual plot area is less easy to control than the whole graph area, so the graph areas align but the plot areas, the bit the readers actually see and expect to align, do not. That can sometimes be mitigated by careful choice of axis label formats and legends.
Comment from Jon Peltier
Time: Friday, March 21, 2008, 7:51 am
Derek - I use the shift-select trick a lot. You actually get a more accurate copy-paste than copying the chart area: when you copy the chart area and paste it, sometimes the pasted chart is a pixel wider and taller. Also, there’s a strange glitch when the window is at a lower resolution than 100%. Copy a chart at 50%, then paste it, and the pasted chart is half the size of the original. Shift-selecting before copying avoids this.
When I shift-select a chart, then copy it and paste it into PowerPoint, I am still getting an embedded workbook. I just tested it before shooting my mouth off….
And I have VBA solutions for the plot area size variations. In fact, here’s a semi-documented, semi-functional version:
http://peltiertech.com/Excel/Zips/AlignChartDimensions.zip
Write a comment