How To Show Values & Percentages in Excel Pivot Tables

Want to Be a Power Analyst?

You must learn simple Pivot Table techniques in Excel like showing both values and percentages. Its like having your cake and eating it too!

Pivot Table Showing Values & Percentages
Pivot Table Showing Values & Percentages

Ever since I published the How to Analyze data with Pivot Tables Video on YouTube, I receive an average of 1 new query every 2 days, asking for more tips on getting the maximum value from using Pivot Tables in Microsoft Excel.

This tip about showing Percentages in the Pivot table is pretty simple, yet it could make you into the next Power Analyst you see on CNBC or other TV News channels.

You see, analysts crunch numbers on Business Shows, News Channels all day long. And they make it look so sexy, so cool…. They talk about Year or Year Growth, Quarterly Income Growths, Dividend per share, and most importantly, the presentation has an impact on you… because they talk about percentages.

Simply learn this neat trick at ExcelChamp.Net here – step by step.

Yes, a simple ” We have 528 widgets” does not make any one notice.
But the same thing converted into “28% of our inventory is stuck in widgets” can make a big difference, and make people take notice about what you say.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


How to Convert Numbers Into Percentages?

Well, most of the time, this nifty trick can be done using the basic Pivot Table in Excel. And the version of Excel does not matter. You could use Excel 2003, Excel 2007, Excel 2010 or even the latest Excel 2013. Pretty much any version will do.

Let’s say you are showing Categories, Products and their inventory in a Pivot Table like below.Rawdata before Pivot Creation

It is useful to see the inventory by category, but it does not clarify things.

Inventory by Category in Pivot
Inventory by Category in Pivot

If you just drag the Stock field to the Values section of the Pivot Table one more time, the output would look like this.

after_adding_second_value_in_pivot

Now you click the Second Stock Field in the Values Section, and click on it. You will see a popup like below.

Show Value As Popup in Microsoft Excel 2010
Show Value as Popup

Choose Show Value As > % of Grand Total. In some Version, it might show as % of Total. This is fine.

The Pivot view now changes to this:

Pivot with Values & Percentages
Pivot showing Values & Percentages both at the same time

Isn’t it magical! Now you can see that the Art category accounts for 42% Inventory, and Computer Products account for 35%, followed by Stationery, at 23%.

This is more insightful than the absolute numbers. And it converts you into a Power Analyst, who talks in Percentages, not just numbers.

Use it. I am sure you will love this simple technique.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Resources:

Do post your comments about this technique.

Cheers,
Vinai