Pivot table loses formating on refresh

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark1406 - 28 Aug 2007 09:50 GMT

I'm using Excel for Mac 2004 V11.3.3

I've created a pivot table, which when refreshed on my PC works fine.
However, if I refresh it on my Mac I lose all the formating. I found
the support article 162028 on the Microsoft web site, which suggests

" This problem occurs if you update data or change the layout of the
table when the following conditions are true:
·    You click PivotTable on the PivotTable toolbar, point to Select, and
then click Enable Selection (you deselect the option)
·    You clear Preserve Formatting in the PivotTable Options dialog box.
·    You format data in an existing PivotTable."

However, I have checked all my settings and this does not appear to be
the case. I have enabled selection, preserve formatting is checked and
I have a formatted pivot table, which becomes unformatted when I
refresh the data. There is a somewhat cryptic footnote to the effect:

"Note: Microsoft Excel does not retain changes to cell borders when
you change the layout or refresh a PivotTable."

Does this really mean the table formatting? Any ideas?

Reply to this Message

JE McGimpsey - 28 Aug 2007 15:14 GMT

> I'm using Excel for Mac 2004 V11.3.3
>
[quoted text clipped - 7 lines]
>
> Does this really mean the table formatting? Any ideas?

With Preserve formatting checked, my PTs retain font color, interior
color, bold, italic, etc, on refresh. Cell borders do get reset.

First, you're several updates behind - the current version is 11.3.7,
though I don't necessarily think that's the cause of the problem.

Do you get the same behavior if you recreate the PT in MacXL?

Reply to this Message

CyberTaz - 28 Aug 2007 17:53 GMT

Hi John -

Just to add to the conundrum... Upon reading this post I did some brief
investigation on my own (PPC, 10.4.10, 11.3.7). I found that if I bold the
column & row headings *not* including the Grand Total headings that the
format holds on refresh. However, if the Grand Total headings have been
formatted *all* formatting in the PT is stripped on refresh.

Naturally I was a bit reluctant to reply to the OP %-}

Signature

Regards |:>)
Bob Jones
[MVP] Office:Mac

>> I'm using Excel for Mac 2004 V11.3.3
>>
[quoted text clipped - 15 lines]
>
> Do you get the same behavior if you recreate the PT in MacXL?

Reply to this Message

Mark1406 - 11 Sep 2007 14:54 GMT

Hi Bob,

Just got back from leave, hence the late response. My PT does have formatted
Grand Total headings - and yes all the formatting gets blown away, including
the column headings. Is this likely to be addressed in a forthcoming release?
In the meantime I'll try removing the GT formating on the PC version....

Regards,

Mark

> Hi John -
>
[quoted text clipped - 25 lines]
> >
> > Do you get the same behavior if you recreate the PT in MacXL?

Reply to this Message

Mark1406 - 11 Sep 2007 15:38 GMT

Hi John,

I recreated the PT in MacXL. If I apply the formatting to the headings then
it gets blown away on refresh, however the rest of the formatting is
preserved including the column labels, so it is marginally better. I guess I
could write a macro to reapply the formatting.... PIA.

Cheers,

Mark

> > I'm using Excel for Mac 2004 V11.3.3
> >
[quoted text clipped - 15 lines]
>
> Do you get the same behavior if you recreate the PT in MacXL?

Reply to this Message

Mac Forum / Applications / Excel / September 2007

Pivot table loses formating on refresh