Pivot tables - 2 dummy questions

[eyeldham]

I can't remember how to do either of these but know they are simple.

1. I have generated pivot table but would like to format it so all the totals are in bold - I have done this before. I don't like the preset formats though.

2. I want to add a calculated field to do a percentage for GP, I have GP in table and revenue and think if I do it in the table as calc field it will then do a sensible "total" % rather than adding up all the % and coming to figure greater than 100!

Ed

 

[Excelenator]

Last things first.

#2
From the pivot tabel menu select, Pivot Table, Formulas, Calculated Fields. Name the calculation and use the fields to create your calculation.

#1
Hover over the Total that you want to format and move the cursor around until you get the solid black arrow and then left click. Apply the format you want and when you refresh the data the format will not disappear.

 

[Excelenator]

Sorry, I only looked quickly at #1. What you could do is drag the item you want represented as a % into the data section a second time and then format that field (Field Settings\Options\Show Data as and then select % of Column) to get the % you need. And yes the totals will be meaningful.

 

[eyeldham]

Thanks for the help.........but...........

I know the black arrow bit works for a single row but not for all the subtotals in the pivot table as a whole.

I can't get the second tip you gave on the dragging the item into the wizard a second time to work.

I drag it in, get the second set of info to come up and chosse the field that i want it to calc from. But I assume i still need to choose the sum function from the top section? Also it has box to thr right talking about base number - "next","previous" and all the data figures that I have in the table? Help?

Ed

 

[Excelenator]

if you hover over the 1st subtotalled row until the black arrow appears and then format it the way you want ALL subtotals of that level will be formatted the way you want.

AS for the second item you do NOT need to do anything with ANY other column or data. Follow the steps I outlined and the data will be shown as a percentage of the totals and subtotals in the columns.