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:
Without the table the formula would have looked like this:
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: