?
Solved

2010 Pivot Tables

Posted on 2011-10-13
11
Medium Priority
?
806 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:vegas86
  • 8
  • 3
11 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36965739
Hi, vegas86.

Preserve Formatting
Regards,
Brian.
0
 

Author Comment

by:vegas86
ID: 36965752
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36965861
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:redmondb
ID: 36965916
vegas86,

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

Regards,
Brian.
0
 

Author Comment

by:vegas86
ID: 36965918
haha thanks Brian, no rush! I just think it would be a good thing to know, thanks for your help!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36965936
... 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
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 36978962
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36979183
... and, of course, PivotTable Style isn't saved in .xls files. <sigh>
0
 

Author Closing Comment

by:vegas86
ID: 36982793
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36982847
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36993833
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question