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
vegas86Asked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
vegas86,

If you apply a PivotTable Style to the pivot then the formatting is not lost when you run the zoom macros.

However, I still don't know how the sample file does it as it does not use PivotTable Style.

Regards,
Brian.
0
 
redmondbCommented:
Hi, vegas86.

Preserve Formatting
Regards,
Brian.
0
 
vegas86Author Commented:
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??
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
redmondbCommented:
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
0
 
redmondbCommented:
vegas86,

OK, your file is behaving similarly to mine in that, if I apply any formatting, it's lost on ZoomIn.

Regards,
Brian.
0
 
vegas86Author Commented:
haha thanks Brian, no rush! I just think it would be a good thing to know, thanks for your help!
0
 
redmondbCommented:
... 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.
0
 
redmondbCommented:
... and, of course, PivotTable Style isn't saved in .xls files. <sigh>
0
 
vegas86Author Commented:
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!
0
 
redmondbCommented:
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.
0
 
redmondbCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.