If you can not get your pivot table formatting correct while the report is a real pivot table, this video has a great solution for you. Build a shell report to hold the formatted results.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Pivot Table of Data Crunching: Chapter 14, GetPivot Data.
You know, as you may know, I travel around the country doing Excel seminars and you know, I always talk about Pivot Tables during those Excel seminars and one of the questions, there's two questions I get, one, why can't Pivot Tables be formatted better, why can't we teach it a template of how we want it formatted.
So, we won't recreate the Pivot Table or update the Pivot Table month after, month after, month it, it looks right and then the second question, we get is what the heck is GetPivot Data? Why is it so evil and how do I turn it off?
All right, I never or very rare to get anyone who says, hey, you GetPivot Data is the greatest thing ever, but it turns out that, those two questions are really related and the solutions to getting a pivot table that's formatted involves GetPivot Data.
So, I'm gonna go take a look here at Sheet 6, where I've created the world's ugliest Pivot Table.
This just has all kinds of data that I'm never going to need it's very ugly you never be able to print this out and have this look correct, but then I'm gonna come here where I've created not a Pivot Table, but just a regular old worksheet and this worksheet has been formatted to show exactly how I want the data.
So, I have extra blank rows here, I have division totals, here I have a date stored at 7/31 and I formatted that date to have words before it and then little formulas to figure out if we are before that date or after that date to get either Actuals or Budget.
All right and so, what I'm going to do let's just remember this one cell here, this cell Northeast January Actuals.
We're going to go find that cell in the Pivot Table.
So, Northeast January Actuals, perfect it's right there.
So, that's the cell I'm interested in, we need to make sure under Pivot Table, Options, that we have generate get pivot data turned on.
Now, not the options button, but the Options drop down, make sure that that's turned on, you might have turned it off, it's very common because most people hate generate GetPivot Data.
So, we'll come here to our report we're going to type an equal sign and then go to our Pivot Table choose, was it January, was it Northeast, Actuals, click Enter, perfect.
All right and now, we actually have to confront this formula and figure out what's going on with our formula.
So, let's take a look at the formula, we get here the problem would GetPivot Data and the reason is that it just is, so unfriendly is it they have hard coded a lot of stuff.
They've hard-coded that the region is Northeast, they have hard coded that the month is, month one that the measure is Actuals that the years is 2011 and that's why it doesn't work.
So, what you have to do after creating this first, GetPivot Data is you want to replace that hard coded Northeast, with a cell reference.
So, instead of having Northeast there, we want cell A6 and by the way I'm going to press F4 one, two, three times to lock it down to column A, for the measure I don't want to hard code that to Actuals, I want to use the word that's up here in cell B5 and I'll press F4, one, two times to lock that down to row 5 for years, for years we're going to be hard coded.
Actually, listen we can leave that hard coded to 2011 or we can ask for the year of cell A2, press F4 once to lock that down.
Now, for the month we're going to use the month of these values here that Jan, Feb, Mar is actually a date that's been formatted to show Jan, Feb, Mar.
So, here where we have date of 1. I going to ask for the month of this particular cell again we'll press F4, one, two, times to lock it down to the row.
All right! So, we'll press Enter, we should get the same answer 277435, the benefit now though is that is a formula that can be copied, with Paste Special, Formulas and you see the numbers automatically update.
All right! so now, we have almost the perfect situation we have let's make our formula bar small again we have a report here that is formatted perfectly and that report is getting its data from a Pivot Table.
So, every month we paste new data at the bottom of the raw data we come to Pivot Table, Options, we refresh the pivot table and then go to our report which will now be nicely formatted and have data coming from the Pivot Table.
So, this is I call this the new, I call this the new Pivot Table model, where we're not actually ever printing the pivot table, but we're printing a nicely formatted report that uses get pivot data to get the right numbers from our Pivot Table.
So, I think a very different way than most people go.
I used to create my Pivot Table, recreated every month and then change the values and then new formatting you don't have to do that now I just create an ugly Pivot Table, know that no one will ever see it except for me and then use GetPivot Data to populate the good formula...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Pivot Table of Data Crunching: Chapter 14, GetPivot Data.
You know, as you may know, I travel around the country doing Excel seminars and you know, I always talk about Pivot Tables during those Excel seminars and one of the questions, there's two questions I get, one, why can't Pivot Tables be formatted better, why can't we teach it a template of how we want it formatted.
So, we won't recreate the Pivot Table or update the Pivot Table month after, month after, month it, it looks right and then the second question, we get is what the heck is GetPivot Data? Why is it so evil and how do I turn it off?
All right, I never or very rare to get anyone who says, hey, you GetPivot Data is the greatest thing ever, but it turns out that, those two questions are really related and the solutions to getting a pivot table that's formatted involves GetPivot Data.
So, I'm gonna go take a look here at Sheet 6, where I've created the world's ugliest Pivot Table.
This just has all kinds of data that I'm never going to need it's very ugly you never be able to print this out and have this look correct, but then I'm gonna come here where I've created not a Pivot Table, but just a regular old worksheet and this worksheet has been formatted to show exactly how I want the data.
So, I have extra blank rows here, I have division totals, here I have a date stored at 7/31 and I formatted that date to have words before it and then little formulas to figure out if we are before that date or after that date to get either Actuals or Budget.
All right and so, what I'm going to do let's just remember this one cell here, this cell Northeast January Actuals.
We're going to go find that cell in the Pivot Table.
So, Northeast January Actuals, perfect it's right there.
So, that's the cell I'm interested in, we need to make sure under Pivot Table, Options, that we have generate get pivot data turned on.
Now, not the options button, but the Options drop down, make sure that that's turned on, you might have turned it off, it's very common because most people hate generate GetPivot Data.
So, we'll come here to our report we're going to type an equal sign and then go to our Pivot Table choose, was it January, was it Northeast, Actuals, click Enter, perfect.
All right and now, we actually have to confront this formula and figure out what's going on with our formula.
So, let's take a look at the formula, we get here the problem would GetPivot Data and the reason is that it just is, so unfriendly is it they have hard coded a lot of stuff.
They've hard-coded that the region is Northeast, they have hard coded that the month is, month one that the measure is Actuals that the years is 2011 and that's why it doesn't work.
So, what you have to do after creating this first, GetPivot Data is you want to replace that hard coded Northeast, with a cell reference.
So, instead of having Northeast there, we want cell A6 and by the way I'm going to press F4 one, two, three times to lock it down to column A, for the measure I don't want to hard code that to Actuals, I want to use the word that's up here in cell B5 and I'll press F4, one, two times to lock that down to row 5 for years, for years we're going to be hard coded.
Actually, listen we can leave that hard coded to 2011 or we can ask for the year of cell A2, press F4 once to lock that down.
Now, for the month we're going to use the month of these values here that Jan, Feb, Mar is actually a date that's been formatted to show Jan, Feb, Mar.
So, here where we have date of 1. I going to ask for the month of this particular cell again we'll press F4, one, two, times to lock it down to the row.
All right! So, we'll press Enter, we should get the same answer 277435, the benefit now though is that is a formula that can be copied, with Paste Special, Formulas and you see the numbers automatically update.
All right! so now, we have almost the perfect situation we have let's make our formula bar small again we have a report here that is formatted perfectly and that report is getting its data from a Pivot Table.
So, every month we paste new data at the bottom of the raw data we come to Pivot Table, Options, we refresh the pivot table and then go to our report which will now be nicely formatted and have data coming from the Pivot Table.
So, this is I call this the new, I call this the new Pivot Table model, where we're not actually ever printing the pivot table, but we're printing a nicely formatted report that uses get pivot data to get the right numbers from our Pivot Table.
So, I think a very different way than most people go.
I used to create my Pivot Table, recreated every month and then change the values and then new formatting you don't have to do that now I just create an ugly Pivot Table, know that no one will ever see it except for me and then use GetPivot Data to populate the good formula...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
