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


Advertisements


Free Excel Forum

Tools Options Chart Question

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

Tools Options Chart Why is " Plot Visible Cells Only " is Dimm?

See Attached print screen.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

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
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
Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics







Win XP HE, XL 2002 SP3
Hi,
I am doing a rush presentation and am stuck...!
Under Tools|Options| Chart tab, the otion to "Plot empty cells as: Not
plotted" is grayed out.
Yet, that is precisely what I need to do.
How can I fix that?
I have other charts where the optin is available.
What am I doing wrong?
S







Hi,

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

Can anyone help please?

I have a column in my woksheet that contains formulae that refers to blank
cells. Therefore the result is zero in each cell in my column. When I use
the data in a chart, I set Tools, Options, Chart tab - plot empty cells as
"Not plotted", but it still plots the cells as zero and distorts my chart.

Is there any way to get is to plot the cells that have a formula where the
result is zero, as "not plotted"?

Any help would be appreciated.

--
Thanks,
Rob E.



Hi,

I have an excel 2003 document that has a heap of charts in it. Source data for the charts is on a hidden sheet and I've unchecked the 'show names' and 'show values' check boxes in the Tools + Options + chart tab menu.

Reason for this is that I don't want anyone viewing the document to have access to any of the underlying data, however I need to keep the charts active, rather then saving them as pictures, as I have set up macros so that anyone viewing them can update them with the click of a button.

Anyway, my question is this: Is there a way for me to protect the document in such a way that anyone viewing it cannot access or change any of the options available in the Tools + Options + chart tab menu?

Any help is very much appreciated!

Cheers,
Kerry


I am having trouble plotting visible cells only within a worksheet. I created a chart that has source data in 3 columns of a list where each row contains daily data from 2006-2008. The list is not filtered, however I have hidden columns prior to 2007.

When selecting the chart I have gone into Tools->Options and have checked the box "Plot visible cells only", but the chart still plots 2006-2008 data. Does Excel have issues with correctly plotting data contained in a list when this box is selected?


Hi,

Here is my problem, I import a txt file wich the number of rows varies. And
I want to make a macro that plot the ''good'' value. I tried to put #N/A
everywere, because they are not suppose to be plot... But they are still
plot! Even by changing option:Tools>Options>Chart> tick in Plot visible cells
only.

ex:
2006-07-03 34 65
2006-07-04 65 76
2006-07-05 98 90
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A

I'm looking for a solution since a month! Any other idea?

Thanks a lot!





Several items here...

- In Excel, when one plots a chart containing a LOT of data with anything
other than a solid line, it is impossible to use any other format because the
line formatting restarts with EACH DATA POINT. Let's say I have 5 plots I
want on one graph. I have a black and white printer. I need to use symbols
to differentiate which one is which. But, because each plot has 1000 data
points, everything looks the same! It would be better if you could make the
line format be scaled according to, say, a percentage of the plot window
width.

* Dashed lines: the width of the dash is specified as a percentage of the
plot width--say 2%...that way you'd always see it, with 10 or 10,000 data
points.

* Symbols--allow symbols to be plotted as part of the line, rather than only
as data points.

- Also, the "Tools...Options...Chart Options" route required to change how
blank cells are interpreted on the chart ("interpolate", "gaps", or "zero")
should be part of the chart options or series options. The feature also
doesn't work with multiple data series in one chart. I had two plots on one
chart, and both possessed areas where data was missing. Individually, I was
able to make them interpolate between gaps. When combined, neither would. I
had to wade through 20,000 data points and delete the empty zones for it to
work.

- Add the ability to put the area under a curve in a scatterplot in as a
shaded region.

- Add the ability to do a "zoom plot"--this is where I make a chart, and
wish to examine one area of it in a breakout window in much higher detail.
This could be done through selecting a region to zoom in on, and then having
Excel insert a mini-chart into the existing chart.

- Add the ability to have items plotted that do not appear in the legend.

Thanks,
Christian



Hi,

I am working on a big chart, some of the series have blank cells (were there is no data available), this is expected and I want those blanks to be uncharted.

However, my chart is returning those blanks as 0s (zeros) and charting them at zero. The cells of the series are made of the following formula:-

=IF(E3=0,"",E3-E$7) ~ which says make the cell blank if Ex is 0, else subtract E7 from Ex

