vegas86
asked on
2010 Pivot Tables
Hello,
Can someone please have a look at the attached demo file and tell me how they have managed to keep the formatting in their pivot table when using the 'zoom in' 'zoom out' buttons.
You will notice that all the totals stay coloured, I thought that this was impossible and am very keen to understand how this works.
Any info will be appreciated!
Example-Report-Profit-Loss.xls
Can someone please have a look at the attached demo file and tell me how they have managed to keep the formatting in their pivot table when using the 'zoom in' 'zoom out' buttons.
You will notice that all the totals stay coloured, I thought that this was impossible and am very keen to understand how this works.
Any info will be appreciated!
Example-Report-Profit-Loss.xls
ASKER
Hi Brian,
I assumed that all I had to do was that but when I created a quick pivot table with the exact macros of zoom in and zoom out it doesn't work??
I assumed that all I had to do was that but when I created a quick pivot table with the exact macros of zoom in and zoom out it doesn't work??
vegas86,
Hm, I'm seeing something similar.
Please see attached. This virulent colour scheme is retained when I move fields between Row, Column and Report. But if I move one to Sum Values, drag it from the Pivot or use the ZoomOut macro then the formatting is lost.
So, hiding a field is dropping its format - and this is exactly what the macro is doing. But I'm now as puzzled as you are as to how ZoomIn "restores" the formatting.
This is just a status report. I'll get back to you when (?if?) I know more.
Regards,
Brian. Preserve-Pivot-Formatting.xlsm
Hm, I'm seeing something similar.
Please see attached. This virulent colour scheme is retained when I move fields between Row, Column and Report. But if I move one to Sum Values, drag it from the Pivot or use the ZoomOut macro then the formatting is lost.
So, hiding a field is dropping its format - and this is exactly what the macro is doing. But I'm now as puzzled as you are as to how ZoomIn "restores" the formatting.
This is just a status report. I'll get back to you when (?if?) I know more.
Regards,
Brian. Preserve-Pivot-Formatting.xlsm
vegas86,
OK, your file is behaving similarly to mine in that, if I apply any formatting, it's lost on ZoomIn.
Regards,
Brian.
OK, your file is behaving similarly to mine in that, if I apply any formatting, it's lost on ZoomIn.
Regards,
Brian.
ASKER
haha thanks Brian, no rush! I just think it would be a good thing to know, thanks for your help!
... and if I apply and remove a Style then the original formatting is also lost.
If the original formatting had come from a custom style that might explain what we're seeing. Unfortunately there's no Style applied.
I just think it would be a good thing to know
Unfortunately you've now infected me. :)
Regards,
Brian.
If the original formatting had come from a custom style that might explain what we're seeing. Unfortunately there's no Style applied.
I just think it would be a good thing to know
Unfortunately you've now infected me. :)
Regards,
Brian.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
... and, of course, PivotTable Style isn't saved in .xls files. <sigh>
ASKER
Hey Brian,
This is good enough for me, I just tried this and it worked! thank you for all your help, i really do appreciate it!
This is good enough for me, I just tried this and it worked! thank you for all your help, i really do appreciate it!
Hah! Easy for you to say, I'm still stuck trying to work out what's going on! :)
Many thanks, vegas86, and all the best.
Many thanks, vegas86, and all the best.
vegas86,
Ok, half got it - the pivot has, almost certainly, had an AutoFormat applied to it. The AutoFormat feature seems to me to be little more than a hangover from pre-2007 days. It's not on the Ribbon, so it needs to be added to the Quick Access Toolbar (the button is "AutoFormat..." - look in "Commands not in the Ribbon").
The button provides 22 layouts - 10 "Table", 10 "Report", "PivotTable Classic" and "None". Interestingly (well, to me, but then I'm the kind of sad b*st*d who reads T&C's), these are specifically referenced in the ECMA OpenXML specification (ECMA-376 3rd edition Part 1, a 50 mb download which also includes PivotTableFormats.xlsm which has an example of each format).
The bit still outstanding for me is to be able to identify which, if any, AutoFormat has been applied to a Pivot. Famous last words, but, now that I know what I'm looking for, I don't expect this to be a problem.
Regards,
Brian.
Ok, half got it - the pivot has, almost certainly, had an AutoFormat applied to it. The AutoFormat feature seems to me to be little more than a hangover from pre-2007 days. It's not on the Ribbon, so it needs to be added to the Quick Access Toolbar (the button is "AutoFormat..." - look in "Commands not in the Ribbon").
The button provides 22 layouts - 10 "Table", 10 "Report", "PivotTable Classic" and "None". Interestingly (well, to me, but then I'm the kind of sad b*st*d who reads T&C's), these are specifically referenced in the ECMA OpenXML specification (ECMA-376 3rd edition Part 1, a 50 mb download which also includes PivotTableFormats.xlsm which has an example of each format).
The bit still outstanding for me is to be able to identify which, if any, AutoFormat has been applied to a Pivot. Famous last words, but, now that I know what I'm looking for, I don't expect this to be a problem.
Regards,
Brian.
Regards,
Brian.