Taylor from Orange County California calls in to ask how you can get the formatting of a pivot table to stick? Every time that you refresh a pivot table, the formatting reverts back to a default. The bad news is that you can not make all formatting stick, but Episode 865 will show you a couple of methods to use.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
We had a call from Taylor in Orange County, California.
Taylor's asking about formatting in a Pivot Table-- how do we get the formatting to stick?
So, if you format something in a Pivot Table, and then add more data and refresh, how does it stick?
Well, this is a frustrating problem, and I don't have good solutions for everything.
The one tip I can pass along is, if we go to Sum of Revenue and click the blue “I”-- Field Settings button-- and then go to the Number tab...
So, by changing the number format here to currency, we will make sure that that setting does stick through future updates.
So, we can change the heading from Sum of Revenue to Revenue space, and of course, that will stick.
Notice that you have to put a space at the end; same thing here, change the heading to Profit space-- you have to put Sum character to make it not be Profit; click Number; choose Currency with 0 "Decimal places"; click OK; click ok.
Well, now those settings will persist, but Taylor actually mentioned, you know, tiny things like changing the format of the subtotal rows.
And the problem is that that's going to get wiped out when we refresh.
Now, if you use one of their 14 Auto Formats here, those settings will persist.
The problem that I have is that in Excel 2003, these 14 formats are the worst thing I've ever seen-- I've never found anyone who likes any of these and, you know, it's frustrating that it actually changes the format of your Pivot Table, and I'm not going to seriously suggest that anyone starts to use this.
It would be really nice if they would allow us to specify a format for this Pivot Table, and even a template for all future Pivot Tables.
It's just not in the cards yet.
Now, I have another tip.
Taylor mentioned adding more data to the bottom of the data set.
If you would, in Excel 2003, press Ctrl+L first-- in Excel 2007 it's T-- Ctrl+L or Ctrl+T, you could then take a few hundred rows and paste them immediately below your data-- Ctrl+V-- click OK.
And now, back in the Pivot Table, you don't have to go back into the Wizard to re-specify, you just hit the Refresh button and Excel will refresh.
Now, you see, because I used that setting within the Pivot Table field list to chain the number formats, my number formats persisted; and because I used one of these hideous 14 formats, the formats persisted.
Now, if you happened to have upgraded to Excel 2007, you do have a little bit more control here in the Pivot Table styles, in that you can define your own style.
And so you might be able to find a setting where you can control things better, and, of course, those settings will persist as we change.
Again, in 2007, you're going to click on Sum of Revenue, go to the Options tab, and then choose the Field settings button-- it's the same blue "I" but it actually has the word "Field Settings"-- and we can change this to Revenue space, and then click Number Format here on the lower left to change to currency, click 0.
And now, those settings will persist through future refreshes.
But, Taylor, I certainly understand the problem and it's one of those frustrating things that every time we refresh the Pivot Table, we lose a lot of our settings.
By using the built-in formats and that number format, you can control a few more things, but, certainly, you're going to lose some formats as you go.
I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
We had a call from Taylor in Orange County, California.
Taylor's asking about formatting in a Pivot Table-- how do we get the formatting to stick?
So, if you format something in a Pivot Table, and then add more data and refresh, how does it stick?
Well, this is a frustrating problem, and I don't have good solutions for everything.
The one tip I can pass along is, if we go to Sum of Revenue and click the blue “I”-- Field Settings button-- and then go to the Number tab...
So, by changing the number format here to currency, we will make sure that that setting does stick through future updates.
So, we can change the heading from Sum of Revenue to Revenue space, and of course, that will stick.
Notice that you have to put a space at the end; same thing here, change the heading to Profit space-- you have to put Sum character to make it not be Profit; click Number; choose Currency with 0 "Decimal places"; click OK; click ok.
Well, now those settings will persist, but Taylor actually mentioned, you know, tiny things like changing the format of the subtotal rows.
And the problem is that that's going to get wiped out when we refresh.
Now, if you use one of their 14 Auto Formats here, those settings will persist.
The problem that I have is that in Excel 2003, these 14 formats are the worst thing I've ever seen-- I've never found anyone who likes any of these and, you know, it's frustrating that it actually changes the format of your Pivot Table, and I'm not going to seriously suggest that anyone starts to use this.
It would be really nice if they would allow us to specify a format for this Pivot Table, and even a template for all future Pivot Tables.
It's just not in the cards yet.
Now, I have another tip.
Taylor mentioned adding more data to the bottom of the data set.
If you would, in Excel 2003, press Ctrl+L first-- in Excel 2007 it's T-- Ctrl+L or Ctrl+T, you could then take a few hundred rows and paste them immediately below your data-- Ctrl+V-- click OK.
And now, back in the Pivot Table, you don't have to go back into the Wizard to re-specify, you just hit the Refresh button and Excel will refresh.
Now, you see, because I used that setting within the Pivot Table field list to chain the number formats, my number formats persisted; and because I used one of these hideous 14 formats, the formats persisted.
Now, if you happened to have upgraded to Excel 2007, you do have a little bit more control here in the Pivot Table styles, in that you can define your own style.
And so you might be able to find a setting where you can control things better, and, of course, those settings will persist as we change.
Again, in 2007, you're going to click on Sum of Revenue, go to the Options tab, and then choose the Field settings button-- it's the same blue "I" but it actually has the word "Field Settings"-- and we can change this to Revenue space, and then click Number Format here on the lower left to change to currency, click 0.
And now, those settings will persist through future refreshes.
But, Taylor, I certainly understand the problem and it's one of those frustrating things that every time we refresh the Pivot Table, we lose a lot of our settings.
By using the built-in formats and that number format, you can control a few more things, but, certainly, you're going to lose some formats as you go.
I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
