Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

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 ...
Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Change Axis Units on Charts in Excel
You can change the size of the units on a chart axis, their interval, where they start, where they finish, and mor ...
Changing Any Element in a Chart in Excel
In this tutorial I am going to go through the Layout tab in more detail and show you how to build up chart layouts ...

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





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.



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!





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?


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


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


Typically Excel 2000 line charts seem to plot any data point that has no Y value (i.e. empty cell) as zero. Even with the settings in Tools>Options>Charts:Plot empty cells as... set to 'Not plotted (leave gaps)' the problem remains.
Curiously however, when adding a second series to the line chart and plotting this on a secondary axis the problem (for this particular series) disappears.

All data in this instance is entered manually in to the data ranges (i.e. the problem isn't to do with formula results etc) but I understand that the issue also occurs when the plotted data is calculated and the result is "0", " ", or "N/A".

Has anyone got an explanation for this issue and/or had any comment from MS?

Thanks.


Dear all,

Now, my Excel program has this error :
When I Click "Book1 WorkSheet" Document. And Choose "Tools"-> Options on
the Menu. Then the "Options" Dialog appear, but I cannot click the mouse to
move any Tab between "View", "Caculation", "Edit", "General",
"Transition"....tabs of this above dialog, and this following error inform
"Your entry cannot be used. An integer or decimal number may be required". I
removed Excel program and reinstalled Excel again, but this error still
happen.
How can I correct it? please reply to me as soon as possible. Thanks you
very much





Hi there,

I have developed a excel workbook that is to be given to an external party and I need to lock it down as much as possible.

I have protected the cells/sheets by using Tools -> Protection, with a password the external party will not know.

I have also protected the workbook using Tools -> Options -> Security with a password to open, which the external party will know.

However I am still finding a few flaws in the protection, in that once the external party has logged in, they are able to go Tools -> Options -> Security themselves and remove the password.

AND, what is worse, they are able to go Tools -> Options -> View and they can click the 'Formulas' checkbox in the 'Window Options' section ... this is a major flaw in that even though the cells are locked and cannot be clicked in let alone view the formula inside them, by going through the tools menu anyone can essentially see the entire workings of the workbook.

Anyone with any experience or able to assist in this matter would be very much appreciated!


I'm trying to get the "Plot Area" within a chart as flush as possible with the "Chart Area" border on the right side. There seems to be a limit to how far I can drag the Plot Area border to the right, and I can't find any options for manipulating the margin between the Plot and Chart areas. Does anyone know how to do this or a workaround?

Many thanks.


I've got a spreadsheet with a lot of calculations embedded in it.

When I'm doing core work I go into Tools>Options and on the Calculation tab set it to "Manual" so I don't have to worry about waiting for a re-calculation every time I make a change. With this setting in manual I can re-calculate any time by pressing "F9".

Here's the question: if I were to leave the calculation option on "Manual" permanently, in the case of another user who was working with my sheets not knowing about the "F9" functionality; is there any simple way to make a "re-calculate" button that would do this?

FYI, I am not versed in macro's and/or vbasic at all.

Thx for your help.


When I change the values in the source sheet my linked chart is not updating automatically until the file is reopened. My links are still active. Is there some kind of option I need to toggle on?

In Tools, Option, Chart, I have plot visible cells, empty cells as not plotted, show nams and show values all turned on. On Tools, Option, Calculation, I have automatic turned on.

Help!


Hi,

I have written a couple of macros that enable me to move and change the values of chart axes using the Chart_MouseDown and Chart_MouseMove procedures, so effectively you can move around and zoom in on different areas of the plot.

The one thing I am missing is a method so you cannot select the chart or plot area so you can make best use of the whole screen to manipulate the plot, without moving the plot area et al.

I have tried all the options with protecting the chart but that prevents the axes from being modified. I saw this EnableSelection function but it seems to only be applicable to worksheets and not charts

Any ideas?

Thanks in advance

Adam


I have a long list of data which is displayed in a chart. The problem is, if I scroll down in the data the graph moves out of the screen and can not be seen anymore.

Is there an Excel setting that allows the chart to float or move with the data?
Is there a way that this can be set with a macro?

I attached an example to illustrate.

I know about the "Chart Tools/Format/Size and Properties/Properties/Move and size with cells" setting. But that doesn't seem to work in my example.


The title basically says it all...

"Tools > Options > Window Options > Page Breaks" is Greyed Out

How can I turn this back on? It's really frustrating. It's not letting me edit my page breaks.