Add a Horizontal Line to a Column Chart in XL 2007

[Fabio Coatis]

Hi,

John Peltier´s method ( http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html)
doesn´t work in xl 2007, any comments?

TIA

 

[Jon Peltier]

Sure it does. The various commands are no longer found on the familiar
toolbars and dialogs, but in general the protocol works fine.

Create a column chart.
Copy the XY data, Select the chart, Paste Special as New Series.
Convert added series to XY type.
Remove secondary Y axis, format secondary X axis.

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


Hi,

John Peltier´s method (
http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html)
doesn´t work in xl 2007, any comments?

TIA

 

[Fabio Coatis]

Hi Jon,

I just was following your directions using the column chart, but when
I was pasting special the sencondary axis data ( 0 and 1) the series
did not appear in the chart so that I couldn´t select it to change the
data type for xy scatter. Maybe I have an old version of XL 2007.

 

[Jon Peltier]

Did it not appear at all, or was in not visible for some reason? Are the Y
values similar in magnitude to those of the columns already plotted?

In the dropdown that lets you see what chart element is selected, and select
other elements, can you see and select the new series? (I don't have 2007
right in front of me, but I think the dropdown is on the second and third of
the Chart Tools tabs.)

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


Hi Jon,

I just was following your directions using the column chart, but when
I was pasting special the sencondary axis data ( 0 and 1) the series
did not appear in the chart so that I couldn´t select it to change the
data type for xy scatter. Maybe I have an old version of XL 2007.

 

[Fabio Coatis]

Did it not appear at all, or was in not visible for some reason? Are the Y
values similar in magnitude to those of the columns already plotted?

In the dropdown that lets you see what chart element is selected, and select
other elements, can you see and select the new series? (I don't have 2007
right in front of me, but I think the dropdown is on the second and thirdof
the Chart Tools tabs.)

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


Hi Jon,

I just was following your directions using the column chart, but when
I was pasting special the sencondary axis data ( 0 and 1) the series
did not appear in the chart so that I couldn´t select it to change the
data type for xy scatter. Maybe I have an old version of XL 2007.







- Mostrar texto entre aspas -

Just figured it out. In my case the problem was related to the
classification of dates. In your example they were in descending
order, when I changed to ascending the series appeared. Can you
confirm if this problem is happening in your version of excel?

Thank you for your help.

 

[Jon Peltier]

I don't know why that should make a difference, but it does, bug report to
follow. When you paste the new data onto the chart, Excel does add another
series. There is room for it in the cluster of columns already present. The
series is not visible, for whatever reason. You can still access the series,
not using the up/down arrows as I always advise, because that didn't seem to
select the series, but by selecting the series in the Chart Element
dropdown, the top left control on the Chart Tools > Layout and Chart Tools >
Format tabs. Once it is selected, you can change it to an XY type, and the
rest of the protocol is unchanged.

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

Did it not appear at all, or was in not visible for some reason? Are the Y
values similar in magnitude to those of the columns already plotted?

In the dropdown that lets you see what chart element is selected, and
select
other elements, can you see and select the new series? (I don't have 2007
right in front of me, but I think the dropdown is on the second and third
of
the Chart Tools tabs.)

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


Hi Jon,

I just was following your directions using the column chart, but when
I was pasting special the sencondary axis data ( 0 and 1) the series
did not appear in the chart so that I couldn´t select it to change the
data type for xy scatter. Maybe I have an old version of XL 2007.







- Mostrar texto entre aspas -

Just figured it out. In my case the problem was related to the
classification of dates. In your example they were in descending
order, when I changed to ascending the series appeared. Can you
confirm if this problem is happening in your version of excel?

Thank you for your help.

 

[Fabio Coatis]

I don't know why that should make a difference, but it does, bug report to
follow. When you paste the new data onto the chart, Excel does add another
series. There is room for it in the cluster of columns already present. The
series is not visible, for whatever reason. You can still access the series,
not using the up/down arrows as I always advise, because that didn't seemto
select the series, but by selecting the series in the Chart Element
dropdown, the top left control on the Chart Tools > Layout and Chart Tools >
Format tabs. Once it is selected, you can change it to an XY type, and the
rest of the protocol is unchanged.

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











Just figured it out. In my case the problem was related to the
classification of dates. In your example they were in descending
order, when I changed to ascending the series appeared. Can you
confirm if this problem is happening in your version of excel?

Thank you for your help.- Ocultar texto entre aspas -

- Mostrar texto entre aspas -

Thanks for the explanation. Good to help you on reporting a bug.

Regards.