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

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

Make a Transparent Chart in Excel
How to make a Chart transparent so that it blends in with its background and surroundings in Excel. Hover your mous ...
Add Image Background to a Chart in Excel
How to use an image for the background of a chart in Excel. Simple but fun way to spruce up charts. Click the desi ...
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 ...
Filter and Sort Data on Charts in Excel
Create a dynamic chart in Excel that displays only the data you want. You can filter it and sort it in order to dri ...

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



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.

I am trying to add a secondary vertical axis to a bar chart in Excel 2007.
I used to add them with ease in 2003 but our company has upgraded us all and I am fearful that they have scrimped and given us an inferior build to the full MS Excel 2007.

I have followed the MS inbuilt help thus far and completed these steps okay .......
In a chart, click the data series that you want to plot on a secondary vertical axis, or do the following to select the data series from a list of chart elements: Click the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the data series that you want to plot along a secondary vertical axis. On the Format tab, in the Current Selection group, click Format Selection. The Format Data Series dialog box is displayed.

Note If a different dialog box is displayed, repeat step 1 and make sure that you select a data series in the chart. On the Series Options tab, under Plot Series On, click Secondary Axis and then click Close. It is at this point I stumble as there is no option to Plot Series On against the Series options tab for me to select. I think I have checked the Excel options ok, as I wondered if it was a feature to enable / disable. Or am I looking in the wrong place?
I am unable to move on the the next part

any help would be appreciated

I have a worksheet template that I copy, rename, replace. This worksheet has anywhere between 1 and 20 different charts that need to be updated. Unfortunately the charts don't have any order in numbers and i would like to keep the flexibility of adding/removing charts. How would I create something that would have the following structu

For k = 1 to 100
if chart(Chart k) exists then
cells(i,3).value = k
end if
if chart(Chart k) does not exist then
i don't want and error and i want to keep going
end if

I will then go through and update all charts that populated a cell with a value

Thank you for taking a look at this,

-Arthur (Junior Varsity VBA)

Hello all,

I ran into some issues regarding how Excel automatically scaled some of my charts - mainly I didn't like how it determined what should be the maximum or the minimum values of the Y axis of my Line chart.

Manually changing the scaling isn't an option I would like to pursue as when my project is done I will have about 800 charts in numerous files.

I found some VB code out on a website where I can enter a value in a cell and have that value go to the chart (, modified it just a bit, but I cannot make it work.

Here is the code I have:
Sub minMax()
Dim wks As Worksheet
Dim cht As ChartObject
Set wks = ThisWorkbook.Sheets("Charts")
Set cht = wks.ChartObjects("Chart 3")
cht.Chart.Axes(xlValue).MinimumScale = wks.Range("a1")
cht.Chart.Axes(xlValue).MaximumScale = wks.Range("b1")
End Sub

In A1 on the "Charts" sheet, I entered my minimum and in B1 I entered the maximum.

When I run the macro, the Y axis changes to a bunch of zeros and negative ones, along with moving the Y axis to the top of the chart.

Can someone help me fix this code?


Dear Excel community,

My problem concerns the mixing of chart types in Excel 2007. A complex one, see the length of my post.

As part of a professional assignment, I've created the following chart:
two series of the stacked area type for consumption two series of the (non stacked) line type for budget and forecast one serie of the scatter plot type for milestones The last step in the creation of this chart is the labeling of the milestones with their name (neither the x nor the y value). Given the report is to be distributed and, ideally, retrocompatible (2003), the XY Labeler add-in is not an option (or am I wrong?).

I've thus used a macro to assign the correct labels to the milestones, which runs correctly. Several strange things happen however:
the labels are not shown although they have been correctly assigned if I remove all series but the scatter plot one, the labels become visible if I save and reopen the file, the labels are not visible anymore the same technique has been used with another graph made of stacked areas and one scatter plot serie, and works fine All this makes me think the issue comes from the mixing of 3 chart types. I've tried to replace the stacked areas by non stacked areas but the story remains the same.

Any idea on how to solve this?

Thanks in advance.