42. Videotutorial – Deal with Pivot Tables 2 – Calculation options

The video series Deal with Pivot Tables continues with episode Calculation options. In this tutorial you will learn how to change the aggregate function in calculations, how to set up percentage totals and how to display more summaries at once. 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.

We can see prepared Pivot table from the previous episode, showing the total summary cost of business trips in the country. Let’s look at the various options for calculations.

The basic function is summary. If we want to change the type of subtotal function, just click in a field with right mouse and find a choice of Summarize Values by, where you select the desired function, e.g. average. Now we see the average cost of one business trip to each country. Nothing prevents us from seeing more calculations simultaneously. Dragging the cost (Částka) field again to the values section ​​and subsequent change in aggregate function in this column achieve a combination of both results, e.g. average and count.

Drag the cost (Částka) field into Values sections ​​for the third time, but this time I change the calculation so that instead of numbers it shows the percentages of the total. It can be set again using the right mouse menu, find Show Values ​​As and choose % of Column Total, this option is one of the most widely used. The total of column Excel understood as 100%, and each value given to it expressed as a proportion.

This is a general principle of making changes in the basic calculations in pivot tables.

In the next episode we examine the Design tab, which contains not only arrangement options.