Two Pivot Table questions today from my 'Power Excel' Seminar in Detroit, MI yesterday. First, when you drill down on a pivot table in Excel 2007, you are getting the Table Format instead of just a Range. How can I just get the Range? Second, when you convert a Pivot Table to Values, you lose the Formatting and the Formulas. How do I mitigate this when trying to get back to the Data Field? Follow Bill in Episode #1456 to learn the solutions.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1456: Pivot to Range.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I was up in Michigan yesterday doing a seminar for the Detroit Chapter, the IIA.
I got a lot of great questions there.
I'm going to cover two of those questions.
First, we have a pivot table here.
If you drill down-- if you choose one cell and drill down, you know, you get the new worksheet with all of the reason-- the records that make up that cell, but starting in Excel 2007, they automatically make that into a table format.
The question is how do we stop it from doing that?
Well, we can't stop it from doing that, but it is just a couple of clicks to go back to a regular table.
First of all, get rid of the formatting, if you don't like the formatting, and then right here, convert to range.
Do you want to convert the table to a normal range?
Well, yes.
That's why I clicked the icon.
So, then you get just a regular spreadsheet.
Kind of a hassle as you have to do that every single time, but you have to do that every single time.
Then, the next question was, okay, I have a pivot table and I’d like to unpivot that pivot table.
In other words, convert that pivot table to a range, but-- well, I said, okay, easy.
Select the whole thing, copy, and then paste those values.
She said, ”But then I lose the format.” I said, all right, easy enough to get the formatting back.
Let's do Ctrl T for a table and then we can go back here to the quick styles and choose one of the settings.
Also, you then have banded rows and so on just like you would be able to do in a regular pivot table.
All right, so that was good, but then she came back and she said, “Wait a second.
Wait a second.
It's not the formatting I'm upset about.
It's the fact that I'm losing these formulas.
These formulas go away when I convert it back to a range.” I said, okay, well, then what we're going to have to do is we're going to come here to the-- actually we're going to just take the east, central, and west and make that not have subtotals anymore, all right?
So, here's how we do that.
Under the options tab, active field, field settings, subtotals, none, click OK.
Of course, it also makes a lot of sense to go back here: the design, tab, report layout, and say show in tabular form so that way we get those filled in.
Also, this is Excel 2010.
Only repeat all item labels to fill in that data.
Grand total, off for rows and columns.
So, now we just have what I call a flat pivot table.
Now, that we've done that, let's convert that to value.
So, copy, paste, paste as values and then you simply have to do data, subtotals, at each change in region use the SUM function for these fields, click OK and you get back to the formulas.
So whether it's the formatting or the formulas that you want to keep, there are ways to get back to that.
Okay, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1456: Pivot to Range.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I was up in Michigan yesterday doing a seminar for the Detroit Chapter, the IIA.
I got a lot of great questions there.
I'm going to cover two of those questions.
First, we have a pivot table here.
If you drill down-- if you choose one cell and drill down, you know, you get the new worksheet with all of the reason-- the records that make up that cell, but starting in Excel 2007, they automatically make that into a table format.
The question is how do we stop it from doing that?
Well, we can't stop it from doing that, but it is just a couple of clicks to go back to a regular table.
First of all, get rid of the formatting, if you don't like the formatting, and then right here, convert to range.
Do you want to convert the table to a normal range?
Well, yes.
That's why I clicked the icon.
So, then you get just a regular spreadsheet.
Kind of a hassle as you have to do that every single time, but you have to do that every single time.
Then, the next question was, okay, I have a pivot table and I’d like to unpivot that pivot table.
In other words, convert that pivot table to a range, but-- well, I said, okay, easy.
Select the whole thing, copy, and then paste those values.
She said, ”But then I lose the format.” I said, all right, easy enough to get the formatting back.
Let's do Ctrl T for a table and then we can go back here to the quick styles and choose one of the settings.
Also, you then have banded rows and so on just like you would be able to do in a regular pivot table.
All right, so that was good, but then she came back and she said, “Wait a second.
Wait a second.
It's not the formatting I'm upset about.
It's the fact that I'm losing these formulas.
These formulas go away when I convert it back to a range.” I said, okay, well, then what we're going to have to do is we're going to come here to the-- actually we're going to just take the east, central, and west and make that not have subtotals anymore, all right?
So, here's how we do that.
Under the options tab, active field, field settings, subtotals, none, click OK.
Of course, it also makes a lot of sense to go back here: the design, tab, report layout, and say show in tabular form so that way we get those filled in.
Also, this is Excel 2010.
Only repeat all item labels to fill in that data.
Grand total, off for rows and columns.
So, now we just have what I call a flat pivot table.
Now, that we've done that, let's convert that to value.
So, copy, paste, paste as values and then you simply have to do data, subtotals, at each change in region use the SUM function for these fields, click OK and you get back to the formulas.
So whether it's the formatting or the formulas that you want to keep, there are ways to get back to that.
Okay, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
