Doorgaan naar hoofdcontent


Posts uit maart, 2015 weergeven

Excel: Hidden Rows in Charts

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:


Without the table the formula would have looked like this:


The formula SUBTOTAL(103;[@Apples]) performs a COUNTA without …

Excel: Which Data In Your Graph? Take your Pick: Combine Month, Moving Average, Maximum or Minimum

What to show? Thirty six charts or just one dynamic one?

I created I chart which shows the choice of two parameters: cumulatives AVERAGE, MIN or MAX and months JAN through DEC. So, in this case we have thirty six sets of data we can choose from. Take your pick!

I needed just one complex name:

MONTH: =OFFSET(temp1!$B$2;0;MATCH(temp!$Q$2;monthnames;0)-1;temp!XER1-1705;1)

The rest you can easily figure out yourself.
You can download ExcelMoving.xlsx via: