Re: Decreasing white space around graphs in Excel?

[Dave R.]

Try selecting (clicking) the outside of the pie chart itself (plot area it
may be called), and not the square border that surrounds it. that will allow
you to expand the size of the pie without expanding the border box around
it.

"guenthj" <gue...@discussions.microsoft.com> wrote in message
news:244FAE15-738A-4EB6...@microsoft.com...
> I want to increase the size of a pie chart within its window in Excel, but
I
> don't want to increase the amount of white space surrounding it.
>
> I want to paste the chart into a small area in Publisher, but when I do I
> get a lot of wasted space around the chart.

[Jon Peltier]

Actually, to select the plot area of a pie chart, you have to click in
the corners between the circle and the square it fits within. With this
square highlighted, drag the corners outward to increase its size.

All about pie charts:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=390

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

[jmdaniel]

Kind of a puzzler, but when I hover the cursor over one of the corners of the
box, it changes to a two headed arrow, but clicking and dragging does
nothing. Can't enlarge the overall size oif the pie. Could this be locked,
somehow?

[Andy Pope]

Hi,

If the sheet has been protected then you could see this effect.
Have a look under menus Tools > Protection

Cheers
Andy

jmdaniel wrote:

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[jmdaniel]

Nope, the sheet isn't protected....

[charlotte]

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.

[charlotte]

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]

"charlotte" <char...@discussions.microsoft.com> wrote in message
news:21C0AAE3-795A-4319...@microsoft.com...

>
> 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]

"Jon Peltier" <jonREMO...@peltierCAPStech.com> wrote in message
news:uD%23s7Oyx...@TK2MSFTNGP14.phx.gbl...

>
> 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]

"Jon Peltier" <jonREMO...@peltierCAPStech.com> wrote in message

news:uGpyJ45x...@TK2MSFTNGP09.phx.gbl...

>
> 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]

"Jon Peltier" <jonREMO...@peltierCAPStech.com> wrote in message

news:ep20dI7x...@TK2MSFTNGP15.phx.gbl...

>
> 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 (news:OjyOJu6x...@TK2MSFTNGP12.phx.gbl...) 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]

"Jon Peltier" <jonREMO...@peltierCAPStech.com> wrote in message

news:OhISMXCy...@tk2msftngp13.phx.gbl...

>
> 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.