Free Microsoft Excel 2013 Quick Reference

Excel Chart Plot Area Auto Size

In Excel 2003 I'm creating a very simple area chart with a legend placed to
the right of the plot area. If I move the legend to say bottom of the plot
area (right mouse click and "Format Legend/Placement/Bottom") the plot area
itself automatically resizes to maximise the visibility. This is good news
for me.

The problem occurs when the plot area has been manually resized (using the
mouse). When the legend is now moved it only resizes the plot area if it
overlaps with the legend. This is bad news for me because it doesn't maximise
the possible size for the plot area.

If a plot area is manually altered it somehow turns off the ability for the
plot area to be auto sized (as if it was a new chart). How do I turn this
back on again ?

Any help would be appreciated, Jack.


Post your answer or comment

comments powered by Disqus
Hello,

I am automatically creating a number of charts, using a Chart Template, with VB.

The series name is entered after the Chart Template is applied. However, this removes the legend/plot area auto-sizing. If I re-apply the Chart Template, then the legend auto-sizing is restored, but the series names are lost. If I re-apply the series names, then the legend auto-sizing is lost again.

Any clues? Is there a command which restores legend resizing?

Thank you very much.

am I missing some part of excel as I cannot make a chart ( even the simple
ones) the points do not show in the plot area, ( or maybe I am doing some
thing really stupid...it sure feels that way at this point!

Hi all! I need to know chart plot area dimensions (in points), but without a data labels. If i hide them then they dont show but Excel still counts the are width and height including them. Is there the way to neutralize them? Here is the i use.

Sub Macro6()

ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.Axes(xlValue).Select
        Selection.Delete
    ActiveChart.Axes(xlCategory).Select
        Selection.Delete
    ActiveChart.PlotArea.Select
       Range("A1").Value = Selection.InsideWidth
       Range("B1").Value = Selection.InsideHeight
        
End Sub
thanks and regards

I am unable to place a textbox into an Excel chart plot area and have it
remain visible when the textbox in un-selected. Cannot view, does not show
up in Print preview unless I click outside of the chart in some cell.
Have tried "draw/order/bring to front"
Any good ideas?

I have two dynamic lined charts whose chart areas are aligned; however,
because the y-axis values change dramatically when graphing different
ranges, the chart plot areas misalign (Y-axis shift to the right to
accomodate larger y-values displayed next to vertical axis). This
annoying behavior essentially puts the the two charted lines in the two
charts out of alignment. Locking the chart areas by code does not help,
since the plot area is the culprit!
Any help on how to align the actual plot areas by VBA code? (to be
clear, the aligned areas must not include the y-axis values which can
alternate between 1-100 range to 1-1000,000 range)
Thank you in advanice for your valuable assistance.

Alseikhan

--
Alseikhan
------------------------------------------------------------------------
Alseikhan's Profile: http://www.excelforum.com/member.php...o&userid=32364
View this thread: http://www.excelforum.com/showthread...hreadid=524606

I have two dynamic lined charts whose chart areas are aligned; however, because the y-axis values change dramatically when graphing different ranges, the chart plot areas misalign (Y-axis shift to the right to accomodate larger y-values displayed next to vertical axis). This annoying behavior essentially puts the the two charted lines in the two charts out of alignment. Locking the chart areas by code does not help, since the plot area is the culprit!
Any help on how to align the actual plot areas by VBA code? (to be clear, the aligned areas must not include the y-axis values which can alternate between 1-100 range to 1-1000,000 range)
Thank you in advanice for your valuable assistance.

Alseikhan

I am unable to place a textbox into an Excel chart plot area and have it
remain visible when the textbox in un-selected. Cannot view, does not show
up in Print preview unless I click outside of the chart in some cell.
Have tried "draw/order/bring to front"
Any good ideas?

Good afternoon. I've encountered an infuriating problem in Excel charts, and
I haven't found a solution in Excel or on Google. Has anyone else seen this?
I'm using Excel 2K3, Windows XP, fully patched up. It's happend to other
users on other computers, too.

On some charts, the plot area for a chart can shrink, or occasionally grow,
on it's own. I'll just click on the chart and the plot will shrink slightly,
and possibly move slightly too. This happens each time I click on it, and
sometimes when I open the workbook, or work on other charts. There's
typically some visual corruption, too, the plot area box will remain visible,
slightly. Once a chart gets "infected" it will not stop, and I have to
either periodically stretch out the area again, or delete it and recreate it.
This can happen on pie charts and line charts, that I've noticed. It seems
to happen in larger Excel files, 5+ MB, and mostly only when charts' areas
overlap, but that's not all there is to it: sometimes a lone chart will be
affected, and sometimes overlapping charts are fine.
I'm quite sure I'm not accidentally dragging the plot area, or a resize
handle. It'll happen when I'm moving the chart, or clicking on the labels.
It's not from deleting or resizing rows or columns, all of my charts are
move-but-don't-resize and I'm not working with rows or cells at all on these
worksheets.

This is especially maddening when I'm linking Excel charts into PowerPoint.
I may paste in four equal-sized pie charts, and after some twiddling I come
back and one pie has shrunk to a pinpoint. On other slides I have line
charts neatly lined up with a PPT rectangle object to highlight a section,
and every time I open the file I need to adjust the chart's plot area again.
I don't remember seeing this before I started working with PowerPoint, so I
suppose there could be something about the OLE linking process that causes
Excel to handle charts differently, but if so it only affects a few.

This seems like a cumulative rounding error, maybe. Has anyone seen this?
Any ideas how to stop it? I'll give you my first born son!

I'm using Excel 2002 and I increase my plot area in my pie chart to reduce the amount of white space in my chart area. When I increase or decrease the chart area it increases and decreases the pie chart too. I just want to increase the plot area. I click on the grey outline on the plot area and it won't allow me to expand the area. Can I change the size on this or am I stuck w/lots o' white?

Thanks,

Jami

I want to print four pages, which include three separate charts, from the
same worksheet. The worksheet is setup to repeat the three top rows, which is
how I wnat the charts to print with the workheet title above the chart title.
The first sheet is data with three charts following. All four sections, data
and charts, sit between natural page breaks. When I print from the worksheet,
the chart plot areas shrink and shift into the chart title area. Printing the
charts seperately works fine. I have futzed with the margins on both the
worksheet and the individual charts without much luck. I have also changed
chart sizing whit no improvement. To make this more interesting, sometimes
the charts look fine when printing from the worksheet; other times they
shrink and shift. I am using Excel 2003. Thanks

I want to add some vertical lines to an Excel chart, so I started off by finding the dimensions of the chart and plot area, using the following:
    ActiveChart.ChartArea.Select
    L = ActiveSheet.Shapes(MyChart).Left
    T = ActiveSheet.Shapes(MyChart).Top
    W = ActiveSheet.Shapes(MyChart).Width
    H = ActiveSheet.Shapes(MyChart).Height
    ActiveChart.PlotArea.Select
    PL = ActiveChart.PlotArea.Left
    PT = ActiveChart.PlotArea.Top
    PW = ActiveChart.PlotArea.Width
    PH = ActiveChart.PlotArea.Height
My problem is with the PlotArea.Left -- If I relocate the plot area to the far left, I get a value of 0, which is the left edge of the Y-axis scale. The actual plot area is further to the right.

How do I find the left edge of the actual plot area?

I need to distinguish two diffrent areas in the plot area of an excel chart.
Is it possible to colour the plot area with two different colours for that
purpose ?
Thanks

I am trying to copy a cell range and apply it to a chart fill area by the means of formatting the "plot area" - "fill" - "picture or texture fill" and using the "Insert from: clipboard". I can't find the code to use to the insert a picture from the clipboard. Any help would be greatly appreciated.

Thanks, SJDTAD.

This seems so simple but i looked everywhere and i cannot find the vba script to add a 3 point solid black line border around the chart and plot area of my graphs. The record macro option does not record this. Thank you

Hi,

I am trying to create a new series in an Excel chart using code (it's
actually VB.Net but I think it's almost identical in VBA). After creating a
series using Series1 = Chart1.SeriesCollection.NewSeries, I am trying to set
the values property of the series by assigning an array (in VB.Net this is an
array of Doubles) e.g. Series1.Values = DataArray1. For charts containing
many data points (>30 depending on the size of the numbers), I get the error
message "Unable to set the values property of the series class". I understand
this is because there is a limit to the size of an array, or the values
definition string, that the Values property can accept. The data for the new
series is not contained in a range within a Worksheet.

Is there a workaround that doesn't involve writing the data to the worksheet
and setting the Values property to the range containing the data? If not, is
it possible to create an invisible range? Alternatively, is there a way of
dynamically adding datapoints to a series in a program loop so I can
gradually build up the array of data points without getting this error? Is
there any other way to create a chart series from an array of numbers without
hitting this size limit?

Thanks,

Pete

I have 2 charts that are created with a macro. But the defaulting colors are not very good. I have tried to change them using all the basic commands that I have found selecting the active chart, plot area and then color index but it doesn't work. I have my code below. The chart is on sheet 4 but the data is on sheet 1.

Any suggestions would be great. Thank you

Tmac

Dim mychtobj As ChartObject
   
    Set mychtobj = Wks4.ChartObjects.Add _
    (Left:=250, Width:=700, Top:=15, Height:=500)
   With mychtobj.Chart
        .ChartType = xlXYScatterLinesNoMarkers
             
        With .SeriesCollection.NewSeries
            .Values = Range(Cells(2, 31), Cells(increment, 31))
            .XValues = Range(Cells(2, 33), Cells(increment, 33))
            .Name = "Chromatogram"
        End With
                
    .Axes(xlCategory).MinimumScale = Wks2.Cells(32, 1)
    .Axes(xlCategory).MaximumScale = Wks2.Cells(33, 1)
    .Axes(xlCategory).MajorUnit = 0.2
    .Axes(xlCategory).MinorUnit = 0.1    
  End With


Is it possible to use the yx cordinates from mousedown to give the value of
any point clicked in an excel chart plot area. GetChartElement only
identifies existing items.

Any help would be appreciated (excel 2000)

Do you really need to use a 3D chart type? 3D charts are first of all
notorious for their distortion of the data, but also they are not as easy to
work with manually or in VBA.

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

"Michael Malinsky" > wrote in message
ups.com...
>I am trying to create a chart using VBA. I attempted to create a
> starting point by creating the chart while the macro recorded was on so
> I could duplicate the result and adjust the code for user input, etc.
> The problem I am having is that when I run the unrevised code created
> from the macro recorded, the sizing of the Chart Area and Plot Area are
> properly duplicated (or close enough) from what was recorded in the
> macro, but the 3D Bar Graph contained within the Plot Area does not
> resize itself as it did when I performed the resizing manually during
> the macro recording. Any help would be greatly appreciated.
>
> TIA
> Mike.
>

I am working on a large spreadsheet full of numerical data. It contains
several dozen bar and line graphs embedded across several worksheets. I have
a decent understanding of Excel and Excel graphs but do not use VBA.

The problem I have with Excel is that it sometimes – and unpredictably –
(vertically) shrinks the plot areas of the embedded graphs. This can happen
between opening the document and printing it. Other times, the plot area
would shrink if I click on the plot area with my mouse, and then click off on
to a cell in the same worksheet. The chart area remains the same size, but
the plot area gets smaller and smaller until I can barely see it. I doubt
that this is due to actions related to resizing columns because I have
checked the options under Format Chart Area:Properties:Move and Size with
Cells. I have also turned off the Format Axis:Font:Autoscale option.

Is there any way that I can make these Excel charts stay *completely
static*? I am tired of having to manually resize the plot area every time
this “bug” happens. I would like to avoid using VBA, but am willing to
decide otherwise if necessary. This problem has been plaguing me for weeks
so your help would be much appreciated.

Thanks!
Rudy

I have a VBA script that parses a bunch of data and creates a scatter plot chart for each subset/group of the data (22 of them). The charts are all created the same size and in the same worksheet. Once they are all created I find the maximum value and then loop thru each chart and set the y-axis to that maximum.

The problem is that every once in a while the data, lines, chart points, everything, is scrunched up above the plot area of one of the charts, and I can't do anything to the data (reformat, change max/min/auto/etc). It typically only happens to one of the 22 charts when it happens, but never the same chart. I know the code is working fine because it works for the other 21 charts.

The only thing I can do is recreate that specific chart when it happens. But that breaks the flow of the script as all the charts are then copied to powerpoint automatically.

Anyone seen anything like this?

Hello!
Sorry about the language!
When creating a chart (clustered column chart) using VBA, I have a problem with chart and plot area. Everytime there can be a different number of bars - from 10 to 1000. Chart and plot area have the same size, so the width of bars stretches. I would like to have fixed bar width, and that chart and plot area would have the size according to number of bars.
My idea was to count bars - number of them is stored in variable lrow_A. Then I was multiplying this value by fixed number, e.g. 20 and put this result as a width of plot area. Then I multiply lrow_A by 21 and it is the width of chart area.

This code works - sort of. Sometimes it gives strange dimensions of chart, sometimes it is ok. Could you suggest me please more stable solution?

Any help is much appreciated!
Thank you!

cn - Chart name


	VB:
	
 
Dim lrow_A As Long 
Sheet1.Select 
lrow_A = Cells(Rows.Count, 1).End(xlUp).Row 
 
Sheet3.Select 
ActiveSheet.ChartObjects(cn).Activate 
ActiveChart.PlotArea.Select 
Selection.Left = 42 
Selection.Top = 83 
Selection.Left = 330 
 
[B]Selection.Width = lrow_A * 20[/B] 
[B]    Selection.Height = 500[/B] 
 
ActiveChart.ChartArea.Select 
[B]ActiveSheet.Shapes(cn).Width = lrow_A * 21 
ActiveSheet.Shapes(cn).Height = 800[/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hiya all

Has anyone got a cure for this???

I have an xy scatter chart which has both primary and secondary axis. In the middle of the plot area i have an area (bounded by a data series line on all sides) that I want a different colour.

I have browsed the site and see a couple of options but they require the secondary axis to be an area chart. This causes my xy chart to fail as it needs both axis.

Once the chart is coloured it will be used as a printing template so no longer needs to function at all. The chart was designed in excel so that it could be dragged to the correct size whilst maintaining accurate calculated dimensions (So i tried exporting the chart to paint and colouring that way, this solved the colour issue but when I pasted back the chart the quality had diminished).

Is there a way to either
a. colour the chart but keep both xy axis ?
or
b. is there a better user friendly drawing software I can use? (A trial version would be fine as I only need to do this once).

Heres hoping!

Nobby

Hello,

I am trying to find a solution to the following problem:

(1) I have created a chart in Excel 2003 and formatted the way I would like
it to be (9 horizontal & 11 vertical gridlines, so I have a total of 10 x 8
square blocks). It looks exactly the way I need it to be.
(2) I need to create a report with (multiple) of these charts in Word 2003.
(3) My problem is that I need the spacing between these gridlines to be
EXACTLY 1 cm when printed and I have no idea how to do this.

Since there is no size entry for the plot area I resized it to be the same
size as the chart area. The chart area object can be set to a fixed size
(10cm by 8cm) but there are 3 problems (FYI: both plot and chart area are
set to border "none") :

a. Although there should be no borders at all, the plot area seems to have a
tiny blank border in the chart area, making the plot area not exactly 10cm
by 8cm. I would not mind having a bigger chart area to solve this problem,
but there seems to be no size entry for the border area so the only way I
could determine its size is by trial and error I suppose. I guess I could
even live with this tiny inaccuracy and leave it the way it is now.
b. When I import this object into Word 2003 (Office clipboard, Paste
special, Excel graph object, link) word seems to rescale the size to 10cm by
7,6cm. I have ABSOLUTELY no idea why Word is doing this. When you scale the
object in Excel, its size will also change in Word but there sizes will
never be exactly the same. I think this has something to do with the chart
area not having a border but I am not sure at all. I really need to fix this
problem.
c. I would really like Word to adopt the sizes as entered in Excel. I guess
I could enter the size in Word again as 10cm x 8cm but that is not exactly
productive.

Is there any Excel/Word expert that can shine his light on these issues ? I
have no idea what I am doing wrong or how I could solve my trouble. For one
single chart I would just change the Word size, but I am not looking forward
doing this (and make errors) on multiple of these...

At the moment I see no other solution than "trial and error". I can only
image what will happen when printing this from another Word version or onto
another printer.

Thanks in advance!

Filip

Sometimes I would like the plot area to have a 1 to 1 ratio, so each little
square of the gridlines are a square. ie, the y- axis could be 10, the x-
axis could be 10, so the plot area would be square, or the y- axis could go
from -10 to 10, with the same x- axis, so the plot area would have twice the
y- axis than the x- axis.

I think the way microsoft has this place worded is misleading, cause it says
"Post your suggestion." I don't mean to put anyone off.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting


No luck finding an answer? You could always try Google.