Strange Problem With Chart And Plot Visible Cells Option

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.


Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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

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


Im a student of economics in Vienna, and am currently working on my diploma's thesis.
Right now, I want to analyse my survey data, which consists of opinions to many different online community features.

All the opinions are in "Worksheet 1".

Each feature now has its own Worksheet, where data from "worksheet 1" is displayed.
It is collected via a series of relational functions, which depend on Cell A1.

If, for instance, I want to analyse the responses for "Forum", I simply type "Forum" into Cell A1 and it will get all the responses for "Forum".

I then copied this sheet, exchanged "Forum" for "Wiki" and so on....

My problem is now, that each sheet has a "scatter plot" chart on it, which charts one value only. (On the Worksheet "Forum" it only plots (X,Y) for "Forum")
Irrespective of the attribute (i.e Wiki, or Forum) it will always only chart values between 0 and 1 on both axis.

Now, for some strange reasons however, the X-axis always changes its length, when the word (Wiki, Forum etc..) changes.

I have already formatted both Axis as follows:
vertical axis cross: 0.0
min: 0
max: 1
major grid: 0.5
minor grid: 0.1

also, i have formatted Chart tools --> Format --> Size --> Properties --> "Don't move or resize cells"

Unfortunately the X-axis is still not locked, but changes depending on where the Datapoint of the attribute lies on the chart:

If X-Value > 0.5 then "Chart axis grows in length"
If X-Value < 0.5 then "Chart axis shrinks in length"

Could someone please tell me if there is a possibility to define an absolute chart size and axis-length?

Thanks a lot for your help in advance!

kind regards,


I have a bunch of charts that I need to plot voltage and current measurments on primay and secondary axis respectivly.

Is there anyway to do this when I create the chart? Each chart will have four different series on the primary axis and eight on the secondary.

The problem I have is when creating the chart everything gets put on the primary axis. when I select a data set and place it on the secondary axis Excel automatically adds markers to the line. Now I have to go it and remove the markers.

This just creates more work for me.

Is there any way to place a data set on the secondary axis without Excel adding markers or doing anything else I did not ask for?

Hi - I have a workbook with a number of sheets, each which have a single chart, which the organization I work for use to create a standardized report for clients. Each client provides us with a different data set (from a survey), and thus the chart ranges / origin need to change with each new data set.

All charts across the different sheets use the same Range / Origin from the dataset.

I found this Macro in a previous forum which seems to work for one chart, and one sheet. Any way to tweak this so that it works for all charts (or perhaps just more than 1 chart), all sheets?

Private Sub SetAxisScales()
With ActiveSheet.ChartObjects("Chart 1").Chart
With .Axes(xlValue)
.MaximumScale = ActiveSheet.Range("Ymax").Value
.MinimumScale = ActiveSheet.Range("Ymin").Value
.MajorUnit = ActiveSheet.Range("Ymajor").Value
.MinorUnit = ActiveSheet.Range("Yminor").Value
End With
End With
End Sub

Hi guys,

I'm trying to add a chart to a userform using the Microsoft Office Chart 9.0 control.

I have the following code, taken from a website, but I get a 'type mismatch' error at the red line:


Option Explicit

Private Sub UserForm_Initialize()
    'Sheet1.Visible = False
    With ChartSpace1
         ' Add a chart.
         ' Set the data source of the chart to the Spreadsheet control.
         Set ChartSpace1.DataSource = Sheets("Sheet1").Range("H2:S3")
        With .Charts(0)
             ' Create a bar chart.
            .Type = chChartTypeBarClustered
             ' Add two data series to the chart.
             ' Set the properties of the first data series.
            With .SeriesCollection(0)
                .SetData chDimSeriesNames, 0, "B1"
                .SetData chDimCategories, 0, "A2:A5"
                .SetData chDimValues, 0, "B2:B5"
            End With
             ' Set the properties of the second data series.
            With .SeriesCollection(1)
                .SetData chDimSeriesNames, 0, "C1"
                .SetData chDimValues, 0, "C2:C5"
            End With
             ' Display the legend.
            .HasLegend = True
        End With
    End With
End Sub

How on earth do I set the data series to a range on a spreadsheet?



I have a dynamic chart in Excel that uses named ranges to adjust the data that is charted. The series in the charts are input as: ='myfile.xlsx'!chartrange

