Pivot Tables

Thanks for your question Anneka.

Suppose you have created a PivotTable showing sales by department. You could use a calculated field to, for example, calculate the VAT on those sales.

Row Labels Sum of Value of sale
Central £134,878
North £104,322
South £74,726
Grand Total £313,926

Here are the steps to create a calculated field:

1. Place your cursor within the Pivot Table.
2. Select the Options tab, then Fields, Items & Sets.
3. Select Calculated Field.
4. Type a name for the calculated field, eg VAT.
5. In the Formula part remove the = and double click the value field from the list of fields and multiply by the VAT rate. In this case the formula will be:

='Vale of Sales' * 0.2

6. Press OK and the calculated field column will be created to the right of the PivotTable:

Row Labels Sum of Value of sale Sum of VAT
Central £134,878 £26,976
North £104,322 £20,864
South £74,726 £14,945
Grand Total £313,926 £62,785

Editing a Calculated Field

If you want to amend a calculated field:
1. Select Option, Fields Items & Sets, Calculated Field
2. In the Name box, select the drop-down arrow to the right.
3. Choose the calculated field to amend and make the changes required.

Hope that helps. Your calculated field may be quite different from VAT. It can contain Excel functions such as IF for conditional calculations.

Regards
Doug
STL