RE: Decreasing white space around graphs in Excel?

[Guest]

I too want to do this. When selecting just the plot area, it does not allow
"copy" as an option. I just want the pie chart and not the extra white
space. The chart area will enlarge/reduce everything. HELP. Protection is
off also.

 

[Guest]

Found it in another post so will share - Thanks to Jon Peltier who replied to
a post..

If you want to keep your chart as a chart, it's not so easy. First you need
to
format all the text elements in the chart, and uncheck the Autoscale box on
the Font tab. This prevents everything from changing in size. Then you have
to shrink the chart area (the larger outer box) and expand the plot area (the
inner box) in a
coordinated effort.

This works well. You have to do the chart area, then the plot area and play
with the positioning until they come close to each other. but id does
eliminate alot of the extra white space around the graphics. Thanks Jon

 

[Alan]

Found it in another post so will share - Thanks to Jon Peltier who
replied to a post..

If you want to keep your chart as a chart, it's not so easy. First
you need to
format all the text elements in the chart, and uncheck the Autoscale
box on the Font tab. This prevents everything from changing in size.
Then you have to shrink the chart area (the larger outer box) and
expand the plot area (the inner box) in a
coordinated effort.

This works well. You have to do the chart area, then the plot area
and play with the positioning until they come close to each other.
but id does eliminate alot of the extra white space around the
graphics. Thanks Jon

Hi Charlotte,

Do you mean that, in addition to deselecting the autoscale option, you
also have to set each and every property (font, size, position, .....
etc etc etc) for each chart?

If so, is that best done programmatically in order to ensure that
*every* property is manually set?

Thanks,

Alan.

 

[Jon Peltier]

Alan -

You can set the Autoscale to false with a simple macro, and set it as your default
with a registry tweak. Both of these are covered in this web page:

http://peltiertech.com/Excel/Charts/FixFonts.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Alan]

Alan -

You can set the Autoscale to false with a simple macro, and set it
as your default with a registry tweak. Both of these are covered in
this web page:

http://peltiertech.com/Excel/Charts/FixFonts.html

- Jon

Hi Jon,

Thank you for the link to your site.

Can I ask whether this will resolve the issue of charts changing
between a save and re-open despite nothing being done to them?

Thanks,

Alan.

 

[Jon Peltier]

Alan -

I've hardly ever had this problem happen to my charts, although it's a common
complaint. It might be that elements left in their default sizes are susceptible to
being changed when Excel calculates what the defaults should be. I'm a compulsive
tweaker, so no defaults are left uncovered. I also keep the zoom at 100%, otherwise
the chart may act up. (Copy an embedded chart with 100% zoom, change to 75%, and
paste the chart. It is 75% of the size of the original (i.e., 75% time 75%).

Bottom line: I don't think anyone really knows.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Alan]

Alan -

I've hardly ever had this problem happen to my charts, although it's
a common complaint. It might be that elements left in their default
sizes are susceptible to being changed when Excel calculates what
the defaults should be. I'm a compulsive tweaker, so no defaults are
left uncovered. I also keep the zoom at 100%, otherwise the chart
may act up. (Copy an embedded chart with 100% zoom, change to 75%,
and paste the chart. It is 75% of the size of the original (i.e.,
75% time 75%).

Bottom line: I don't think anyone really knows.

- Jon

Hi Jon,

Is it possible to write a VBA sub that would (recursively?) cycle
through every possible property of a chart and, where applicable,
every sub-property, and set it to something specific so that it no
longer contains that default size?

I had a think about it, but I am not sure that it is possible to do
this since there appears to be no object of type 'property'? Each
property is of a potentialy different type (Boolean, Int, Long,
String, etc...)


Sub PropChanger()

Dim Prop as Chart.Property (?)
Dim myCht as Chart
Dim Holder as Variant

For Each Prop in myChart

Holder = Prop

Prop = Holder

Next

End Sub


Thanks,

Alan.

 

[Jon Peltier]

I think you could get away with locking in the font size, and defining the plot area
dimensions. The plot area is good to fix anyway, because of the excessive white
space around it.

With ActiveChart.PlotArea
.Left = 10
.Top = 25
.Width = 215
.Height = 170
End With

Of course, you need to adjust these sizes for labels, chart area size, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Alan]

I think you could get away with locking in the font size, and
defining the plot area dimensions. The plot area is good to fix
anyway, because of the excessive white space around it.

With ActiveChart.PlotArea
.Left = 10
.Top = 25
.Width = 215
.Height = 170
End With

Of course, you need to adjust these sizes for labels, chart area
size, etc.

- Jon

Okay - thanks Jon, much appreciated.

Just out of interest, would it be conceptually possible to do what I
was trying before (or
is that just impossible?

Thanks again,

Alan.

 

[Jon Peltier]

Alan -

The problem with your hoped for approach is that the properties are not just
properties of the chart. The chart has elements, and each element has its
properties, and subelements have their properties, etc. You can't just find them
the way you can recursively read files from a directory tree, or if you can, I don't
know how.

The ones you really need to be worried about can easily enough be done individually
through code. You could also automate the process of applying a user defined chart
type, or copying a good chart and using paste special-formats on a bad chart, but
both of these replace existing chart and axis titles with whatever is in the copied
chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Alan]

Alan -

The problem with your hoped for approach is that the properties are
not just properties of the chart. The chart has elements, and each
element has its properties, and subelements have their properties,
etc. You can't just find them the way you can recursively read files
from a directory tree, or if you can, I don't know how.

The ones you really need to be worried about can easily enough be
done individually through code. You could also automate the process
of applying a user defined chart type, or copying a good chart and
using paste special-formats on a bad chart, but both of these replace
existing chart and axis titles with whatever is in the copied chart.

- Jon

I see - thanks for that, understanding a little more than is strictly
necessary is always a good thing I find!

Regards.

Alan.