Multiple Chart on Single Page

  • Thread starter Thread starter BT
  • Start date Start date
B

BT

I'm having a lot of trouble arranging multiple charts on a page. Actually
I'm doing 10 charts that are all the same size on 2 pages with 6 on the
first page, then 4 on the last. I'm having a heck of time trying to get
this right. I select all 10 charts and stretch them this way and that and
adjust some row and column widths between the charts to make adjustments,
but I can't get it right. There has to be a better method that this trial
and error approach.

I've search the web, but almost everything I've found is for some fancy
charting program to do this. It must be doable within Excel. Can anyone
give me some pointers or, better yet, give me a link to some sort of
advanced Excel charting tutorial that could help.

many thanks, BDT
 
I have found two ways to align multiple charts on a worksheet. Method 1 is
to size and align floating charts with a VBA procedure.

The second way is to place my charts in single cells. the row height and
column width set the chart size and they are automatically aligned with row
and column boundaries.

If you want your charts to float, the following code will get you started.

Public Sub Size_align_charts()
Dim ChtWidth As Long, ChtHeight As Long
Dim TopPosition As Long, LeftPosition As Long
Dim chtobj As ChartObject

If ActiveChart Is Nothing Then Exit Sub
'Get size of active chart
ChtWidth = ActiveChart.Parent.Width
ChtHeight = ActiveChart.Parent.Height
TopPosition = ActiveChart.Parent.Top
LeftPosition = ActiveChart.Parent.Left
For Each chtobj In ActiveSheet.ChartObjects
chtobj.Width = ChtWidth
chtobj.Height = ChtHeight
chtobj.Top = TopPosition
chtobj.Left = LeftPosition
TopPosition = TopPosition + chtobj.Height
Next chtobj
End Sub

See my website for information on how to place charts in cells.

http://processtrends.com/TOC_charts.htm

...Kelly
 
Thanks, the Chart in a Cell looks like a good approach, but I already have a
workbook and I already have 10 charts ... is there an easy way to cut and
paste the charts into cells in my existing workbook and have the chart
borders locked to the cell borders.

cheers, BDT
 
BT - you can place your existing charts in cells in a few steps.

For preparation, you should do two things:
1. Make copy of workbook. Do all your work on copy, you don't want to
risk messing up your original wok
2. Turn off autoscale for each chart (right click chart, go to Font,
uncheck Autoscale)

To move charts to cells, you need to establish sizes for rows and columns
where you want to place charts. I suggest you pick an area outside range of
existing area being used. You want to go to right and down so that changes
in your row/cols won't affect existing charts.

Once you get cells to the approximate size you want, you can move charts to
target cells. The trick is to select the chart you want to move, hold down
the Alt key and place chart in cell. I align top left with Alt key down,
then adjust chart height and width to cell boundaries with Alt key down.

Once charts anchored in cells, you can delete extra cols and rows and put
final sizes to rows and cols.

...Kelly
 
In the middle of this page, you'll find links to a couple of downloads of
Excel workbooks. One is the Interactive Chart Resizer. It allows you to
select a chart, click a button, then select a range of cells, and the
program resizes the chart to cover the selected cells. It's a little quicker
than dragging the chart yourself while holding the Alt key.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______
 
Back
Top