Bubble chart will not pick X series

I am trying to draw a bubble chart.

The X values are dates in the next few years. However when the graph is drawn, these X values are ignored, and Excel uses the row number of the cell containing the data instead.

I can confirm this by moving the pointer over the bubbles. The label that pops up says <Point "01/01/09" (50, 6%)>, when really it should be <Point 50 ("01/01/09", 6%)>.

Has anyone seen this before? it's the second time it happens to me in different files.

Thanks in advance

Interesting. If the first column is Dates XL doesn't put it on the x-
axis -- at least not by default. What you can do is in the 2nd step of
the chart wizard, click on the Series tab and adjust the X- Y- and Size
fields. XL may actually create an incorrect 2nd series with the bubble
sizes as the y-values of this series. If so, delete it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> I am trying to draw a bubble chart.
>
> The X values are dates in the next few years. However when the graph is
> drawn, these X values are ignored, and Excel uses the row number of the
> cell containing the data instead.
>
> I can confirm this by moving the pointer over the bubbles. The label
> that pops up says <Point "01/01/09" (50, 6%)>, when really it should be
> <Point 50 ("01/01/09", 6%)>.
>
> Has anyone seen this before? it's the second time it happens to me in
> different files.
>
> Thanks in advance
>
>
> --
> lupocattivo
> ------------------------------------------------------------------------
> lupocattivo's Profile: http://www.excelforum.com/member.php...o&userid=26622
> View this thread: http://www.excelforum.com/showthread...hreadid=398971
>
>

In a quick test in Excel 2003 (then duplicated in Excel 2000), Excel by
default did use the dates as the X axis variable. The chart tip says

Series "y" Point "8/1/2005"
(8/1/2005, 50) Size: 0.625

If the "dates" in the first column of the data range are treated by
Excel as categories (i.e., valueless text), then you'd see the behavior
described by lupocattivo. What's funny, is that Excel sees them
formatted as dates, so my numbers 1, 2, 3 for the categories are
displayed as 1/1/1900, 1/2/1900, etc.

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


Tushar Mehta wrote:

> Interesting. If the first column is Dates XL doesn't put it on the x-
> axis -- at least not by default. What you can do is in the 2nd step of
> the chart wizard, click on the Series tab and adjust the X- Y- and Size
> fields. XL may actually create an incorrect 2nd series with the bubble
> sizes as the y-values of this series. If so, delete it.
>

And, you figured I reached my conclusion by just pulling it out of a
hat? {grin}

I just rechecked XL2003:
1-Jan 10 20
2-Jan 12 40
3-Jan 15 30

XL creates 2 series in a bubble chart. The y-axis are dates! The
legend shows two series plotted. Series 1 has self-created x-values,
the dates as the y values and 10,12,15 as the bubble sizes. Series two
is invisible and cannot be seen. After deleting series 1, the arrow
keys will located series 2. Its y-values are 20,40,30 and its bubble
sizes are {0,0,0}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#[email protected]>,
[email protected] says...
> In a quick test in Excel 2003 (then duplicated in Excel 2000), Excel by
> default did use the dates as the X axis variable. The chart tip says
>
> Series "y" Point "8/1/2005"
> (8/1/2005, 50) Size: 0.625
>
> If the "dates" in the first column of the data range are treated by
> Excel as categories (i.e., valueless text), then you'd see the behavior
> described by lupocattivo. What's funny, is that Excel sees them
> formatted as dates, so my numbers 1, 2, 3 for the categories are
> displayed as 1/1/1900, 1/2/1900, etc.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Tushar Mehta wrote:
>
> > Interesting. If the first column is Dates XL doesn't put it on the x-
> > axis -- at least not by default. What you can do is in the 2nd step of
> > the chart wizard, click on the Series tab and adjust the X- Y- and Size
> > fields. XL may actually create an incorrect 2nd series with the bubble
> > sizes as the y-values of this series. If so, delete it.
> >
>

I made no assumptions about how you reached your conclusion, other than
your data looked different than mine, and Excel interpreted it
differently (not too bad an assumption in general, and spot on in this
case).

If you use column headers, Excel does the bubble chart correctly:

x y z
1-Jan 10 20
2-Jan 12 40
3-Jan 15 30

The dates are used for X, (10,12,15) are used for Y, and the bubble
sizes are (20,40,30).

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


Tushar Mehta wrote:

> And, you figured I reached my conclusion by just pulling it out of a
> hat? {grin}
>
> I just rechecked XL2003:
> 1-Jan 10 20
> 2-Jan 12 40
> 3-Jan 15 30
>
> XL creates 2 series in a bubble chart. The y-axis are dates! The
> legend shows two series plotted. Series 1 has self-created x-values,
> the dates as the y values and 10,12,15 as the bubble sizes. Series two
> is invisible and cannot be seen. After deleting series 1, the arrow
> keys will located series 2. Its y-values are 20,40,30 and its bubble
> sizes are {0,0,0}
>