Hidden data

[Mike]

Hi. I have a line chart that is in a worksheet with the
data below it. There is one series where the value will
always be constant. I have two questions:

1) Is there a way to have the series in the chart "point"
to one cell so that each y coordinate will be this value
for each x point in the series.

2) If not, is there a way to "hide" the rows containing
the constant series? If I hide the row now, the series
disappears from the chart.

Thanks,
Mike.

 

[Jerry W. Lewis]

With an "XY (Scatter)" chart with an arithmetic scale for the x axis, it
is fairly straightforward. Add a dummy data series that spans the data
range. Select that dummy data series and change its formula from
something like
=SERIES(,Sheet1!$A$1:$A$7,Sheet1!$B$1:$B$7,2)
to something like
=SERIES(,(Sheet1!$A$1,Sheet1!$A$7),(Sheet1!$X$1,Sheet1!$X$1),1)
where X1 is the cell containing the constant value.

It is a bit more complicated with a "Line" chart, but John Peltier gives
directions at
http://peltiertech.com/Excel/Charts/ComboCharts.html#AddLine

Hidden cells do not appear on charts by design, and that cannot be altered.

Jerry

 

[Andy Pope]

Hi Mike,

You can hide the row and have it plotted if you uncheck the "Plot
Visible Cells Only" option.

Select the chart and then use the menus Tools > Options you will find
the "Plot visible cells only" on the Chart tab.

Hi. I have a line chart that is in a worksheet with the
data below it. There is one series where the value will
always be constant. I have two questions:

1) Is there a way to have the series in the chart "point"
to one cell so that each y coordinate will be this value
for each x point in the series.

2) If not, is there a way to "hide" the rows containing
the constant series? If I hide the row now, the series
disappears from the chart.

Thanks,
Mike.

--

Cheers
Andy

http://www.andypope.info

 

[Jerry W. Lewis]

Hi Mike,

You can hide the row and have it plotted if you uncheck the "Plot
Visible Cells Only" option.

Select the chart and then use the menus Tools > Options you will find
the "Plot visible cells only" on the Chart tab.

That's a new one for me, thanks. I do agree with Excel not plotting
hidden cells by default, that behavior is extremely useful.

Jerry

 

[Mike]

Thanks, Jerry and Andy. It seems like as much as we
learn about excel, there is always something new.

Mike.