Tweet

Hide / show series in a Chart Excel

Example of hide/show series

What I mean?

Translate from czech Zobraz datovou řadu graf.

Chapter Article

The article is divided into

• Source data
• Solution 1 - use check box
• Solution 2 - selecting from the list
• Download free

Knowledge

I suppose you know use in Excel:

• Using logical function IF
• Controls Forms
• Inserting line charts bar chart ...

Source data

We will use the following data

Description	Quality	Cost	Speed
Product 1	2	3	7
Product 2	3	5	8
Product 3	5	7	9
Product 4	4	6	9
Product 5	4	8	9

Source data

File source data for chart for free download.

Solution 1 - use check box

Activities we can divide:

• Edit the source table
• Loading check box
• Create Data for graph
• Insert chart
• Design modifications

Edit the source table

We modify the source table. For three data series, put three more columns, the data will be used for creating chart.

Loading check box

check box connection with the cell C4, etc.

Inspection - check TRUE, FALSE unchecking.

Create Data for chart

We can use two versions:

1. version

=IF($C$4=FALSE,-1,F6)

2. version

=IF($E$4=FALSE,NA(),H6)

Insert chart

The result may look like:

Design modifications

In preparation.

Solution 2 - selecting from the list

Activities we can divide:

• Insert control form: List
• Create Data for chart
• Insert chart
• Design modifications

Insert control form: List

From Ribbon Developer insert Combo whith list. Fill titles (Quality, Speed, Price) and connect with the cell L7.

Create Data for chart

In this sample workbook to the three data series, we will add a fourth series (for highlight). We using IF function, This function help copying data from the selected column.

=KDYŽ($L$7=1;C10;KDYŽ($L$7=2;D10;E10))

Insert chart

Insert chart whith fourth axis. The selected data series (line) is in the chart double. Highlighted curve has priority display.

Design modifications

In preparation.

Download free

• File Hide / show series in a chart Excel 1 check box (version 1) - free download
• File Hide / show series in a chart Excel 2 check box (version 2) - free download
• File Hide / show series in a chart Excel 3 select from list - free download

Finally

Do you have other interesting solutions? You can note in comments.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.