How to Use the Show As Values Dialog Box for an Excel Pivot Table

Show Values As for Pivot Table

I created this Excel Video Tutorial in response to a viewer who wrote to me about a problem that he was having with an Excel 2007 Pivot Table.

Viewer’s Question

“I want to show both the % of Growth from Fiscal Year-to-Year and the Actual Variance in Volume between each of 3 Fiscal Years. I have been trying to do this with a Pivot Table Calculated Field, but I am not having any luck with this approach. What do you suggest?”

My Solution

Use the “Show values As” Dialog Box which you find in the Value Field Settings Dialog Box. I continue to be amazed, when I teach Pivot Tables in a Live Seminar, that the high majority of my students have never clicked on this Tab!

In this case, we are working with a “copy” of the original Pivot Table. Simply choose “% Difference From” in the drop-down menu. Next, choose Fiscal Year and Previous (year) to create a Fiscal Year-over_Fiscal Year Report. It is so easy to do! And… you do not have to write a single formula to create this report!