Pivot tables are effortless. It is not only capable of doing heavy data lifting and quickly summarize and analyze the data but can also perform some additional calculations.
Value Field Settings provides a plethora of options as quick calculations. This article covers my top 5 such features.
How to get Value Field Settings?
Value field settings offer plenty of flexibility for calculations. There are two ways we can access Value Field Settings:
Right-Click on any cells containing the value which we want to transform:
Under Values, click on the drop-down option arrow:
Percentage of Column Total or Row Total
The percentage is a crucial aspect of any analysis as it provides a baseline. Using a percentage, we can compare the performance of different periods or different KPIs.
The most common calculation is calculating the percentage of column total or row total. Value Filed settings come with extensive calculation options.
% of Column Total
Value Field Settings > Show Value As > % of Column Total
Output:
Analytical conclusion: Steve has contributed 23.95% to the total sales whereas Bob's contribution is only 0.91%.
% of Row Total
Value Field Settings > Show Value As > % of Row Total
Analytical conclusion: A majority of Bob's sale (approx. 70%) is from Corporate and Home Office segments. Overall, Small Business has the lowest contribution to sales(17.98%).
Percentage of Parent Total
When we have multi-level report structure, Pivot table provides the flexibility of choosing the denominator:
Analytical conclusion: Tables contributes towards 38% of sales in the Furniture segment and 13% on the overall product category. Technology has the highest contribution to sales(approx. 42%).
Value Field Settings > Show Value As > % of Parent Total > Select the Parent Category
Percentage Running Total
This one helps in prioritizing the top and bottom contributors. We also know this as the Pareto Rule or 80/20 rule.
Output:
Analytical conclusion: The highlighted segments contribute towards approx. 80% of the total sales volume.
We can also get the absolute number (Select Running Total In)
Percentage Difference from Previous Row or Column
Another related useful feature is that we can calculate the percentage difference from the previous row or column. This type of calculation is handy when we compare from one period to another.
% Change from the previous row
Value Field Settings > Show Value As > % Difference From
Output
Analytical conclusion: There is a 5.23% reduction in the total orders from the year 2010 to 2011.
% Change from the previous column
Summary data:
% change from the previous column:
Analytical conclusion: Sales for the Atlantic region for the year 2011 has reduced by 20.32%.
Add Ranking
The ranking is handy during analysis. In Excel, we can use functions like RANK.AVG or RANK.EQ. Pivot provides a drag & drop option to calculate a ranking.
Analytical conclusion: Our top 3 ranked products are Office Machines, Telephones and Communication, and Tables.
And the best part about the pivot is the dynamism it provides.
You may follow this article for more on Ranking: Use Ranking for comparison
Less effort, more output!
Comments