46. Videotutorial – Deal with Pivot Tables 6 – Calculated field

Another episode of tutorials Deal with Pivot Tables continues with the topic Calculated field. Without changing the source table we will create a new column with a formula. You will learn how a field is added to the table, or to influence the source data and then update the PivotTable. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Please note that this tutorial is presented in czech language, with english subtitles.

Source file can be downloaded here.

If you are for some time using Pivot Tables whose basics we have seen in previous episodes, you may have identified the need to create new calculations. I mean you need new columns from the existing columns to calculate the new value. Such a column we call calculated field and now I will show you how to do it.

We see a Pivot Table with the travel cost to individual countries. We wish to express values ​​in Euros. I do not want to change the source data, and writing a formula to neighboring cells (outside the pivot table) is possible, but ineffective in terms of future adjustments. Therefore, use the built-in tool for this purpose, go to Analysis tab, activate the button Fields, items and sets, Calculated field. You can create an entirely new column with a formula. We’ll call it “EURO” and into the formula enter the existing amount field (Částka) and divide by exchange rate for the Euro. Unfortunately we can not use the functions or links to cells. Click OK to confirm and field appears in a PivotTable. If it did not automatically, drag it to the Values ​​section. Remember to change the desired format. Thus calculated field can only display totals, other functions are unavailable.

If we want to remove a calculated field, we need to find it again in the same way as when creating it. Here, then delete it.

If something does not work, you still have the option to add new column with a formula into source table and then update the PivotTable.

Next time we will look at filtering and Slicers, until then try introduced functionality and any questions should be directed video below.