Pivot/ Calculated fields loose number format when saved&refereshed


I have a really simple Pivot Table, with a standard calculated field (summarize values by sum, but show as percentage of parent row total).

When I save & close the file, and reopen and refresh, the 'show as percentage of parent row total' disappears, showing the values as a simple sum, not %.

- The 'preserve cell formatting on update' is checked.

- I've tried to define the Number Format of this value as percentage, but this get's lost every time I re-open as well.

Do you have any ideas how I could make sure the formatting won't be changed after re-opening & refreshing?

This has become a huge pain, since I created many tabs from this one PivotTable (using Report Filter Pages), and I end up having to change them constantly.

Any help will be greatly appreciated

Thank you so much in advance

Best regards


May 29th, 2015 8:29am

Hi Kate,

Based on your description, you loss of formatting after Pivot Table refresh. This is a known issue and very annoying.  And you checked the 'preserve cell formatting on update', but the issue still exists.

Now please check these tips for creating pivot table formatting.

Tip 1:

There is a difference between formatting a pivot table and formatting the cells around it. Don't just start highlighting cells. You have to specifically select the area of the pivot table you want to format. For example, if you want to format a Subtotal, you'll have to hover your mouse over the edge of the Subtotal until your cursor turns into a black arrow. When it does, click the mouse to select that area. After the area is selected, you can format away.

Tip 2:

Clear all filters BEFORE formatting your pivot table, you can simply clear all filtering before you format. This means ALL filtering (Filter pages, dropdowns, text filters, etc).

Please refer to this article and get more information about your issue:


Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Hope its helpful.


George Zhao
TechNet Community Support

Free Windows Admin Tool Kit Click here and download it now
June 1st, 2015 3:16am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics