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:
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.
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:
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:
Reacties