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 Tutorials

Format Any Element of a Chart in Excel
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...
Prevent Charts from Printing in Excel
This is how you prevent a chart from appearing when you print from Excel. This is a great feature when you want to ...
Combine Multiple Chart Types in Excel to Make Powerful Charts
In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...
Update, Change, and Manage the Data Used in a Chart in Excel
In this tutorial I am going to show you how to update, change and manage the data used by charts in Excel. This tut ...

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.


I have a Chart on my Sheet, which is being displayed on Userform.

Now I want to hide chart data & chart from Sheet, but still let Chart be visible on Userform.

I hide Chart like this :

Please Login or Register  to view this content.

This works, no problem.

But when It comes to chart data, I have problems...

So far I have tried all this :

1. Formating these cells to ";;;", but that messes up chart data - on chart.

2. Hide rows in VBA, but then Chart is not displayed on Userform.

3. Clicked on the chart then went to Tools>Options >Select the Chart tab> unticked the "plot visible cells only" option. Nothing happens here ?!?

Does anybody have other suggestion, with VBA maybe ?

Thanks for help !!

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 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'm really new to this and need some help.
i need this macro to run on each of the chart names i will list below. right now it only works if i select a chart.
the macro changes the scale of the x axis of a graph.

Please Login or Register  to view this content.

list of charts the macro should use:
"Chart 40", "Chart 57", "Chart 58", "Chart 59", "Chart 60", "Chart 61", "Chart 62", "Chart 63", "Chart 70", "Chart 69", "Chart 68", "Chart 67", "Chart 66", "Chart 65", "Chart 56", "Chart 64"

please help, i know i should use a loop, but i don't know what the syntax would be.

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!

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


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.

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



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?


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?

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

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?

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

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.


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 have 80 charts on on a worksheet called "Viewer" which I have make dynamic by creating named formulas and inserting them into the chart series.

the formulas all reference a worksheet called "chart"

When I initially set up the chart series it look so:


Chart! is the worksheet
Chart1A is the named formula relating to the specific series

I do this for all 80 charts *5 series

the charts work fine, I crack open the champagne as they are now dynamic and work perfectly

THEN for some unknown reason after a short while when I go back and look at the series

I get =[0]!ChartA etc etc for all series on all charts, it is as if the worksheet Charts has disapeared even though I have not touched it

This is the second time I have relabeled all 80 charts, does anyone know why this
is happening would be most gratefull for any input?


Hi all,

I am trying to create a chart on userform.but unfortunately the code generates error "Copile Error - can not find Project and Library" when we choose chart type -".Type = chChartTypeBarClustered"

Option Explicit
Private Sub UserForm_Initialize()
'Spreadsheet1.Visible = False
With ChartSpace1
' Add a chart.
' Set the data source of the chart to the Spreadsheet control.
.DataSource = Spreadsheet1
With .Charts(0)
' Create a bar chart.
.Type = chChartTypeBarClustered

' Set the properties of data series.
With .SeriesCollection(0)
.SetData chDimSeriesNames, 0, "B1"
.SetData chDimCategories, 0, "A2:A15"
.SetData chDimValues, 0, "B2:B15"
End With

' Display the legend.
.HasLegend = True
End With
End With

End Sub

Please help.........


I know the technique of using Names to create dynamic charts, and they work great with 2D graphs. The problem is that I want to do this in a 3D chart. Is this possible?

When I set the chart's data range as a previously defined Name, the chart assumes the source data in the Name, but if this Name is altered, the chart doesn't update accordingly.

Any way around this? Or do I have to use VBA?



I have a excel sheet that has multiple charts in it. There is some vba code
that copies the charts into powerpoint. My problem is that sometimes one
chart and plot area is off a little from a different chart, so when it copies
into powerpoint, it does not look aligned properly. Does anyone know of any
way to force the plot and chart areas to be set the same values for all the
charts on the worksheet, before it gets copied over?



Hello. Here's a little background: We (about 30 people) are using a shared workbook -- it was created in Excel 2003, some users have 2003, some have 2007. We all are using Windows XP to the best of my knowledge. The workbook has about 15 sheets - each representing a different metric, each sheet has 5 charts prepresenting 5 projects (data for each chart is included in the same sheet; about 15 rows of data per project -- the chart is to the right of the data); total # of charts in the file is around 75.

When we were all using 2003, the file was working well -- each person will go to their metric sheet each month, filter the data to their program, update the data which in turn updates the chart, un-filter and so on.

Since 2007 users came on board (I think) is when we started having a problem with the plot area of the chart changing size -- basically it is squishing flat. The overall chart size remains the same, but not the plot area -- and the legend, the legend will also squish down to almost looking like a dot. The axis labels don't change size, just position -- but that is fairly easy to grab and move.

I can't repeat the problem -- seems we can filter/unfilter and have no problems, but when the problem does strike, it strikes just about all the charts. You can imagine how long it takes to stretch the plot areas back out so they are readable or in many cases, I can't grab a handle to stretch it so I "re-create" the chart. One option is to tell folks not to use the filter, but that will increase the chance of data being entered in the wrong project... I don't have "database" skills and someone is assigned to create a better tool for us...which will be awhile... so in the meantime, I'm looking for ideas/help.

Any advice? Thank you in advance for any help you can offer.