Is there a way to format a number when it is placed in a row field in a pivot table?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

OK, I feel like this is a stupid question, but here goes.  I certainly know how to format a pivot item (containing a number) when that number is placed in the values section of a pivot table.  But, what about when the item is placed in a row or column section?  For example, if I have an item with a value of 100%, in a table, and then create a pivot table from that, the item will be displayed with "1.", its natural value, vs. the 100% that I want displayed.  

A workaround that I found: to get around this, I created a dummy column in my raw table and used the TEXT function to format the percentage item as text, but with a format of %.  That results in my 100% in the table being displayed a 100% in the pivot table.  But, I have a lot of percent items to display in my pivot table and am looking for a more elegant solution than this.

You might ask why do I want to do this?  Why don't I just leave the pivot item in the values section along with the other values I want to display.  The reason is I'm showing allocation rollups and need preserve line of sight into the original data.  Here's a simple example:

Person A works in Group 1 and costs $100 K, Person B works in Group 2 and costs $80 K.  Total salary cost is $180 K.

60% of Group 1 rolls up to Division A; 40% of Group 1 rolls up to Division B

30% of Group 2 rolls up to Division C; 70% of Group 2 rolls up to Division D.

Organization 1 is comprised of Division A and C; Organization 2 is comprised of Division B and D.

So, how much are the salary costs for the 2 organizations.  A full line of sight to the raw costs shows:

Org    Div    Group %     Group    Person    Person Cost         Group Cost

1        A        60%            1           A            $100 K                 $60 K

2        B        40%            1           A            $100 K                 $40 K

1        C        30%            2           B            $80 K                  $24 K

2        D        70%            2           B            $80 K                  $56 K

==============================================

Total                                                                                    $180 K

with Org 1 costing $84 and Org 2 costing $96 K

This is just a silly, simple example, but you can see that the values for the Group % field need to be in the row section and not the value section.  That's how I came up with a need to have numbers being formatted as percentages and not fractions.

Thanks in advance for anyone with a solution to the above.  

Scotty81