Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Strange Problem With Chart And Plot Visible Cells Option

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm deslecting the "Plot visible cells only" option on some charts in
order to display a chart while hiding the data. It works well except
for one case.

On one chart when I deselect this option, Excel adds some additional
series' to my chart and also changes the range of my X axis labels.
Does anyone have a clue about this? I'm totally stumped.

Note that on all my charts I've using the Offset and CountA functions
to create dynamic charts that automatically update the chart when new
data is added.

Thanks very much.


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Print all Embedded Charts in the Entire Workbook
- This macro will print every embedded chart in the active workbook. If you have an embedded chart in any worksheet within
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang

Similar Topics

I have 2 charts (one an XY Scatter and one a Line plot) and a worksheet in a workbook. Depending on the value of a cell in the workbook I want one or the other chart to be visible. I have the event trapping working correctly so I know when the appropriate cell value changes. I also have the following (simplified) code on the worksheet's code page that isn't behaving correctly:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code that makes sure the target is the right cell is delete for simplicity
Select Case Targe.Value
Case 1
Charts("B").Visible = False
Charts("A").Visible = True
Case 2
Charts("A").Visible = False
Charts("B").Visible = True
End Select
End If
End Sub

The code properly hides chart "B" (the Line plot) if both charts are visible and Target = 1. And it properly shows chart "A" (the XY Scatter plot) if "B" is showing and Target = 1. However, it won't show (visible = True) chart "B" or Hide "A". I know for a fact that it is stepping through the Case = 2 statements but appears to be ignoring the code.

Any suggestions for what might be happening?

I am using Excel 2011 (Office for Macs 2011). I have 86 bar charts (the data has to be represented as a bar chart), all of which have been log-transformed on the y-axis.
The x-axis of each of these charts is different - some range from 1 to 100, while others range from 1 to 1500. I am trying to make the maximum number on the x-axis the same on all of the charts (ie. I want the maximum number to be 1500 on all x-axes). This way comparison between the charts is easier. Is there a way to change the maximum value on the x-axis of a bar chart in excel? I dont mind going through each of the 86 charts one by one changing them manually if there is a way to do it.

When I try to plot the data as a different chart type (eg. scatter plot), I can change the max value of the x-axis. But as stated earlier, the data must be in a bar chart format. And in the bar chart format, there is no option to change the max value. Does anyone have any tricks they wish to share?
Any and all help is greatly appreciated.

Hi, wondering if anyone can help with Panel Charts with Different Scales...

I've organised my original data as:

Date, A, B, C, D, A-plot, B-plot, C-plot, D-plot

Calculated the min, max, major and rel.size

Calculated X-left, X-right, A-labels, B-labels, C-labels, D-labels and the same for axis.

The problem arises when I add the first new axis and changed it to a XY chart - when I try to move the series to a primary axis the chart shifts and I can only see my first date and it looks like all the data has shifted leftwards off the screen.

Hope this makes sense to someone!


I hope someone can help with this! I have already spent a few hours trying to figure it out!
The first attachment is how the chart looks in the workbook - this is how it should look, with the box around the dates all nicely aligned.

The second attachment is when I do a print preview - the little box around the dates jumps up a bit and is now out of alignment. Why is it doing this?

I have done these charts for years, and produce other workbooks just like this without this issue, so I have no idea why its suddenly not 'print previewing' exactly how it shows on the sheet.

I have tried various things, like putting objects forward, selecting different things under 'object positioning' - like "move and size with cells" (whatever that means!?)
Any ideas???

My only option for now is to position the box and labels far below where they should be, then it will print out looking ok. Not ideal really...

One thing I noticed about charts - there is a "chart area" (which incudes the chart plus all the chart headers etc) and a "plot area" (which is JUST the graph itself) - on this one, the plot area is not positioned right at the bottom of the chart area like it is on my other workbooks. In other words, when I select the chart area, the little black selection squares appear further down from the bottom of the plot area. And when I move the chart area up, the plot area moves with it.
Not sure if this is relevent or not....Is there a way of positioning the plot area in the chart area?

I'm still using excel 2003 for these for compatibility reasons...



Does anyone know how to create dynamic charts in excel such that as data is added to the sheet the chart automatically updates. I have 3 y-variables and 1 x-variable. My X, Y1, Y2, and Y3 are all in columns. Say X is in colA, Y1 is in colB, Y2 is in colC and Y3 is in colD.

How to make the chart dynamically update as data is added. The other twist is to only plot the "last" 12 entries of data. Is this possible?

