Some time back I setup a (not too serious) survey (based on Excel online of course) to see what people thought were the best (most heroic) and worst (most evil) Excel worksheet functions.
And the most evil function is:
Well I did not see that coming – the reasons people do not like it are:
|I feel like I have to run my head through a meat grinder to understand the syntax for referencing pivotfields|
|GETPIVOTDATA is evil because the slightest change in a pivottable’s layout breaks the function and it is extremely hard to pin-point what element of the PivotTable the function is getting.|
|It demand text, not cell references. That makes it unuseful.|
|It always breaks, eventually, and more frequently than any other formula. It’s not dynamic whatsoever. It’s unbelievably slow.|
|-Pain in the ass to use- can’t fill down formulas easily
-Hard to use when manupulating pivot tables
-Hard to debug
|I loath this function. You can not disable it. To get a value from a pivot table that is relative you have to manually key it in.
I also dislike indirect, but getpivotdata is the worst.
|It’s just a d@mned nuisance!|
|It has limited use and I am always changing it to a direct reference. Less of an issue in the newer Excels|
|Weird referencing protocol, and formula traceability doesn’t work.|
|Defaults are absolute. Editing for relative references are cumbersome.|
|Too many parameters|
|Might actually be a good function but have not read very much on it to fully understand what it does or how to use it.|
|It’s never what I want it to do and not as smart as I assume it should be..|
|pivot tables are lovely for quick data review, but a pain in the ass for long term analysis set ups. far better to create some combined index fields and filter them as a pseudo pivot for greater control.
IfERROR() is also pretty evil, but in a subtle way, it hides failures in your working so tthey are difficult to debug!
|It always appears when I don’t want it to|
VLOOKUP – the love-hate relationship
The reasons people gave for hating VLOOKUP were:
- Wrong default (sorted) mostly gives wrong answer
- Column number not understandable and breaks when columns inserted
The reasons people loved it were:
|It solves so many data cleansing situations.|
|Because for most people it’s the first function that gives them that ‘wow, Excel is powerful’ moment. It makes them want to learn more. Including better functions like INDEX & MATCH!|
|Because I hate Access and VLOOKUP lets me avoid it! Matching and classifying without all that Access nonsense.|
|Have used it the most. Need to learn how to use Index and Match in its place however.|
|Easy to understand the syntax|
|It lets me get stuff done.|
|Because I use it multiple times a day, and it allows you to re-create relational database functionality within Excel.|
|Easy to use. Sometimes however I use INDEX(MATCH instead of VLOOKUP|
Easily the most loved function is INDEX/MATCH. Although it is more cumbersome to use than VLOOKUP people love it’s flexibility, ability to construct robust solutions and potential for optimisation. Many people start by using VLOOKUP and then migrate to INDEX/MATCH.
If you look at the combined positive votes for VLOOKUP and INDEX/MATCH its probable that the survey people consider LOOKUPs the most important thing in Excel.
I think the reason for the love-hate relationship with SUMPRODUCT is because its being used to fill the hole of a function that does not exist in Excel (I call it FILTER.IFS),=.
It’s power allows it to be used for a purpose it was never designed for, but that same power comes with a significant cost in performance terms.
Could GETPIVOTDATA be improved?
Undoubtedly there is a need for a GetPivotData like function.
If it worked directly from the pivot cache in a multi-threaded way it would be a lot more robust and performant.
It would also need a wizard of some kind to simplify picking the field names.
So how would you improve GETPIVOTDATA?