% in a pivot table

[colpliscol]

I am trying to add a column to a pivot table that shows the percentage of another column.

I have Sales and Returns listed for my company's products in the pivot table columns.

Lets say the numbers are: Sales are 10 and returns are 4. I show both columns and want to add a 3rd column that shows 40% (4/10).

Does anyone know how to do this in the pivot table? There are plenty of percentage options, but none to do this simple one. What am I missing?

 

[#BoB#]

Hi,

Right click on the pivot table and go to Formula and then Calculated Field

In the Name box, give a desired name of the field.

In the Formula box write down your formula.

Below the Formula box there is another box, Field.

You don't have to write the formula manually. Just click on the Formula box and double click the fields from the Field box.

For example, if you want % of Returns to Sales. Then double click Returns field and give "/" symbol, and then double click the Sales field.

Now press OK.

If the table is not in your desired format, then right click on the pivot and go to Format Report. And Select a suitable format for your table.

Also, don't forget to change the format of your new column into % format.


Hope this helps,


~ChillaX!!!

 

[#BoB#]

Just another thing...

If your are using Excel 2003... then first right click on the pivot and go to Show PivotTable Toolbar.

In the PivotTable toolbar, click on the PivotTable Dropdown. Then go to Formulas and then to Calculated Fields.


Regards,

 

[colpliscol]

Thank you!!!

I thought it had to be simple.