User‎ > ‎

Emulate Excel Pivot Tables with Texts in the Value Area using Formulas (by Robert Mundigl) - Taken to more extreme

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:
[...]
The Cons

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:
  • As already described above, the number of rows has to be static. You will always either see some empty rows or – even worse – the number of rows is too small and some risks will not be displayed. For me, this is the main disadvantage.
  • The row and column headers always include all entries of those categories. E.g. if you do not have any risks in your data with a probability of “high”, the crosstab will still include this column. A real Pivot Table would not show this empty column.
  • There is no easy way of changing the layout of the crosstab. If you want to e.g. swap rows and columns, you have to change the whole layout and the formulas.
  • If you add new risks with new category entries (e.g. you add a new type of impact) you have to change the layout of the crosstab, to copy formulas and to change the worksheet [Control].
  • The height of the rows will not be adapted automatically. In other words you have to change the row height manually each time you selected another filter.

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:
  • choose data fields for row and column header
  • choose which data field you want to see in the data part
  • choose which data field you want to use as pager
…as in a common Pivot table.
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

Best regard

r

Thanks to Kris for her help in the translation.

Ĉ
roberto mensa,
Nov 11, 2011, 2:35 PM
Comments