Here is a picture which explains it better:-

http://img338.imageshack.us/img338/1...problempa2.jpg

When I go to Tools>Options and CHART tab, I have the following settings:-

Active Chart Section:-

Plot empty cells as "Not Plotted"
Plot visible cells only (checked this box)

Please let me know if these are the correct settings and if anyone has any ideas how to fix it.


I have an excel 2003 workbook that that I have been using for many years - without a problem. One of the worksheets in the workbook is a chart. On this windows Vista system, with a screen resolution of 1920 x 1200, the chart displays on top of a page outline; covering a full page. The setup on this file is:
Page Setup - Chart - Full Page
and under
Tools - Options - Chart - chart sizes with window frame is checked

I am now having a problem on one of my newer Windows 7 computers in how this one chart is displayed.

When I take this same workbook and put it on a new computer that I have (windows 7) also running excel 2003, I get a different look on the chart worksheet
The worksheet no longer looks like a page outline with a chart covering the entire page. It now looks like a wide blank screen with the chart covering only a fraction of the screen, and the chart titles covering across the entire width of the monitor.
In looking at the settings, the settings are as follows
Page Setup - Chart - is full page - as before
and under
Tools - Options - Chart - chart sizes with window frame is now checked
when i go to uncheck this, it physically unchecks, but when you accept the change, nothing happens and when you go back to see if it is still unchecked, the check reappears.

The other thing that I notice is that the zoom control for the page is blanked out with no value in it, and you cannot enter a value.

I have not been able to figure out how to consistantly get the check to uncheck, but sometimes I can get it to do so. It may have something to do with the printer although I do not know why because the printer will print what is shown on the monitor. The reason why it may have something to do with the printer is because sometimes to get the box to uncheck, I have to change the print option to Microsoft XPS Image Writer instead of my printer, print a document file to my desktop, and then go back and change the Tools-options-chart - chart size with window frame - when it will uncheck.

BTW - the printer is the same printer for both computers as it is a network printer.

If I then save this file to my desktop, the next time it opens it is ok, but if I open the original file from the other computer, I have the same mess all over again.

Is there a way to prevent this from displaying incorrectly?


I have 'IF' formulas in my data set that are returning an "" if false. The chart referencing that data is plotting the "" as a zero value. I have gone to Tools-Options-Chart-Plot Zero Values As, but there is not an option for ignoring "" results.

Is there any way to have the chart ignore the ""?


I have an excel 2003 workbook that that I have been using for many years - without a problem

I have used in on many computers but am having a problem on one computer with how a chart is displayed on a separate worksheet.

The setup on the computer that works (and within the excel file) under:
Page Setup - Chart - is use full page
and under
Tools - Options - Chart - chart sizes with window frame is unchecked

the result is that the worksheet with the chart appears to be a page outline with a chart covering the entire page.
This is excactly what I want

When I take this same workbook and put it on a new computer that I have (windows 7) also running excel 2003, I get a different look on the chart worksheet
The worksheet no longer looks like a page outline with a chart covering the entire page. It now looks like a wide blank screen with the chart covering only a fraction of the screen, and the chart titles covering across the entire width of the monitor.
In looking at the settings, the settings are as follows
Page Setup - Chart - is full page - as before
and under
Tools - Options - Chart - chart sizes with window frame is now checked
when i go to uncheck this, it physically unchecks, but when you accept the change, nothing happens and when you go back to see if it is still unchecked, the check reappears.

The other thing that I notice is that the zoom control for the page is blanked out with no value in it, and you cannot enter a value.

I have not been able to figure out how to consistantly get the check to uncheck, but sometimes I can get it to do so. It may have something to do with the printer although I do not know why because the printer will print what is shown on the monitor. The reason why it may have something to do with the printer is because sometimes to get the box to uncheck, I have to change the print option to Microsoft XPS Image Writer instead of my printer, print a document file to my desktop, and then go back and change the Tools-options-chart - chart size with window frame - when it will uncheck.

BTW - the printer is the same printer for both computers as it is a network printer.

If I then save this file to my desktop, the next time it opens it is ok, but if I open the original file from the other computer, I have the same mess all over again

Help!


In Excel I have a few spreadsheets which use charts, just today when I mouse over the bars the little "Chart Tip" stopped showing up. I checked in Tools/Options and under Charts the options are checked for both "Show Name" and "Show Value". Anyone see this before? Nothing changed on the system really.


