Hide data series in chart - toggle on and off
 

Excel 2002 SP3
Win XP HE

*Follow-up to: microsoft.public.excel.charting*

Hi,
I have a chart with 3 series. 2 are on right axis (secondary) and 1 on
left (primary).
Is it possible for me to toggle on or off 1 of the series?
I cannot just hide it by choosing "none" as plot data as it still plots
it even if not visible.
I am really trying to fix scaling problems....
Thanks.

Answer: Hide data series in chart - toggle on and off
 

Yes, it is possible to toggle on or off a data series in a chart. Here's how:

1. Click on the chart to select it.
2. Right-click on the data series you want to hide and select "Format Data Series" from the context menu.
3. In the "Format Data Series" dialog box, go to the "Options" tab.
4. Uncheck the "Plot visible cells only" option.
5. Click "OK" to close the dialog box.

To toggle the data series on and off, you can use a checkbox or a button linked to a macro that hides or shows the data series. Here's how to create a checkbox:

1. Go to the "Developer" tab and click on "Insert" in the "Controls" group.
2. Select the checkbox control from the list.
3. Draw the checkbox on the worksheet near the chart.
4. Right-click on the checkbox and select "Format Control" from the context menu.
5. In the "Format Control" dialog box, go to the "Control" tab.
6. In the "Cell link" field, enter a cell reference where the checkbox state will be stored (e.g. A1).
7. Click "OK" to close the dialog box.

Now, when you click on the checkbox, the state will be stored in the cell you specified. You can use this cell in a formula to control the visibility of the data series. Here's an example formula:

This formula checks the state of the checkbox in cell A1. If it's true, it returns the data range for the data series (B2:B6 in this example). If it's false, it returns the #N/A error, which will hide the data series.

To use this formula, select the data series and go to the "Select Data" dialog box. In the "Edit Series" dialog box, enter the formula in the "Series values" field, replacing the data range with the formula. Click "OK" to close the dialog boxes.

Now, when you click on the checkbox, the data series will be hidden or shown depending on the state of the checkbox.

Hide data series in chart - toggle on and off
 

One of the examples in the page below shows how to show and hide chart
series using checkboxes:

http://peltiertech.com/Excel/Charts/ChartByControl.html

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


"stef" wrote in message
...
Excel 2002 SP3
Win XP HE

*Follow-up to: microsoft.public.excel.charting*

Hi,
I have a chart with 3 series. 2 are on right axis (secondary) and 1 on
left (primary).
Is it possible for me to toggle on or off 1 of the series?
I cannot just hide it by choosing "none" as plot data as it still plots it
even if not visible.
I am really trying to fix scaling problems....
Thanks.

Hide data series in chart - toggle on and off
 

Thanks!

Jon Peltier wrote:
One of the examples in the page below shows how to show and hide chart
series using checkboxes:

http://peltiertech.com/Excel/Charts/ChartByControl.html

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


"stef" wrote in message
...