Linda asks how to fix some Pivot Table formatting. She would like to get rid of "Sum of" and "Count of" as well as format all of the numeric fields with currency instead of general. In Episode #1347, Bill shows several ways to get the results.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1347: Pivot Formatting.
Today's question comes in from Linda and Linda I hear--this one is just so frustrating.
We're going to create a PivotTable, customers down the side and a few different value fields because we got “Sum of Quantity,” “Sum of Revenue,” “Sum cost of goods sold” and no matter how we formatted the original data it always just appears here as general.
This is driving me crazy.
All right well there's a couple things we can do.
First thing, if I click in “Sum of Quantity” you see that we have the heading right up here; so I'm going to get rid of the word “sum of” in that box, leave the space before Quantity; real important that you do that and so we can kind of get rid of those words “sum of” relatively quickly and officially rename those fields.
All right, so that's solution number one.
Solution number two for the numeric styles.
If we choose Quantity, Field settings, Number Format, Currency, 0, click, click.
Then, can we press F4 to redo the last thing we did?
Of course not; that would have been too easy.
So select Field Settings, Number Format Currency 0, click OK, click OK and then again Field Settings, Number Format, Currency 0.
All right, now this is the right way to do this because if we later take some fields out; take customer out and put region in it will remember that formatting, all right.
So that's the good way to do it but what if you created a PivotTable with dozens of fields?
So Insert PivotTable, OK.
Let's put customer down the left-hand side and just choose a boatload of fields out there.
I have 31 different fields that I could put out there.
Well that is going to be really frustrating to go through and change the numeric formatting on every one.
I have to tell you I'd be tempted, tempted to choose all the cells, Ctrl+1, Number and make all of those be Currency, 0 decimal places right off the bat, just be done with it.
Although if you do that you should go into PivotTable options and right here “Preserve cell formatting on update” turn that on; usually it’s turned off.
This will allow you to add new data to the original data set, update the PivotTable and have the cell formatting appear.
You know one more method I'm going to show you and I think it's really well I don't know I'm not going to call it weak but let's just try it.
Insert PivotTable and click OK.
We'll put customers down the left-hand side, again a whole bunch of fields across the top.
Remember back in Excel 2003 under Format there was something called Auto Format.
Well I'm here in the PivotTable and I'm going to choose my data and I'm going to do Alt+O for Format and A for AutoFormat and sure enough that old dialogue is still there with those old, old, ugly formats but these aren't the ones I want.
These are the PivotTable formats so I'm going to trick Excel.
I'm going to start outside of the PivotTable right there in that blank cell and we'll select everything in the PivotTable but you notice I've started outside the PivotTable, now Alt+O+A.
Aha then I get the real AutoFormats, the old ones and look at that.
There's a few of them, not many; kind of the four accounting formats.
They put dollar signs on the first and last row, click OK.
It shows that it has dollar signs--oh I must have chosen the wrong one, I did.
There we go okay, so yet another method to go.
I don’t know.
it just seems like it's cheating on so many different levels and I made fun of those old Auto Formats back in 2003.
I hate to even bring it up that you can even do it.
So three different ways to go.
One, either use the field settings or; Two format everything and turn on preserve formatting or; Three, this cheating method use format auto format and choose one of the accounting formats; your call how you want to proceed.
Either way.
Well hey, I want to thank Linda for sending that question in and thank you stopping by.
We’ll see you next time for another netcast with MrExcel.
Learn Excel from MrExcel podcast episode 1347: Pivot Formatting.
Today's question comes in from Linda and Linda I hear--this one is just so frustrating.
We're going to create a PivotTable, customers down the side and a few different value fields because we got “Sum of Quantity,” “Sum of Revenue,” “Sum cost of goods sold” and no matter how we formatted the original data it always just appears here as general.
This is driving me crazy.
All right well there's a couple things we can do.
First thing, if I click in “Sum of Quantity” you see that we have the heading right up here; so I'm going to get rid of the word “sum of” in that box, leave the space before Quantity; real important that you do that and so we can kind of get rid of those words “sum of” relatively quickly and officially rename those fields.
All right, so that's solution number one.
Solution number two for the numeric styles.
If we choose Quantity, Field settings, Number Format, Currency, 0, click, click.
Then, can we press F4 to redo the last thing we did?
Of course not; that would have been too easy.
So select Field Settings, Number Format Currency 0, click OK, click OK and then again Field Settings, Number Format, Currency 0.
All right, now this is the right way to do this because if we later take some fields out; take customer out and put region in it will remember that formatting, all right.
So that's the good way to do it but what if you created a PivotTable with dozens of fields?
So Insert PivotTable, OK.
Let's put customer down the left-hand side and just choose a boatload of fields out there.
I have 31 different fields that I could put out there.
Well that is going to be really frustrating to go through and change the numeric formatting on every one.
I have to tell you I'd be tempted, tempted to choose all the cells, Ctrl+1, Number and make all of those be Currency, 0 decimal places right off the bat, just be done with it.
Although if you do that you should go into PivotTable options and right here “Preserve cell formatting on update” turn that on; usually it’s turned off.
This will allow you to add new data to the original data set, update the PivotTable and have the cell formatting appear.
You know one more method I'm going to show you and I think it's really well I don't know I'm not going to call it weak but let's just try it.
Insert PivotTable and click OK.
We'll put customers down the left-hand side, again a whole bunch of fields across the top.
Remember back in Excel 2003 under Format there was something called Auto Format.
Well I'm here in the PivotTable and I'm going to choose my data and I'm going to do Alt+O for Format and A for AutoFormat and sure enough that old dialogue is still there with those old, old, ugly formats but these aren't the ones I want.
These are the PivotTable formats so I'm going to trick Excel.
I'm going to start outside of the PivotTable right there in that blank cell and we'll select everything in the PivotTable but you notice I've started outside the PivotTable, now Alt+O+A.
Aha then I get the real AutoFormats, the old ones and look at that.
There's a few of them, not many; kind of the four accounting formats.
They put dollar signs on the first and last row, click OK.
It shows that it has dollar signs--oh I must have chosen the wrong one, I did.
There we go okay, so yet another method to go.
I don’t know.
it just seems like it's cheating on so many different levels and I made fun of those old Auto Formats back in 2003.
I hate to even bring it up that you can even do it.
So three different ways to go.
One, either use the field settings or; Two format everything and turn on preserve formatting or; Three, this cheating method use format auto format and choose one of the accounting formats; your call how you want to proceed.
Either way.
Well hey, I want to thank Linda for sending that question in and thank you stopping by.
We’ll see you next time for another netcast with MrExcel.
