Hey welcome back to the Mr. Excel Net Cast. I’m Bill Jelen. Yesterday we talked about this annoying get Pivot data and I showed you how to turn it off but I said yesterday that I had lunch with someone from Microsoft and they said that inside Microsoft people use this all the time so let’s figure out what the heck it does. You go get pivot.
Actually, I came out here to Excel help just to remind myself what it does and it says we have specify a data field that is like is a sum of revenue and then specify the pivot table it’s very easy to do that just pick any cell inside the pivot table and then pairs of fields. Field one and the item that we are looking for, field two and the item that we’re looking for so given that let’s come back here and take a look at what’s going on this asking for some of cost and good soul that’s one of our fields in the pivot table.
To identify the pivot table, they just used 83 which is the top left corner so it could be any cell in the pivot table and then pairs of fields so here they are saying hey we have a field called region I want the central. We have a field called product, I want ABC. We have a field called date; I want one that was January. Field called customer IBM quarters one years, 2008 and that gave s the answer well the problem with that is it so hard coated. We would never actually be able to use that for anything that can’t copy or anything but here is the way that people at Microsoft use it. They parameterize that function so equal.
Get pivot data specify any cell out, first of all the field that we’re looking for is less a in quote some of profit and then specify any field. I’m going to choose 83 just like you know press F4 so you want be able to copy this and then we start to do pairs of fields so I put in the word region looking for a region and it’s data type in which region I want. I’m going to point to it using you know G7. I’m going to press F4 one two three times and lock that down with the column and we should see that the central region was 1.3 million copy it down there’s east and there’s west.
Interesting, okay, let’s copy that down to here we’re back to a 1.3 million. I’m going to edit the formula I can have up to a 126 pairs of fields and value so I can ask for, product see the quotes see I’m near this in quotes product. What if I have to capitalize it? I’m going to capitalize it just to be superstitious and then specify that I want this value up here and H12. I’m going to press that for one-two times to lock that down to row x actually row 11 so now we see the central region did 425,000 in ABC. I will copy that throughout all right and now hey look I have a two way look up table.
Alright so you can start to see how the people at Microsoft are using this. What they do remember when I built that pivot table yesterday it was an ugly, ugly pivot table. I just threw every single field in, you know which probably makes the worst looking pivot we’ve ever seen. It doesn’t matter because that is just going to be sitting there as an intermediate result and now I have a great way to without using D sums or sum products or anything like that to get any total or any value that’s in the pivot table.
Tomorrow we’ll take a look at exactly how we’re going to do that but wanted to talk about the basic syntax of get pivot data today and how we get to start to build these. Hey thanks for stopping we’ll see you next time for another net cast for Mr. Excel.
Transcription by:
Scribe4you Transcription Services