Pivot table with % of total?

  • Thread starter Thread starter jago25_98
  • Start date Start date
J

jago25_98

How do I make a pivot table with values that are a percentage of the
total?

– this makes it easier to interpret; the highest % jumps straight out;
you don’t have to convert them all by hand before you can begin to see
which values are the highest.

Any ideas?
 
Assuming you've already got one that you're trying to fix ...

Rightclick one of the value cells, choose Field Options. The Pivot Table
Field dialog has an Options button, click it to access a "Show data as"
dropdown. "% of total" option should be near the bottom of the list.

HTH,
Andy
 
brilliant! done :)

a slight niggle though - can I remove the 0% values somehow; they get
in the way quite a bit?
 
a slight niggle though - can I remove the 0% values somehow

I got nowhere tinkering with formats, & could only find a real ugly way to
do this.

Originally I had a 2 col data source, Names and Numbers. I added a 3rd col
"Total", =SUM(B:B).

After redefining "where is the data?", I rightclicked a value cell in the
table, then Formulas -- Calculated Field.

For a name "% of Total", I used the formula =Pts/Total *100.

Back in the table, I used the Data dropdown to hide the Sum of Points rows
(after which the Data dropdown vanished).

Is it worth it? I expect someone else will come up with something better.

Rgds,
Andy
 
Back
Top