Robert Mundigl’s Clearly and Simply blog is full of interesting articles. I recommend everyone to read it!
I am continuously looking for new challenges – now I’m focusing on this article:
Emulate Excel Pivot Tables with Texts in the Value Area using Formulas
Robert sees the limitations of this approach ... Here are the Cons as he collected it:
Besides the fact that it takes a lot of preparation work to set it up and the complex array formulas, the static layout of the crosstab is the main disadvantage of the formula based approach. You have to define in advance the size of the crosstab and this leads to a couple of undesired effects:
In a nutshell: the formula approach works, but from my point of view it is way too static.
Why I've striked through almost all of the text?
In my file (you can find at the bottom of this page) you can find the work of Robert taken to the extreme:
On Pivot_setting sheet you can:
On top of that you can choose from the relevant fields only – a special validation is set on the cells which makes impossible to choose the same data field for more than one Pivot table field. It was just a nice “challenge in the challenge”.
The structure is dynamic in its entirety ... You can add columns and rows ... certainly not forever ... the model is completely dynamic with regard to the limitations of using formulas only.
But I agree with Robert saying that:
"…it takes a lot of preparation work to set it up …"
If I really needed that kind of a pivot I would definitely choose the model with vba of Robert:
Emulate Excel Pivot Tables with Texts in the Value Area using VBA by Robert Mundigl
Thanks to Kris for her help in the translation.