I am using Office 2000, and I have a bunch of charts that are all related and
use the same data (ie I have a consolidated chart and then charts that are
the individual series from the consolidated chart). Is there a way that I
can have excel automatically produce these sub-charts from the series in the
main chart? It is time consuming to have to update each chart every month.

I am in the process of teaching myself Visual Basic and programing in

I have been modifying a program that pulls data from a spreadsheet and
creates a set of bubble charts. The original program was dealing with
4 categories of data. I had added an additional two.

This worked out fine for creating the categories and inputing data into
a spreadsheet via a form. But in the subroutine to create the chart I
hit a bug. I copied and modified some lines of code that I don't
entirely understand, and not surprizingly the subroutine will not run.

The portion of the code that has me stumped is below. The jj in the
comments is me just so I can keep track of the comments I was putting
in while I figured out the program

The code difines objects - shapes - rectangles with a number (11-14).
Then it applies methods to to them. I had added. I added 15 and 16.
The editor did not recognize these objects. Probably because they
either don't exist in the chart or they are not defined anywhere. My
problem is that I have looked everywhere trying to find these shapes
and have pretty much hit a wall. The documentation has not been

Can anyone point me in the right direction? Where should I be looking
for these rectangle shapes, and how can I define/create the additional
two I need to chart the two other data categories I have created?

Thanks in advance.



'To show the legend if the type of report is all
If strType = "All" Then
'this is for a full report "All"
Charts(conChartName).Shapes("Group 16").Visible = True 'not
sure what Group 16 is JJ

'jj makes shapes visible
Charts(conChartName).Shapes("Rectangle 11").Visible = True
Charts(conChartName).Shapes("Rectangle 12").Visible = True
Charts(conChartName).Shapes("Rectangle 13").Visible = True
Charts(conChartName).Shapes("Rectangle 14").Visible = True
'Charts(conChartName).Shapes("Rectangle 15").Visible = True 'JJ
added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = True
'JJ added for SVC

'jj these set a group of shapes on the chart and associate
them with the security imperatives defined in cells BF1-6 on the
projects sheet
'which are set from frmInvSetup It provides the caption on
the chart key
Charts(conChartName).Shapes("Rectangle 11").DrawingObject.Text
= Sheets("Projects").Range("BF1").Cells
Charts(conChartName).Shapes("Rectangle 12").DrawingObject.Text
= Sheets("Projects").Range("BF2").Cells
Charts(conChartName).Shapes("Rectangle 13").DrawingObject.Text
= Sheets("Projects").Range("BF3").Cells
Charts(conChartName).Shapes("Rectangle 14").DrawingObject.Text
= Sheets("Projects").Range("BF4").Cells
'Charts(conChartName).Shapes("Rectangle 15").DrawingObject.Text
= Sheets("Projects").Range("BF5").Cells 'JJ added for SecureValueChain
'Charts(conChartName).Shapes("Rectangle 16").DrawingObject.Text
= Sheets("Projects").Range("BF6").Cells 'JJ added for SVC
Charts(conChartName).Shapes("Group 16").Visible = False
Charts(conChartName).Shapes("Rectangle 11").Visible = False
Charts(conChartName).Shapes("Rectangle 12").Visible = False
Charts(conChartName).Shapes("Rectangle 13").Visible = False
Charts(conChartName).Shapes("Rectangle 14").Visible = False
'Charts(conChartName).Shapes("Rectangle 15").Visible = False
'JJ added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = False
'JJ added for SVC

End If

Hello, I am running Excel 2007 and trying to make a chart that will add a new series automatically as new data is entered. I haven't seen this particular problem discussed in the forums, but if it has please send me a link: this is driving me nuts!

The problem:
Here is a simplified version of my data

SeriesA SeriesB SeriesC SeriesD Mon 7 9 8
Tue 5 4 1
Wed 6 5 1
Thu 4 4 3
Fri 5 3 7

I have created a line chart to plot these four series (Mon-Fri as the horizontal category labels). As you can see, SeriesD does not have any data yet, and so I would like to exclude it from my chart (both as a line and as a Series in the legend). Over time I am going to be adding more and more series to this chart (SeriesE, SeriesF, etc.), and I would like Excel to update the chart as the data is added, but exclude these series entirely when they are blank.

This spreadsheet is going to be used by Excel novices who will not be able to run macros, reset filters, manually hide blank rows, or modify the chart in any way. They will only be able to add new Series data, so the fix needs to be entirely automated up front before I send it out.

What I've tried:
I first thought to use a dynamic name as the Chart Data Range, which works the first time, but does not update itself over time. Apparently, the Chart Data Range converts any defined names into their actual references when you close the Select Data menu. This would seem to be a perfect solution, if only the defined name would stay dynamic in the Select Data menu. I'm hoping that I've just overlooked an option here, and somebody knows how to do this.

Any help or suggestions or simple solidarity with this frustrating problem are very much appriciated! Thank you all in advance!


When i make a chart template and set it as a user-defined default
chart, and press F11 to apply it to a selection, the chart appears with
the plot area collapsed to a thin line along the axis.
When i re-start excel, this also applies to other charts, not only to
the one made by F11 - even in the preview of the chart wizard, charts
appear as a thin horizontal line.
The only way i can make charts then is to copy a previous one and
change its data - very time consuming!
Do you know what could be the problem, and how to fix it?


I was hoping someone may be able to help. I have set up a number of stacked bar charts, each chart has between 5 and 10 questions on it. I used the same method of setting up each chart. The problem is that the chart area is not the same on each chart with the axis a different width so putting the charts next to each other in a presentation for comparison looks stupid.

I have tried the usual trick of selecting the plot area and then trying to drag the borders but this doesn't work as with the plot area and the axis the full sheet is already taken up - any one any ideas how to change this.

Thanks in advance.

hello, i have been making a lot of automated charts lately but i am tasked with a more advanced one than usual.

for this new chart i am working with a large amount of data that is always changing but the way i have it set up in the spread sheet allows me to make a name space (ctrl+F3) of the entire amount of data and then type it into the "data range" in the "source data" option of the chart.
This works great until i close the window, because excel changes the name to the actual range in the spreadsheet. This causes problems when the data changes size.

i was using the define name and a few formulas to select my data and plugging it into the "series" tab in the "source data" option of the chart. in order to do this i have to add each source of data separately which was fine for the other charts but this one is too large.

thanks for any help!


I'm good with Excel and I'm testing office 2010 prior to converting everyone across from 2003.

Excel 2003 has an option under Tools/Options/Chart thats called "Chart Sizes With Window Frame". Using this on chart worksheets makes the chart fill the users screen. Handy as it looks right on everyones monitor and most of our more complex charts are far easier to look at on widescreen monitors.

Using Excel 2010 the charts ocupy a small chunk in the middle of the widescreen display, wasting the additional screen width. Does anyone know how to get the same effect as the old "Chart Sizes With Window" option?

I want to creat charts which would automatically update data as new information is added, i.e. if I am tracking cost of salaries/month then as soon as the next month's data is input into the data range, the graph will automatically include that, while excluding all empty cells (i.e. future months which have no data yet).

I got to the example of the Dynamic Column Chart by Jon Peltier - and to a very mediocre Excel end-user like myself...WOW, AWESOME!!! The problem is that the example that I got from Jon's site is only for a chart that has one data series. I need a similar practical example for a chart that will show multiple series of data, i.e. X-Axis = Months of the year, and data series are things like CTC, Nett Salaries, Tax Deductions, etc, etc. on a stacked graph.

I must be honest by stating that I dont quite understand the full science behind how the Dynamic Charting works - I get by by looking at what was done and applying the same strings of formulae/functions to my own charts.

Can anyone please assist with an example/tutorial on this issue? Thanks!

I am trying to create a dynamic xbar and moving range chart. Any ideas?
At the moment when I enter the data for the chart and create the charts it
works fine, but if I change any data a new chart has to be created for each
change. Is there a way to create the chart and have it automatically update
without having to recreate the charts? Thanks in advance for any help.

I have created a waterfall chart and would like to show a data-table with the chart. the normal Excel option to show a data-table is not good enough because it shows all the data the chart (and in a waterfall chart you have a few hidden series etc - and I want to show additional data in the data-table that is not in the chart).

my work-around is to put the chart as an object in a sheet and to constuct my own data-table underneath the chart using normal Excel cells. with a bit of work you can align the cells underneath perfectly with the columns above in the chart.

However, if the scale of the data in the chart changes the Plot Area shifts and spoils the alignment (the Y-axis values change and take up more or less space).

