MrExcel.com - Your One Stop for Excel Tips & Solutions

Better Calculations with Show Values As

January 23, 2023 - by

Better Calculations with Show Values As

Problem: Excel offers eleven functions on the Summarize Value By tab of the Value Field Settings dialog. Those are not the good ones. The good ones are on the Show Values As tab. Ninety percent of the time that I change the calculation, I am using one of the relatively hidden Show Values As calculations.

Strategy: Drag the Revenue field multiple times to the pivot table Values area. Select a cell in one column, use Field Settings and change the Show Values As.

Columns in the pivot table are Sum of Revenue, Percent of Total, Rank, Running Total, and Percentage Difference from Manufacturing.
Figure 955. Show values as a variety of calculations.

Gotcha: The calculations require one, two, or zero arguments. An example of each follows.

The numbers in C4:C10 use the % of Column Total setting. You simply choose this setting. You don’t have to specify any additional information.

The Value Field Settings dialog. The Custom Name is % of Total. The Show Values As is % of Column Total.
Figure 956. Show values as a percentage of the column total.

Rank and Running Total are examples where Excel will ask you to identify the base field. Most often, this will be the row field.

% Difference From is a calculation that requires a Base Field and a Base Item. The calculation in F4:F9 expresses revenue as a percentage of Manufacturing revenue.

The second tab in the Value Field Settings is Show Values As. Here, the calculation is % Difference From. The Base Field is Sector and the Base Item is Manufacturing
Figure 957. Enter a base field and a base item.

The (previous) entry in the figure above is great for reports with dates. This report shows the sales as a percentage change from the previous day.

Show Values As % Difference From. The Base Field is Sector and the Base Item is Manufacturing.
Figure 958. Specify a base field and a base item.

Additional Details: You can combine the 11 functions on Summarize Values By and the 15 settings under Show Values As. The figure below is showing the average sale for each day and then the % change from the previous day of the average sale.

Percentage Change from Yesterday. With dates in the Row field, in the Value Field Settings, choose the Show Values As tab, then % Difference From. The Base field is Date and the Base Item is (previous)
Figure 959. Change from previous day.

In this figure, the percentages in C16:C18 express the revenue as a percentage of the total sector revenue in B15. The confusing part is that the 14.35% in C15 shows how the Communications sector total of $962K compares to the grand total of $6.7 million. All of the percentages are correct, it is just strange to see a smaller number on the total line than on the detail lines.

The Percentage of Parent Row setting uses a different divisor for each row. AT&T in row 16 is 51.83% of the Communications sector in row 15. The Communications sector in row 15 is 14.35% of the grand total in row 24.
Figure 960. With multiple fields in rows, report as a percentage of parent item.

This article is an excerpt from Power Excel With MrExcel

Title photo by Linus Nylund on Unsplash