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
If it's a regular pivot table (non data model) and all the data in that field is numeric, then there should be a number format button at the bottom of the Field Settings dialog (right-click the field, choose Field Settings). If it's a data model pivot, then I suspect you will have to explicitly format the cells that the field is in.
Hi Scotty81,
There are no formatting options for row and column fields as these are text by default. Your workaround by formatting the source data is the best solution.
Mynda
Thanks Mynda and Velouria,
I did see that the pivot table formats things a bit differently, depending on whether it's a regular pivot table or from a data model. At least I know not to keep searching for another work around.
Regards,
Scotty81