-Posted by Rob Collie
OK, ignore my last post, that was just silly.
Yesterday one of my fine colleagues at Pivtostream mentioned that he was seeing both field lists simultaneously. Then followed an exchange where I said “BS,” he said “bet me some cash, oh PowerPivot boy” and, well, he was right.
One should be careful never to wager against one’s own boss.
Anyway, in beta releases of the addin, it was IMPOSSIBLE to bring up the Excel field list without disabling the addin. Apparently, the team decided at the last minute that allowing the user to bring up the Excel field list could compensate for the lack of certain features.
It’s a brilliant move on their part. So here it is, both field lists, living together in harmony:
You don’t have to disable anything. Just go to the Options tab on the ribbon when you have a pivot selected, and click the field list button there:
OK, so… you can do the defer layout update trick which is excellent for long-running queries, without turning off the addin, that’s huge.
But wait, there’s more 🙂
You can also adjust the layout of your measures. Try dragging the little Sigma “Values” capsule around the Excel field list (shown here at right) and you will see what I mean – move it to Row Labels for instance.
This also comes in handy when you have other fields on Column Labels and want to change whether Measures are nested inside of those fields or vice versa.
But the last trick that this side by side field list stuff enables is truly my favorite…
Batch Creation of Measures Without Refresh!
Follow along, this is fantastic.
Step one: turn on defer layout update:
Two, go create a measure:
Three, ignore this error you get when you enter the formula and click OK:
Four, observe that the measure was created anyway:
This changes my life in rather significant ways.