MrExcel's Learn Excel #870 - GetPivotData

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 6, 2009.
You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, yesterday we had a look at Richard's Pivot Table, and there's another solution to this: It's called GETPIVOTDATA.
Now, most of us hate GETPIVOTDATA because Microsoft forced it upon us, and no one understands exactly how it works; but it does solve this particular problem.
So, here I'm going to enter this formula, =G5-- and I typed G5, I didn't click on it-- and then divided by-- and I'm going to click here in Cell G9 and it generates a formula for us called GETPIVOTDATDA("Reason",$A$3).
Well, the $A$3, that's just pointing to a cell within the Pivot Table, and Reason is saying that that is adding up all of the Reason counts.
Now, let's just click somewhere else here, and we'll see how this is working.
If I would click on Branch 31 Grand Total, then it says, "Hey, we're going to take the Reason measure from the Pivot Table and A3 only, where Branch = 31.
And if I would click up here, Branch 31 and Reason C, you'll see that the GETPIVOTDATA says we want the Reason measure from the Pivot Table in A3, Branch 31, Reason C. So, you can imagine that it adds additional pairs of criteria.
But if, in fact, if we just really want this Grand Total, we could simply use GETPIVOTDATA("Reason"A3), Enter that, copy that down, and you'll see that as we change to a different quarter-- which may have more or less reasons-- it continues to always return 35 as the denominator.
The GETPIVOTDATA-- very flexible function, although most of us never take the time to learn what it's doing because Excel just starts turning it on by default.
It also solves Richard's problem in this case.
Now, what about getting rid of the extra zeros at the end?
I'm going to edit this formula and say =IF(ISBLANK(G5), then I want "" otherwise I want my formula, and put a closing parenthesis at the end to close the IF function.
Copy that down, and what we should see is, now, all of the cells where there is no data, will appear blank.
The formula is still there-- you can see in the formula bar as I go down there-- but as we update each item in the Pivot Table, it's only going to show the items where the corresponding value in Column G is not blank.
So there, we have it-- a better way, perhaps, to solve yesterday's problem, and then also modification of using =IF(ISBLANK) to make sure that the items with no data appear not to have a formula.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
 

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top