Calculated Fields in Pivot Tables

I have previously written about the power of pivot tables in Microsoft Excel, but in this article I will expand on that power.  Pivot tables are a great way to summarize data.  Sage uses them with their Intelligence Reporting tool.  Now what if you want to do some calculations on the raw data used for reports.  Say the data you have has sales by invoice, the cost of the sale and the margin on each sale.  Also, you want to make sure that every sale has at least a 30% margin.
To create a calculated field in a pivot table, all you need to do is select the pivot table and on the Excel menu ribbon you will get some additional options.  In my version of Excel 2013 under the ANALYZE tab select the fields, items and sets option and choose calculate field.  You can name your field and the formula.  In my example, I could call my field mrgpct and the formula would be =IF(sale>0, margin/sale,0).  I could then format that column to be a percentage and I would have the margin percent.  The final touch is to highlight the mrgpct field and from the HOME tab select conditional formatting, highlight cell rules, less than and enter 30% and select the default fill, light red fill with dark red text.
I have now highlighted any sales that did not meet the minimum margin.  This is just one example of how you can use calculated fields with pivot tables in Excel.  Calculated fields can really add great information to your reporting.
Written by Michael Ericksen
WAC Solution Partners Midwest