Hidden data

  • Thread starter Thread starter Mike
  • Start date Start date
M

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.
 
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
 
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.
Click to expand...

--

Cheers
Andy

http://www.andypope.info
 
Andy said:
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.
Click to expand...

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
 
Thanks, Jerry and Andy. It seems like as much as we
learn about excel, there is always something new.

Mike.
 
Back
Top