How to prevent Excel 2003 from automatically change chart type

At the occurence of a blank cell in the series of X values of an XY chart,
the chart automatically change to a line chart (which is useless in the
context). This drastically reduces the efficiency of Excel, for example:
1) I have to use double records of data, one containing blanks for
statistical operations and one containing #NA for XY charting
2) A single series chart can transform into charts of 20 or even hundreds of
series to circumvent the problem

This problem occurred in Excel 2003, MIcrosoft Office 2003 professional
edition

Click on chart; use Tools|option and open Chart tab; check the box: How to
treat blanks->interpolated
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Oscar II" <Oscar [email protected]> wrote in message
news:[email protected]...
> At the occurence of a blank cell in the series of X values of an XY chart,
> the chart automatically change to a line chart (which is useless in the
> context). This drastically reduces the efficiency of Excel, for example:
> 1) I have to use double records of data, one containing blanks for
> statistical operations and one containing #NA for XY charting
> 2) A single series chart can transform into charts of 20 or even hundreds
> of
> series to circumvent the problem
>
> This problem occurred in Excel 2003, MIcrosoft Office 2003 professional
> edition

> 1) I have to use double records of data, one containing blanks for
> statistical operations and one containing #NA for XY charting

People have such a problem with this, but in fact, it's best to have THREE (or more)
ranges of data. The first is the original data itself. The second is the data nicely
formatted for tabular display, linked to the original data. The third is the data
processed to be included in a chart, linked to the original data. If the source data
for further processing (stats, etc.) must be different, then you need even a fourth
range, linked to the original data. All of these ranges need not clutter up the same
sheet. It may be best to have the original data on a separate sheet, perhaps hidden
to prevent someone altering it, then put the chart on the sheet with the tabular
display data.

If you're worried about how much memory you're wasting, stop. Worksheets are cheap
on modern computers, and it saves you lots of time if you don't have to keep
fretting about optimizing the data for too many purposes at once.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Oscar II wrote:

> At the occurence of a blank cell in the series of X values of an XY chart,
> the chart automatically change to a line chart (which is useless in the
> context). This drastically reduces the efficiency of Excel, for example:
> 1) I have to use double records of data, one containing blanks for
> statistical operations and one containing #NA for XY charting
> 2) A single series chart can transform into charts of 20 or even hundreds of
> series to circumvent the problem
>
> This problem occurred in Excel 2003, MIcrosoft Office 2003 professional
> edition