I was struggling with a simple column graph. I was just wondering if there is a formula in Excel which can indicate whether a row is hidden or not. Without using VBA of course.
Here are my data:
What I wanted to achieve is a graph like this which does not show the rows I hid, in this example the years 2005-2008, but where I still can see the X-axis values.
In this chart I checked the box Show data in hidden rows and columns
Otherwise I would not get the years 2005-2008 on the X-axis.
So I needed a function to check wether a row is hidden or not in order to manipulate the data in column B:B. Here is my solution.
I turned my data list into a table. Then I added a third column with this formula:
=IF(SUBTOTAL(103;[@Apples])=0;NA();[@Apples])
Without the table the formula would have looked like this:
=IF(SUBTOTAL(103;B2)=0;NA();B2)
The formula SUBTOTAL(103;[@Apples]) performs a COUNTA without the hidden rows. So in this case, it either returns a 0 or a 1, depending on whether the row is hidden or not.
After that i created simple dynamic names Apples:
So, now I get the result I shows in my example graph. Actually it does not matter whether you hide rows by hand or by using the data filter.
You can download ExcelGrafiekSubtotaal.xlsx via:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Here are my data:
| Year | Apples |
| 2001 | 10 |
| 2002 | 11 |
| 2003 | 12 |
| 2004 | 13 |
| 2005 | 14 |
| 2006 | 15 |
| 2007 | 16 |
| 2008 | 17 |
| 2009 | 18 |
| 2010 | 19 |
| 2011 | 20 |
| 2012 | 21 |
| 2013 | 22 |
| 2014 | 23 |
| 2015 | 24 |
What I wanted to achieve is a graph like this which does not show the rows I hid, in this example the years 2005-2008, but where I still can see the X-axis values.
In this chart I checked the box Show data in hidden rows and columns
Otherwise I would not get the years 2005-2008 on the X-axis.
So I needed a function to check wether a row is hidden or not in order to manipulate the data in column B:B. Here is my solution.
I turned my data list into a table. Then I added a third column with this formula:
=IF(SUBTOTAL(103;[@Apples])=0;NA();[@Apples])
Without the table the formula would have looked like this:
=IF(SUBTOTAL(103;B2)=0;NA();B2)
The formula SUBTOTAL(103;[@Apples]) performs a COUNTA without the hidden rows. So in this case, it either returns a 0 or a 1, depending on whether the row is hidden or not.
After that i created simple dynamic names Apples:
| =OFFSET(Blad1!$C$2;0;0;COUNTA(Blad1!$C:$C)-1;1) and Years:
|
So, now I get the result I shows in my example graph. Actually it does not matter whether you hide rows by hand or by using the data filter.
You can download ExcelGrafiekSubtotaal.xlsx via:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Reacties