Prev: Get 2007 Chart to fill page
Next: Excel 2007 - problems with macro to change series formula
From: MKArchibald on 18 May 2010 12:31
I've got a really frustrating issue with Excel charts. I am using Excel 2003
SP3 and XP.

I have a large workbook with many tabs. One of the tabs has about a dozen
bar charts. Every time I copy these charts into Word, and then go back to
Excel, the plot area has randomly moved. I don't do ANYTHING in this tab
except copy and paste those graphs. There is no shifting of column widths
etc.

Specifically, the plot area seems to shrink. I make a quarterly report with
these graphs and each quarter the plot area has shrunk until it gets so small
and scrunched into the middle of the graph that I have ot go back and
manually stretch the graphs out again. The problem is twofold:

1. Why does this random movement of the plot area happen? I'll not also that
the legend also seems to move.

2. My workaround of going back each quarter and manually stretching the plot
area back to fill the chart is also frustrating, time consuming, and
impossible to do perfectly the same in each of the 12 graphs because I'm
having to do it by eye. Why does Excel not have rulers and/or measured plot
area options? It seems you can only change sizes by eye. Therefore my report
looks unprofessional and messy because the graphs aren't all bang on the same.

Any help would be greatly appreciated!
From: ck13 on 19 May 2010 22:25
Hi,

Not sure if this will help you. Jon Peltier has a site that talks about
arrange charts in worksheets. You might want to check it out. But you will
need to modified and fine-tune it to suit what you wants.

http://peltiertech.com/WordPress/arranging-charts-in-a-grid/

"MKArchibald" wrote:

> I've got a really frustrating issue with Excel charts. I am using Excel 2003
> SP3 and XP.
>
> I have a large workbook with many tabs. One of the tabs has about a dozen
> bar charts. Every time I copy these charts into Word, and then go back to
> Excel, the plot area has randomly moved. I don't do ANYTHING in this tab
> except copy and paste those graphs. There is no shifting of column widths
> etc.
>
> Specifically, the plot area seems to shrink. I make a quarterly report with
> these graphs and each quarter the plot area has shrunk until it gets so small
> and scrunched into the middle of the graph that I have ot go back and
> manually stretch the graphs out again. The problem is twofold:
>
> 1. Why does this random movement of the plot area happen? I'll not also that
> the legend also seems to move.
>
> 2. My workaround of going back each quarter and manually stretching the plot
> area back to fill the chart is also frustrating, time consuming, and
> impossible to do perfectly the same in each of the 12 graphs because I'm
> having to do it by eye. Why does Excel not have rulers and/or measured plot
> area options? It seems you can only change sizes by eye. Therefore my report
> looks unprofessional and messy because the graphs aren't all bang on the same.
>
> Any help would be greatly appreciated!
From: Ed Ferrero on 20 May 2010 03:30
Hi MKArchibald,

> 1. Why does this random movement of the plot area happen? I'll not also
> that
> the legend also seems to move.

I don't know why this happens. Suggestions are to install all updates, and
check the printer driver (change the default printer to something generic
and see if the behaviour still happens).

> 2. My workaround of going back each quarter and manually stretching the
> plot
> area back to fill the chart is also frustrating, time consuming, and
> impossible to do perfectly the same in each of the 12 graphs because I'm
> having to do it by eye.

You can stretch the plot area on one chart, then Copy and Paste Special
Formats on each of the other charts. That will make each chart the same.
Also make chart type etc the same.

Or, you could run something like this;

Sub SetChartAreas()
Dim oCht As ChartObject

For Each oCht In ActiveSheet.ChartObjects
oCht.Chart.PlotArea.Top = 10
oCht.Chart.PlotArea.Height = oCht.Height - 20
oCht.Chart.PlotArea.Left = 10
oCht.Chart.PlotArea.Width = oCht.Width - 20
Next
End Sub

 | 
Pages: 1
Prev: Get 2007 Chart to fill page
Next: Excel 2007 - problems with macro to change series formula