Excel Formulas

Hello Sarah,

Thank you for your question regarding creating Excel formulas in a PivotTable.

Formulas can be created inside the PivotTable using calculated fields or calculated items.

To create calculated fields you need to select any cell containing data and then in the Options ribbon from Fields, Items and Sets choose Calculated Field and then name the field and create the necessary formula and click OK.

To create calculated items you need to select a Column label or a Row label. In the fields, Items and Sets choose Calculated Item and basically follow the same procedure as above.

When using calculated items you need to switch off the grand totals either for rows or columns depending which label you chose.


Formulas can also be created outside of the PivotTable using GetPivotData.

I suggest that you consider attending our Advanced training courses especially our one day PivotTable course which covers these topics in more detail.


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer