Adding a percentage calculated field column in my pivot table

Hi All,

I have a report that I need to pull data from. I have attached a spreadsheet by way of an example. What I have is an appraisal report and on the source data sheet I have two columns Department and Status. The Status column contains "Due", "Not Due" or "Overdue".

On my Pivot Table I use the Department for my Row data, The Status is used for my Column data and a Count of in the Data area of the table.

Now this works great but I wanted to add a calculated field column so that as well as the count I could have a percentage of "Due" and "Not Due" from the total. Currently I am having to do this by way of a manual calculation in column F and the formula is:

Please Login or Register  to view this content.

This is copied down. The trouble now is this data is not dynamic and sort of defeats the point of having a Pivot Table as if I filter my departments then the formulas don't work.

Is is possible to add a column to include a percentage field?

Thanks in advance.

Good morning Zyphon

It is indeed possible to set up a calculated field within a pivot table.

Unfortunately, you don't say which version of Excel you are using, which matters on this occasion as the location of the option mysteriously changes between versions. As you can't find it I will assume you are using XL2003 (or XP) as the option is really easy to find in XL2000 and is exactly where it should be.

Click on your pivot table and a pivot table toolbar shouild appear. From this select Pivot Table > Formulas > Calulated Field and away you go.

HTH

DominicB

Sorry dominicb, I am using Excel 2003.

I know how to add calculated fields it's just that I cannot get it in the format as laid out in my example. I don't know how to make it calculate the percentage of "Due" and "Not Due" as a percentage of the total as laid out in my formula.

Can you offer some advice please?

Thanks.

Sorry to be a pain but does anyone know how I can achieve my goal? If I haven't explained myself clearly please let me know and I shall try to explain in greater detail.

Thanks.