dinsdag 19 november 2013

Excel: Switching Series On and Off in a Chart

In Excel series in a hidden column are standardly not shown in a graph. This we can use to play around with series. To this worksheet I added four checkboxes with which you can switch series for the graph on and off, as you like.


Here are the data from A1:E11:

Year Apples Pears Cherries Bananas
2001  €      169  €     213  €     376  €     103
2002  €      777  €       58  €     959  €       22
2003  €      797  €     971  €     837  €     841
2004  €      574  €     241  €     368  €     620
2005  €      133  €     980  €     366  €     293
2006  €      769  €     749  €     212  €     204
2007  €      579  €       69  €     421  €       16
2008  €      223  €     851  €     200  €     772
2009  €      593  €     731  €     843  €       22
2010  €      183  €     723  €     419  €       24

I added four checkboxes which are linked to H2:H5 respectively.

I created four names:

Apples =IF(Blad1!$H$2=TRUE;Blad1!$B$2:$B$11;Blad1!$H$1)
Bananas =IF(Blad1!$H$5=TRUE;Blad1!$E$2:$E$11;Blad1!$H$1)
Cherries =IF(Blad1!$H$4=TRUE;Blad1!$D$2:$D$11;Blad1!$H$1)
Pears =IF(Blad1!$H$3=TRUE;Blad1!$C$2:$C$11;Blad1!$H$1)

As you can see, the names refer to a column or H1 based on the outcome of the checkbox.

Then I hid column H.

When you now create a chart based on the names above, you can switch columns on and off.

You can download the file ColumnsOnOff.zip through this link:

Een reactie plaatsen