Pivot tables are a powerful feature in Excel that allow users to summarize, analyze, and visualize data. One of the more advanced features of pivot tables is the ability to add calculated fields. Calculated fields enable you to perform calculations on the data within your pivot table without modifying the original dataset. This can be incredibly useful for generating new insights and custom metrics. In this post, I’ll show you how you can take them a step forward and even incorporate IF statements within calculated fields. Here’s the data set that I’m going to use for this example:
How to add calculated fields in a pivot table
To add a calculated field to a pivot table, take the following steps:
1. Convert your data into a Pivot Table.
2. Click on any cell within your Pivot Table to activate the PivotTable Analyze tab on the ribbon.
3. On the PivotTable Analyze tab click on Fields, Items & sets and then select Calculated Field
4. Enter a name for your calculated field in the Name box.
5. Write out the formula you want to use in the Formula box. You can use existing fields (columns) from your dataset by double-clicking on the field names listed in the Fields box.
6. After you’ve completed writing your formula, click Add then press OK. Your calculated field will be added to the PivotTable, typically in the Values area.
How to use an IF statement in your calculated field
One of the more powerful uses of calculated fields is the ability to include conditional logic using an IF statement. This allows you to create dynamic calculations that can change based on the criteria you set. For my pivot table, I just have a list of dates to start with:
Suppose I want to create a calculated field which will show a value if it is profit (i.e. a gain), and a loss field which will show a value when it is negative.
In the formula box, I’ll write an IF statement for my profit field calculation. It will reference the gain/loss field which I already have. If the value is positive, it will retrieve that value, otherwise it will be zero.
Now, I’ll click on Add and then I’ll setup the Loss field:
Now when I add these fields to my pivot table, I have one column for the profit values, and one for the losses:
If you like this post on How to Track Income and Expense in a Single Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.