Let’s say we have a built pivot table with the results of analyzing sales by months for different cities (if necessary, read this article to understand how to create them in general or refresh your memory):
We want to slightly change its appearance so that it displays the data you need more clearly, and not just dumping a bunch of numbers on the screen. What can be done for this?
Other calculation functions instead of the banal amount
If you right-click on the calculated field in the data area and select the command from the context menu Field Options (Field Settings) or in Excel 2007 version – Value field options (Value Field Settings), then a very useful window will open, using which you can set a bunch of interesting settings:
In particular, you can easily change the field calculation function to mean, minimum, maximum, etc. For example, if we change the sum to the quantity in our pivot table, then we will see not the total revenue, but the number of transactions for each product:
By default, Excel always automatically selects summation for numeric data. (Sum), and for non-numeric ones (even if out of a thousand cells with numbers there is at least one empty one or with text or with a number in text format) – a function for counting the number of values (Count).
If you want to see in one pivot table at once the average, the amount, and the quantity, i.e. several calculation functions for the same field, then feel free to throw the mouse into the data area of the field you need several times in a row to get something similar:
…and then set different functions for each of the fields by clicking on them in turn with the mouse and choosing the command Field Options (Field settings)to end up with what you want:
If in the same window Field Options click the button Additionally (Options) or go to the tab Additional Calculations (in Excel 2007-2010), then the drop-down list will become available Additional Calculations (Show data as):
In this list, for example, you can select options Percentage of line amount (% of row), Percentage of total by column (% of column) or Share of the total (% of total)to automatically calculate percentages for each product or city. This is how, for example, our pivot table will look like with the function enabled Percentage of total by column:
Sales dynamics
If in the drop down list Additional Calculations (Show data as) choose option Distinction (Difference), and in the lower windows Field (Base field) и Element (Base item) select Month и Back (in the native English version, instead of this strange word, there was a more understandable Previous, those. previous):
…then we get a pivot table that shows the differences in sales of each next month from the previous one, i.e. – sales dynamics:
And if you replace Distinction (Difference) on Given difference (% of difference) and add conditional formatting to highlight negative values in red, we get the same thing, but not in rubles, but as a percentage:
PS
In Microsoft Excel 2010, all of the above calculation settings can be done even easier – by right-clicking on any field and selecting commands from the context menu Totals for (Summarize Values By):
… and Additional Calculations (Show Data as):
Also in the version of Excel 2010, several new functions were added to this set:
- % of total by parent row (column) – allows you to calculate the share relative to the subtotal for a row or column:
In previous versions, you could only calculate the proportion relative to the grand total.
- % of the amount cumulative – works similarly to the cumulative sum function, but displays the result as a fraction, i.e. in percents. It is convenient to calculate, for example, the percentage of the plan or budget execution:
- Sorting from smallest to largest and vice versa – a slightly strange name for the ranking function (RANK), which calculates the ordinal number (position) of an element in the general list of values. For example, with its help it is convenient to rank managers by their total revenue, determining who is in what place in the overall standings:
- What are pivot tables and how to build them
- Grouping numbers and dates with the desired step in pivot tables
- Building a PivotTable Report on Multiple Ranges of Source Data