Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm plotting data in an Excel 2003 chart with a logarithmic scale on the Y axis. Some data points are occasionally missing, and this is represented by blank cells. The resulting graph looks like the one below.

There is a gap in the curves at x=150, and that is great because it really emphasizes the fact that some data is missing at that point. This is part and parcel of the information that the graph should convey.

However, Excel throws a warning every time I touch the graph: "Negative or zero values cannot be plotted correctly on log charts." This is incredibly annoying.

Is there a way to suppress this warning?

Here is what I know already:

  • Blank cells get interpreted as zero by the chart, and plotting zero values on a logarithmic chart makes no sense. This is what triggers the error.
  • If I make the missing-data cells #N/A instead of blank, the warning goes away. However, the gap in the curves also go away, which is not good.
  • Application.DisplayAlerts = False would probably work during the execution of a VBA macro, but my question is not within the context of a running macro. (Note that Application.DisplayAlerts automatically gets reset to True when execution terminates.)

enter image description here

enter image description here

share|improve this question
Is Excel-2003 a firm requirement? I tried it in 2010, got one warning when creating the chart, after that no more warinings – chris neilsen Oct 28 '11 at 9:17
Unfortunately, until my company decides to upgrade to 2010, it is a requirement! – Jean-François Corbett Oct 28 '11 at 10:51
Blank cells are not interpreted as zeros. Cells that return "" so they appear blank to us humans actually contain a string (the null string), and are interpreted as zero. Change your formula so they return NA() instead of "" for null values. They look ugly in the sheet, but Excel doesn't try to plot them, and instead interpolates a line across them. – Jon Peltier Jan 11 at 22:09

A workaround but I would install AutoHotkey and set it to immediately dismiss that dialog when it appears:

; Example #1: Close unwanted windows whenever they appear:
SetTimer, CloseMailWarnings, 250

WinClose, Microsoft Excel, Negative or zero values cannot be plotted

You'll soon think of 50 windows and dialogs you'd like to close as soon as they appear so the initial effort to install it and set it up will soon pay off.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .