Hey welcome back to the Mr. Excel net cast. I’m Bill Jelen. Basically, we start out with massive amounts of data as to how we’re going to analyze as well. Let’s fire up a pivot table if you can solve this problem.
Hey welcome back to another Mr. Excel Net Cast. I’m worn out. I have been spending the last hour trying to get this pivot table to do what my manager wants me to do and I think I’m pretty smart, I mean I got myself Mr. Excel. I mean is it able to change these headings from sum of revenue to revenue space and put it in the percentage of the total. I tried to choose some formats here to make it look good. But you know what, these percentages of the total are like there’s no way to get this 204 as a percentage of the 599. Just cannot happen, it’s always the 204s of the percentage of the grand total and I’m just so frustrated with this.
Okay, but let’s use that cool Microsoft trick that we’ve been talking about the last couple of days that get pivot data. So inserted a new sheet and I created a nicely formatted report how I want it to framed. Look at this, the word E is repeated all the way down its center repeated all the way down and west repeated all the way down. And I even put in my percentage of region calculation which the pivot table can’t seem to do.
Now how are we going to make this come to life I’m going to take the equal sign go back and point into my pivot table. This is A, B, C, E so I go to A, B, C, E there’s my number right there and that’s going to build the get pivot data formula for me but now I want to go through and parameterize this. So word says region east, I’m going to say that I always want to grab that from a $86 and the word says product A, B, C. I’m going to parameterize that and say that’s always going to come from $B6 right, good and so now I can copy that down to look how it works and then I want to copy it over here where I had created a field called percentage of total.
So percentage total is the revenue, it doesn’t like that it must have been percentage of total without a space there we go, right. Beautiful, now I wonder if instead of literally typing the value in there if I get point to a cell wouldn’t that be cool. Of course, you know I didn’t do it that way because it doesn’t work so undo. You actually have to type the name in there. Well, alright we can live with that. I will format that nicely and of course we can copy that down.
And I also had a field called GP%. Let’s all come back here and change the percentage of total to GP%. Alright get those GP%, now that we have that whole table working we can copy it paste it, paste it and we are ready to role. Alright so now what at are the advantages. Well, I am using a pivot table here so as I change the underlying data I can come back to my pivot table. I can refresh and the pivot table will be refreshed but then the actual printer report is going to come from this page and the next thing here people will say well you know from to month my pivot table loses his formatting or all kinds of bad things I get.
Well, this isn’t going to lose this format because its jus straight excel, alright b we get to take advantage of get pivot data to reach in to the pivot table and grab those values so it’s really kind of the best of worlds we’re using all of the power of the pivot table but we’re not stuck with EOS stupid limitations that they give us. You know back here in the pivot table, if I wanted to insert a column between C&D no deal, can’t do it. Not allowed and if I cannot hear outside of the pivot table insert column. Cannot move a part of a pivot table. I get it okay, but over here in my report, yeah. Sure you want to call him here, no problem.
She’s put a tiny column in it, it doesn’t matter because it’s just Excel. You know and so I added a field up here for a quarter one and then build a second report here where I’m grabbing that parameter from B1 so you know we can change this to be a quarter two or quarter three and now very quickly get a brand new report. So lot’s of very, very cool things I’m starting to see why the folks at Microsoft had been using get pivot data you know and I think for most of the rest of the world we hate it.
We wish you would get turned off but once you start to understand how it works I can see a lot of the power of get pivot data. Now, one thing to be aware of here is that the value that you are trying to get must be visible in the pivot table. So if I get back to my pivot table here, I can imagine where we would want the A, B, C total without regions and since number is not visible on the pivot table it’s not going to be available to us in get pivot data. It seems to be a bit of annoying limitation.
So that’s actually two days ago when, I started this whole conversation I could have put different wares, put everything in and hopes then that any number I could possibly need get pivot data will be there. Alright so there you have it end of three days talking about get pivot data started out with hate get pivot data understanding get pivot data and now I start to see where there might be some nice uses for get pivot data a pretty interesting functions. Well thank you for stopping by we’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services