how to add calculated fields to pivot tables in excel 2013

You can use the calculated fields just like other fields. The thing is: When it comes to data analysis, quick and effective reporting, or presenting summarized data nothing can beat a pivot table.

Functions without cell references can be used for example in this case you can also write the formulas as =IFERROR(Profit / Sales, “NIL”)Īdd the Calculated Field to your Pivot Table Pivot Tables are one of the Intermediate Excel Skills and this is an Advanced Pivot Table Tutorial that shows you the top 100 tips and tricks to master this skill.

Note that you cannot use functions or array formulas that require cell references or defined names.

You can use Functions and Operators ( +, –, /, *) and existing fields to make a customized calculated field.

Give your calculated field a name. We are calling here ‘Profitability’.

With this option you can add a custom calculated field in the pivot table.

Excel 2013 – Placed the Analyse Tab under Fields, Items & Sets.

Excel 2007 & 2010 – Calculated Field is placed in the Options Tab under Field, Items and Sets.

(Picture Credit – Mynda from MyOnline training hub) Next, select the calculated field you want to work with from the name drop-down list. Then, on the Options tab of the PivotTable Tools ribbon, click Fields, Items & Sets, then choose Calculated Field. First, select any cell in the pivot table. Introducing Calculated Field in Pivot Tables To modify a calculated field, you need to navigate to the Insert Calculated Field dialog box. Profitability means Profit/Sales or the Percentage Profit. One of the ways to get this done is through calculated fields But what if we have to calculate the profitability region wise? Don’t mistake the profitability as Sum of Profit. Simple – Drop the Region in Rows and Sales in Values. Lets quickly summarize the data in a pivot table with region wise sales, shall we ? The easiest way to Create Pivot Table in Excel is to insert one of the recommended PivotTables in to the worksheet and modify it to suit your own needs. One of the less known and extremely awesome feature of Pivot Tables is its ability to create calculated fields with in the Pivot Table

How to add calculated fields to pivot tables in excel 2013