Hi,

I rewrote this question to be a bit more clear.

On line charts, I know you can choose to prevent interpolation by using the Tools->Options->Chart tab option--which works when cells are empty. But I have cells with formulas that return #N/A. These cells don't plot--which is great--but I don't want the plotted line to interpolate the plots of data that come before and after the #N/A cells. I want gaps. Is there a way I can do this?

Thanks in advance,
Christiaan V


I am the admin of this XP box. The security says I have full control of my
files.

I cannot get into "Tools -> Options" in Excel 2003 sp1
I cannot delete worksheets either.

HELLP !!!!






Hi, All

Eventhough I select Plot Empty Cells as - Not Plotted (Leave Gap) in Tools | Options | Chart Tabl, my chart is plotting it as zero value. My cell does not have any value it is a blnnk cell. What could be the reaosn? May be one of you folks can help me on this is issue.

TIA
GNaga


I have charts where the Data Series contains a formula. When the formula evaluates to " " , I don't want it to plot the point, but it's plotting them as zeros. Setting Tools > Options > Chart to not plot empty cells doesn't do it, since the cells have a formula and therefore aren't empty. How can I get this to work?

-- Renzo


Hi:

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,

I am using Excel 2000 to create an interactive chart (one that allows the user to define the starting and ending points and which series are plotted) but I have a number of blank cells in my source data that are plotting as zeros. The blank cells are truly blank: I pasted values for the source data, there are no formulas.

I checked the chart options (Tools-->Options-->Chart) and "Plot Empty Cells as: Not Plotted (Leave Gaps)" is already selected.

As a test, I plotted a series that I manually selected from the source data - I left out the interactive stuff; same result. I have another file with a similar chart--though without the user-definable start and end points--and blank values are treated correctly.

I would appreaciate any help.

Thanks,
Christiaan


The help screen lies! That's a heck of a way to start this question.
Ok, I might be wrong, but I've checked SEVERAL times and can't get to what i need.


The help screen says:

You can plot data on a secondary vertical axis one data series at a time. To plot more than one data series on the secondary vertical axis, repeat this procedure for each data series that you want to display on the secondary vertical axis.

1.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:
1.Click the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

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


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

3.On the Series Options tab, under Plot Series On, click Secondary Axis and then click Close.A secondary vertical axis is displayed in the chart.



Ain't no Plot Series On, I see the Series Option, but there is no Secondary Axis.

All help is greatly appreciated.
Thanks, Phil


Hi:

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 the sample reproduction of a Chart I wish to implement:

A
1 1
2 "Comment: This is an empty cell, no value entered"
3 2
4 0
5 0
6 "Comment: The formula used is - =IF(SUM(A4,A5) = 0,"", SUM(A4,A5))"
7 4
8 3

Now, in A6 though the result is 'zero' it displays in the cell as empty, but
when I plot the line graph it shows zero in the plotting area. For A2, the
value is empty and the graph shows a break in the graph(empty space is not
plotted through Tools>Options>Chart>Plot Empty Cells as Not plotted). I want
the same result for the cell A6, that is the graph shows a gap plotted
whenever the value is zero(only in some cases) and plot the value when its
not zero.

To sum up: The graph should plot a gap when the value is zero(not always) or
empty cell.




I have a copy of Excel 2003 and I am unable to access "Options..." under the Tools menu. It is grayed out. I thought it might be to do with some work I did with automatically deactivating commands through VBA a while ago. Therefore I uninstalled through control panel and installed from the Office disc. Despite this I am still unable to access the options... command. Does anyone have any ideas how to fix this?

thanks,

PhilG


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


All, I have a data line within an excel line chart that has an empty value which I want to not plot as zero on my chart. I have gone to tools, options, chart and selected to choose empty cells as not plotted but excel is ignoring this request and still plotting them as zero. The only thing I can think of why it may be ignoring my request is that my line chart has a secondary axis. Any possible solutions out there?

Thanks


Using Excel 2007 and the "Insert Options" pop-up that occurs when I insert a
line is driving me batty! I've seen suggestions to go thru "Tools > Options"
but in 2007 there is not a "Tools" section, nor do I see anywhere to turn
that off in the "Excel Options" section. Has anyone been able to get rid of
it??? it blocks the data & is very frustrating!