Excel Mac 2011: How to create a chart from a pivot table's top level data?

[grobbins]

Hi and thank you in advance,


First post. I'm an average Excel user. I'm on Excel:Mac 2011 which still has no pivot charts (as far as I've heard). I've successfully created a regular chart based off the data in a pivot table (which summarizes information in a backend table). Everything works fine with my chart if I keep the pivot table closed/collapsed. As soon as I expand one or all of the pivot table row triangles (to see hidden rows), the range for the source data used by the chart gets thrown off. Re-collapsing fixes it. How do I make the chart smart enough to ignore collapsing/expanding of the pivot table rows?


Experimenting with various fixes, I've tried copying/pasting the individual "top-level" data rows I want charted into the chart while the pivot table is all collapsed--doesn't help. I've tried the same thing with the pivot table first all expanded--no help.


I can imagine basing the chart off a smarter dynamic range that just looks for the top-level pivot table data down column A and grabs things from the right, but how could we make it smart enough to ignore my title, column header, the last "Grand Total" row, etc.? Some fancy dynamic range programming, I'm guessing.


Am I asking for too much? If I need to upload something, please advise.

Thanks for any help.

Garth

 

[keymaster]

Welcome to our forums Garth & thanks for posting your question.


As a first step, I recommend you to spend sometime going thru 3 green threads on forums main page - http://chandoo.org/forums


Also, you may join our newsletter - http://chandoo.org/wp/subscribe/


Coming to your question, I do not have a direct solution only a work around.


Once you create a pivot, why not hide the pivot table sheet completely. For your expansion / collapse and other analysis needs you can create the same pivot once again. The chart can refer to hidden sheet's pivot so no one can mess with its format.

 

[grobbins]

Hi Chandoo!


Thanks so much for your response. I appreciate your work-around and it's been working well.


I also managed to create a working dynamic range that grabs all the top-level (collapsed) data as the pivot table changes dimensions (using "offset" and "counta").


The remaining piece I'm looking to solve now is why I can't just base the chart off the dynamic range. When I plug my range name into the "Chart data range" field at the top of the dialog, it works, but if you close and reopen the dialog, Excel will have converted the dynamic range name to an equivalent/standard range of cells (which will work until the pivot table's dimensions change). In case you know that one too...


Thanks and best regards,

Garth