Everything works fine in Excel but I'd like to link these charts into Powerpoint to have the slides update automatically. I copy the chart from Excel and paste-special as a linked chart into Powerpoint. The Powerpoint chart continues to update dynamically with the Excel data, until I close the Powerpoint file. When I re-open, the data is static and will not refresh. If I check the source data in the chart in Powerpoint, it now has the series as: ='[0]'!chartrange

So it appears that when I close Powerpoint I lose the link to source file. I only have this problem with dynamic ranges and suspect it's simply a limitation in the linked chart functionality, but would appreciate any suggestions. Thanks!

I'd like to plot a stock chart with open/high/low/close bars ("candlesticks" format) and add a moving average to it. Because of the nature of the data and how I use it, the chart X axis has to be plotted in "reverse order". Once I do that, however, excel calculates the moving average values not starting from the right-most value on the chart, but from the left-most. How would I change this default setting?

Attached is an example of what I'm looking for. I'd like to avoid using 2 charts overlayed on top of one another as my final product is quite large and doing this takes memory and processing speed.

Thank you.

I have a many charts that my department uses. I would like to make it easier on each user by resizing the chart to maximum size for the display they have, and also DESELECT any parts of the chart that may be selected.

I have tried...

Private Sub Chart_Activate()
    ActiveWindow.Zoom = True
End Sub

...but it does not DESELECT the chart.

Is this an already known problem with Excel 2007?

I have a few small bar charts containing data from a survey such as Yes/No/Maybe questions. They are listed in that order (first Yes, then No and Maybe is the last option). When I use this data to make a bar chart, the data on the vertical axis is ordered the other way, Maybe on top, then No, then Yes.

If I go to Format Axis I can tick "Categories in reverse order" but that puts the horizontal axis (and its labels) above the graph.

I can fix this my changing the order of the source, but that's not only a very dirty solution, it also messes with the fomulas I use to get the data (shifts ranges).

Any ideas? I use Excel 2007.

I have a dynamic xy chart that has a shrinking plot area problem. Each time I save the file and reopen it (whether I added data to the plot or not), the y-axis moves a tiny bit, shrinking the plot area. I have XL07, but the file is saved as 97-03 for compatibility. I'm not using any macros. It's a simple report with two embedded charts. One uses the secondary y-axis, and this chart shrinks in from both sides. The other chart only shrinks from the primary y-axis. Under normal conditions, I have all worksheets and the workbook "Protected" with a password to prevent users from reformatting anything, but it didn't make any difference to the problem. Typically, once per hour the file will be opened, a line of data is appended to the "Data" worksheet, the file is saved and closed. I can reopen the file, make no changes to it, and if I save it on closing then the next time I open it, the axes will have moved in the plot area. I also do not understand why it asks me if I want to save on exiting if I've made no changes to the file. Thanks for any help.

Hello Friends

Is it possible to add 3rd Y Axis to a chart?

I need to plot three series with different scales. One series is plotted as column and other two are line charts. I was able to create a secondary Y Axis but I can't create thrid axis though.

I am using Excel 2003

Thanks for your time.

I have a question about the best way to create a scatter chart. I have three sets of data that has been requested to be put on one graph, and that a scatter chart would be the preferred type.

The catch is that the three sets of data to compare on the chart are on three completely different scales. The x-axis is the date, but the three y-series are in the scale 0-100%, 0-10, and 0-1000. I am having a difficult time resolving the axis so that I don't lose the resolution of each series on the one chart.

An option that I thought was possible was to put two of the series on the same axis but have two different data labels for that axis, and then use a secondary axis for the other. I am using Excel 2003 and I just cannot find the option to do that. Can anyone please point me in the right direction or give me a work around to accomplish this type of graph with this type of data? As a side thought, would it also be even possible to have three different scales with three different axis labels on the primary axis?

Thanks very much!

Hi. I'm working on an excel sheet to track Quality Control data over a long span of time. I need to create a chart that utilizes a scroll bar for historical tracking. I've just looked through a bunch of tutorials about creating dynamic charts, using ranges, and incorporating scroll bars. Up to this point everything is moving smoothly except that despite the ranges moving along with new data, the chart's X axis refuses the automatically change it's minimum value. Maximum seems to be updating with the new data, but the minimum is stuck on the initial date and will only change is I manually change it through the Scale tab. I can't find any data on why this is happening and have gone as far as redefining my ranges and starting another chart, both with the same results. My Ranges are as follows.

Data =OFFSET(DataLabels,0,1)
DataLabels =OFFSET([first data point],[scroll position],20,1)

I'm using the Data Range for the for the chart's series value and the DataLabels Range for the X Axis labels. I'm at complete loss for the answer to this problem and have spent over a couple weeks trying to create this spreadsheet. If anybody has any ideas, please help.

I have a spreadsheet (Excel 2003, SP3) that looks at data from several different perspectives. Each tab contains numerical data and a chart linked to an independent dynamic range that I can vary vertically (date; shown on X-axis) and horizontally (series; shown as values on Y-Axis). The charts on each tab reflect the data that the individual Dynamic Ranges indicate.

I decided to look at the data from one different perspective and created a new tab which basically consisted of copying one of the other worksheets and changing a few parameters. I then created dynamic ranges, which I created based on "Indirect" and "Address" functions, just as I had in the previous worksheets. When I associate the Dynamic Range with the chart I created in the new worksheet using the "Source Data-> Data Range" dialog box, the chart values update. However, when I change the value in the listbox that controls the dynamic range, the range changes but the values shown in the chart do not.

Has anyone ever had an experience where the dynamic range changes but the chart that the range is linked to does not update? Does anyone have any ideas on how to trouble shoot this issue? Any help would be appreciated because this is the only thing that is keeping me from completing a very valuable tool. By the way, I have tried the "Offset" function and had the same results.

I have a chart that I'm using that has data that has ranges of values that can vary drastically. Originally I had the chart on it's own sheet and I had no issues. Now I have a need to copy that same chart onto a worksheet. The problem that I encountered after copying the chart to a new worksheet is the plot area can change in physical size and over run a label I have underneath the chart title and the legend below. How do I go about keeping the X and Y scales on auto but keep the plot area fixed in size when a chart is on a worksheet?

Any help would be appreciated...


Hi everybody!
I have posted this before but I never got any replies so I did some testing
myself. I I want to share what I have found out with you and it would be
great to hear if someone else recognizes this.

I have a number of embedded charts on a scorecard sheet that gets updated
from several sources. I run this scorecard for different business units, take
pictures of the scorecards and put in another Excel file that I later make a
PDF of.

Problem: After running this a few times, I find that my chart legends have
moved around a bit and that some plot areas have been resized.

Solution: I have two macros. One that saves information about size and
postion of chart elements, which I run once when I have set up the charts as
I want them, and one that reformats the charts according to the saved
information, which I run after each run of the scorecard.

My findings: This seem to happen for charts where the legend is not at a
default placement (top, bottom, left, right, corner) and the chart is
automatically reformatted due to new data (or simply when you zoom in and out
a few times).

Am I doing something wrong here or is this a bug?
(I'm using Excel 2003. Try this by creating a simple chart and move the
legend manually, then zoom in and out a couple of times and see if the legend
has moved.)


I have a chart with two axis. One showing value and one showing percent. When the curve or column goes of the chart I update the scale but then the category axis is not aligned (0 value and 0% is not on the same horisontal axis any longer). I then need to recalculate and manually adjust the scale to have 0 on same horisontal axis.

Is there any way of having this done automatically, it's currently not an optimal situation since it's time consuming to correct all the charts (the original file has many charts).

Attached is an example with data sheets and chart sheet.


Financial Analyst

Excel 2003, Win XP

I've got some complicated dynamic charts for which the easiest solution was to overlay 2 or 3 identically-shaped charts, with various data appearing or disappearing depending on the output of form controls.

My problem is: the size of the plot frame changes when the data (and thus, labels) changes on the "bottom" chart. But since I've not got labels on the above charts, they don't change size, so the data becomes misaligned.

Wondering if there's an easy way to say: "You charts, you just stay the size you are." Haven't been able to find any simple formatting to do that. I'm open to code, I just don't know where to begin.

Many thanks!

Hi! i'm having some trouble with piled column charts (the second option of column charts).
I have a simple chart of piled columns. I created the chart to show in a same column 1200 and 30000, so the size of the second block should be much bigger, but it's not!!! If i change the series order, it's correct, but all the other charts are in other order, so I don't want to change it!
I'm using excel 2003.
Any comments?

is there a way to make the chart background be transparent in Excel 2007?

That is, I want to be able to see cells behind a chart or put to charts one on top of another and have both of them show up instead of the top plot area block the apearance of the bottom chart?