MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Preserving pivot table formats

Posted by Dan on August 15, 2001 2:00 PM

Hi, any help is much appreciated.

I have tried using the "grouping" feature to group a list of percentages in a pivot table. I would like to preserve the percentage format but, so far, all I get are the groupings in decimal format. Any advice?
Thank you in advance,

Posted by Mark W. on August 15, 2001 2:38 PM

You can't rely on the Percentage format to get
the results you want because a grouped item is no
longer a number -- it's text. Why don't you
select these items and use Find/Replace to replace
"0." with "". And, then apply the following
number format...


Posted by Mark W. on August 15, 2001 2:46 PM


You'll want to at least temporarily check the
grouped field's "Show items with no data" check
box on the PivotTable Field dialog to be sure
that you convert all of the grouped items --
even those without corresponding data in your
list or database.

After you replace "0." with "" you may have to
replace "." with "". This 2nd step will fix
all of the group items greater than 100% or 1.0.
However, it's IMPORTANT that you do the "0."
replacement 1st!