The only ways I have found to fix the Plot Area is:
- Insert a data-table into the chart (and then format it to have no lines and white text - it "disappears", but still takes up space and doesn't look very good).
- change the Y-axis scaling to not be "auto" - this is no good because the data could vary a lot.

neither of the above is really a workable solution.

Any ideas?

on a similar topic (which also might help solve the issue above):

Are there ways of fixing other objects to points in the Plot Area (or chart area)? an example of this would be to insert a text box into a chart that exactly fit across the width of the Plot Area - and didn't lose its position if the Plot Area moved because of a scale change

Any ideas would be much appreciated



This is my first post. I'm good with Excel and I'm testing office 2010 prior to converting everyone across from 2003.

Excel 2003 has an option under Tools/Options/Chart thats called "Chart Sizes With Window Frame". Using this on chart worksheets makes the chart fill the users screen. Handy as it looks right on everyones monitor and most of our more complex charts are far easier to look at on widescreen monitors.

Using Excel 2010 the charts ocupy a small chunk in the middle of the widescreen display, wasting the additional screen width. Does anyone know how to get the same effect as the old "Chart Sizes With Window" option?

I have a workbook with 2 sheets. I have a few charts on sheet 2. These charts can be enlarged by using option buttons just under each chart.
Sheet 1 contains the data the chart will use.
My problem is:
When I click the sheet 2 tab, it goes to sheet 2. On here if I click the
'larger' option button under the chart the chart gets enlarged. So far its OK.
Now if I click the sheet 1 tab to go back to sheet 1 and then select sheet 2 again, all the charts in sheet 2 stay enlarged. Therefore I put an option button under each chart called 'all charts' which will display all the charts on sheet 2 at normal size.
This is somewhat clumsy, and not too elegant...
I wonder if the sheet 2 tab can be coded so that when it is clicked it will show the charts in normal size, not enlarged.
Can anybody please help ?
Thanks in advance,

Hi all,

I have a question from my teacher. he has given me a chart picture with some data and he wants me to create a chart like it. The chart is really strange! I think it can be created with XY-Scatter charts, but is there any other option?

This si the chart:

hi everyone, i had posted a similar question a month or so ago, but it was suggested that i record a macro to show what i am looking for, so apologies if this is deemed a repost.

Anyway, attached is an excel worksheet, the "raw data" tab has our information for a variety of indicators.
Is there a way to go through the value in column A and when it says "TRUE" to plot the chart in the range C:O
for example: A3 is TRUE, so C2:O3 would be created into a chart. where row 2 contains the values for the x-axis.
Each would be a line chart with C2 being the chart title for example.
Where column A has a FALSE or is blank the data would be ignored.
Finish with a msgbox saying something like "charts done".

See macro1 which is my attempt at charting 3 of the indicators and placing into the chart sheet. Note i have only charted the first 3 where the TRUE / FALSE flag in column A was TRUE.

What i would really like to do is to go through the value in column A in each row and when it says TRUE plot the chart else ignore and go to the next row.

let me know if anything isnt clear
thanks for all your help as always

Hi guys,

I have a question regarding dynamic charts.

I have a XY (scatter) type of Chart and I want it dynamic.

Problem is I have a lot of series of datas and it varies.

If you would go to the source data then the series tab

there is this option to add or remove series.

Now I need to automatically do this in a sense that everytime I add another series in my sheet, the chart will automatically add the series in the chart.

I hope there is code that does this.

Thanks and more power to this site!


I have noticed that the y axis on some of my charts is plotted on the right, which also causes the order of the data series to be reversed.
I have been through every right-click option on the chart and cannot see why this is. I have also created a new chart from the same source data, and this has the y axis on the left, as it ought to be.
I would be grateful if anyone could shed some light on this. I am using Excel 97.


How can I combine the feature of these two charts; In the chart I want to plot the data points for weeks (bottom chart) but have the "X" axis show the letters of Months like top chart and mayor grid lines showing this 12 month.

Thanks in anticipation for your replays.

I've build a workbook to run a simulation that creates two chart sheets. Each chart contains four data series generated by a function that calls the same chart creation function. I noticed that the second chart contained some but not all series from the first chart. So I ended up adding the following after the Charts.Add line:

    For i = Charts(1).SeriesCollection.Count To 1 Step -1

There is probably a method that avoids needing this, but at least it works. Perhaps it applies elsewhere.


So, I've written a Macro that alters all X-axis of all charts in all chart sheets in a workbook. When this macro is run, somehow SOME charts change in size in their own chart sheet.

In 2003 you could set the 'Sized with window' option, however this does not exist anymore. It is also not a zoom problem as I then only zoom in on the chart, however the chart still does not fit in its display (of the chart sheet). For example, I can't see the end of the line as this disappears behind the chart canvas.

Does anyone know how to change this with a single button or do I have to do this manually?

I have 3 data series, which I'd like to disply in a bar chart, but would like to do it in one chart, rather than 3 separate charts for faster processing speed. Is it possible to have several (more than 2) Y-axes in bar chart (or any chart for that matter)?

Attached is an example spreadsheet that has the 3 charts that I'd like to incorporate into 1 chart. I'd like the 1 chart to look as close as possible to the 3 idividual charts.