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:
GETPIVOTDATA!
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 |
| So bad |
| 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. |
| can’t copy |
| 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:
- Slow
- Wrong default (sorted) mostly gives wrong answer
- Column number not understandable and breaks when columns inserted
- Inflexible
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 |
INDEX-MATCH
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.
SUMPRODUCT
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?
Excel and UDF Performance Stuff 
The basis for improving GETPIVOTDATA is already there: Table syntax.
Let GETPIVOTDATA use a syntax that is similar to Table syntax, including the @ to signify the current row, so that when you copy your formula down, it adjusts. All you would need is an additional signifier for the field value. Something like PivotTable1[Year].2016
Totaly agree!
Hi Charles
Because a lot of people found difficulty with GetPivotData, I wrote a tutorial showing how you can parameterize the arguments and have an almost universal GPD function which will work if you then set up the parameters.
The tutorial can be found here
http://www.contextures.com/excelgetpivotdatareports.html
I wholeheartedly agree that it would be nice if Microsoft would address this issue and give us a newer, better function, but I don’t see it happening somehow, as, with PT’s created from the Power Pivot Data model, the GETCUBEMEMBER function is the preferred option.
The official Excel’s Suggestion Box has some suggestions regarding GETPIVOTDATA.
https://excel.uservoice.com/search?query=GETPIVOTDATA
Take a look. Maybe some problems and suggestions were voiced and answered. I know from previous experience that the Excel team listens to the suggestions posted there.
Well I love GETPIVOTDATA. But yes, it could do with some pimping. The old default behavior of having Excel produce them automatically is to blame for a lot of the hate out there. But most of the hate expressed above is unjustified in my opinion.
Sure, I guess it could be made faster, but I don’t really think that matters too much in the grand scheme of most workbooks because it’s not a function that you’re likely to use thousands of instances off.
As for the fact that it automatically uses absolute references…well, that’s precisely what it’s designed to do. But both are easily remedied when you know how.
Sure, it could be considered hard to use when manipulating PivotTables. But it’s a hell of a lot easier to use than any other function when manipulating PivotTables, because it is in fact robust to manipulations where you are rearranging the order or layout of fields without changing the actual fields displayed. And that is pure magic. So it is dynamic.
Sure, you have to replace the hard-coded field names with relative cell addresses if you want to copy it down. But that’s by intent. Not by bad design. And it’s not hard to replace the fields that you want to be dynamic with relative cell references, while leaving the ones that you don’t want to be dynamic as is. The referencing protocol might be a step above your average VLOOKUP worker, but I’d rather use a GETPIVOTDATA function pointed at a summarising PivotTable to dynamically retrieve whatever I feed it via a dropdown than some god-awful two way (or three way or four way) VLOOKUP. Compared to the complexity of the corresponding mega-formula combo that you would need to use, GETPIVOTDATA is actually a hell of a lot less weird or complicated or inflexible. Although I wish you could reference the PivotTable by object name same as you can do with Tables i.e. I wish PivotTables had some kind of structured referencing like my VBA-driven approach at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/
I think this comment sums up the main problem with GETPIVOTDATA:
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.
…and this comment is a classic case of a user not really understanding just how powerful GETPIVOTDATA (and the PivotTables they reference) is:
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.
…because GETPIVOTDATA pointed at some Data Validaiton dropdowns allows you build incredibly simple and dynamic summary fields for dashboards in one relatively inexpensive formula call, without having to show your entire PivotTable at all on that dashboard. That kind of power and flexibility would surely even have the acid-tongued Stephen Few whooping out loud at the ease at which he can build data-dense dashboards thanks to this function.
But amen to your suggestion of having it work directly from the pivot cache…having to have all your fields visible is a pain in the ass. And the fact that the Trace Precedents feature doesn’t point you back to the underlying PivotTable is another pain that MS should really remedy. Spreadsheet integrity and auditing are important. This lack of formula auditing matters.
I learned VLOOKUP several years ago and did get that WOW! factor. It is a great tool.
I use getpivotdata a lot. I am often hampered by data feeds that are outside of my control and a need to meet analysis requirements that are also out of my control. The simplest way to fix this is to create a series of pivots and make my output via getpivot. I disagree with Jeff thought.. I have lots of spreadsheets that use getpivotdata hundreds of times.. the one open in front of me is using it over 400 hundred times on a single sheet and randomly elsewhere. If you set your pivots up right to begin with and use index / match to create filters and grouping lists so that additional data doesn’t change the structure of the pivot you can build a robust solution. My reporting time has reduced from a day a week to an hour a week since I figured out how to do this stuff…
@ trueinsightconsulting
You had the true answer here – Tables and tables syntax – Did the rest of the guys get it?