99% of the people in my seminars hate GetPivotData. Most people outside of Microsoft hate GetPivotData. Today, in Episode 1126, as we've done before, we will take a look at how to turn off GetPivotData.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
OK, last week we talked about VLOOKUPs, this week I'm going to spend a couple of days talking about this next topic.
And I was having lunch with someone from Microsoft last week, and we were talking about Pivot tables.
Of course, what else would you talk about when you're having lunch with someone for Microsoft?
And, you know, he said “Internally at Microsoft, they see a lot of people using GETPIVOTDATA reports.” And I said “Well wait a second!
You know, I see people from around the country all the time, I'm doing Excel seminars, and I've done these seminars for thousands and thousands of people.
All the time I get the question ‘How do you turn off GETPIVOTDATA?’ I've only had one person who actually had embraced it, who said that's a cool feature.” I said “So the people at Microsoft are using this all the time?” he says “Yeah, you know, it's funny, we see the same thing.
When we're outside of Microsoft, the only question we get is ‘How do we turn off that stupid feature?’ But inside of Microsoft, people are using it all the time.” So I asked them to tell me more about that, let's just, first of all, talk about GETPIVOTDATA, and why it's annoying for 99.9% of the people outside of Microsoft.
So, you know, we create a Pivot table, insert PivotTable, click OK, and yeah, I'm just going to build an ugly Pivot table here.
I'm going to choose a bunch of different fields for Region, Product, Dates, Customer, what the heck, let's just include everything.
I mean, we're going to go to the Date field and group that up, so I have Months, Quarters, and Years.
So, you know, I just have this big ugly horrible Pivot table, and even if I try and format it, it's never going to format.
You know, no one's going to give this to their manager or, you know, use this in a presentation, it's just ugly, alright, just flat-out ugly, but that's not what we're talking about today, we're talking about GETPIVOTDATA.
So, you come outside of the Pivot table, and you build a formula here, equal this times 2, and OK, so we have 5082, I copy that down, and that formula doesn't work.
And when we go back and see what's going on, we see that they did something here called GETPIVOTDATA.
Now this started back in Excel 2002, any time you use the mouse or the arrow keys to build a formula, they're going to insert GETPIVOTDATA.
Luckily, in 2007 they gave us a way to turn it off.
Go back to the Pivot table, to the PivotTable Options, and then here don't click the Options button, click the Options drop-down, and turn off that Generate GetPivotData.
So we have a way to turn it off, alright, and that's the question I get all the time: “How do I turn that stupid feature off?” So that's for today, now tomorrow we're going to come back in, and understand how the folks at Microsoft leverage GETPIVOTDATA, and it's actually a very, very cool idea.
So thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
OK, last week we talked about VLOOKUPs, this week I'm going to spend a couple of days talking about this next topic.
And I was having lunch with someone from Microsoft last week, and we were talking about Pivot tables.
Of course, what else would you talk about when you're having lunch with someone for Microsoft?
And, you know, he said “Internally at Microsoft, they see a lot of people using GETPIVOTDATA reports.” And I said “Well wait a second!
You know, I see people from around the country all the time, I'm doing Excel seminars, and I've done these seminars for thousands and thousands of people.
All the time I get the question ‘How do you turn off GETPIVOTDATA?’ I've only had one person who actually had embraced it, who said that's a cool feature.” I said “So the people at Microsoft are using this all the time?” he says “Yeah, you know, it's funny, we see the same thing.
When we're outside of Microsoft, the only question we get is ‘How do we turn off that stupid feature?’ But inside of Microsoft, people are using it all the time.” So I asked them to tell me more about that, let's just, first of all, talk about GETPIVOTDATA, and why it's annoying for 99.9% of the people outside of Microsoft.
So, you know, we create a Pivot table, insert PivotTable, click OK, and yeah, I'm just going to build an ugly Pivot table here.
I'm going to choose a bunch of different fields for Region, Product, Dates, Customer, what the heck, let's just include everything.
I mean, we're going to go to the Date field and group that up, so I have Months, Quarters, and Years.
So, you know, I just have this big ugly horrible Pivot table, and even if I try and format it, it's never going to format.
You know, no one's going to give this to their manager or, you know, use this in a presentation, it's just ugly, alright, just flat-out ugly, but that's not what we're talking about today, we're talking about GETPIVOTDATA.
So, you come outside of the Pivot table, and you build a formula here, equal this times 2, and OK, so we have 5082, I copy that down, and that formula doesn't work.
And when we go back and see what's going on, we see that they did something here called GETPIVOTDATA.
Now this started back in Excel 2002, any time you use the mouse or the arrow keys to build a formula, they're going to insert GETPIVOTDATA.
Luckily, in 2007 they gave us a way to turn it off.
Go back to the Pivot table, to the PivotTable Options, and then here don't click the Options button, click the Options drop-down, and turn off that Generate GetPivotData.
So we have a way to turn it off, alright, and that's the question I get all the time: “How do I turn that stupid feature off?” So that's for today, now tomorrow we're going to come back in, and understand how the folks at Microsoft leverage GETPIVOTDATA, and it's actually a very, very cool idea.
So thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
