How To Show Values & Percentages in Excel Pivot Tables

Want to Be a Power Analyst?

They have a secret… The power analysts NEVER, repeat NEVER report any raw numbers directly.

They only talk in PERCENTAGES!

  • there were 4.3% new jobs created in the last quarter, 
  • the economy shrank 2.45% year on year…
  • the ROI increased 5.34% in this month compared to the previous month, or
  • the customer service satisfaction index improved by 4.8% this quarter, over the industry average…

This is how analysts speak. So up your game, and speak in Percentages only from now onwards…

But where do you get all these percentages from in Excel?

To create a Percentage Table in Excel, all you need to do is to learn the simple Pivot Table techniques of Excel.

These techniques help you to show both values and percentages at the same time. It’s 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 2-3 new queries every day, asking for more tips on getting more juice out of using Pivot Tables in Microsoft Excel. I have now created the ultimate guide of 10 Methods to Summarize Data in 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 to Display Values & Percentages in Excel Pivot Tables.

Yes, a simple “We have 528 widgets” does not make anyone notice the numbers or their impact on the business.

But the same thing converted into “28% of our inventory is stuck in widgets” can make a big difference and make people take notice of 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 2010, Excel 2013, Excel 2016, Excel 2019 or even the latest Microsoft  Office Excel 365. Pretty much any version of Excel 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. So let’s add some clarity to our Pivot table, by adding a percentage.

Adding a Percentage Column in the Pivot Table

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 Pivot Table would look like this.

after_adding_second_value_in_pivot
Pivot Table showing 2 identical sets of columns for the values

Now you click the Second Stock Field (Sum of Stock) in the Values section and click on it again. 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 versions of Excel, it might show as % of Total. This is fine.

Newer versions of Excel,  like Excel 2016, Excel 2019 or Microsoft 365, show a % of Grand Total when you right-click on any numeric value. This is the key way to create a percentage table in Excel Pivots.

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%.

After converting the raw numbers into percentages, it is more insightful than looking at absolute numbers only.

And applying such nifty techniques to create a Percentage Table converts you into a Power Analyst who talks in Percentages, not just numbers.

Use this neat trick to convert numbers into percentages, and especially convert it to a % of Grant Total. It is a really useful technique.

I am sure you will love this simple technique.  Hope it helps! Do share it with others on Facebook or LinkedIn.

And do comment or write to tell me of any issues you are facing in using Excel every day. There may be a simple solution that can save you a lot of time. At ExcelChamp, I solve many small problems each day to make Excel easy for everyone.

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!

Cheers
Vinai Prakash

Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do check out these useful articles & Video Resources

And if you want to learn Advanced Excel fast, then check out our detailed guide.

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
!

Leave a Comment

Want to summarize data in a hurry?

No time to read all the steps?

Start with the Most Essential Steps to Summarize Data.

Download our Quick Start Guide for the 7 Steps To Summarize Data Fast

Where should we email the PDF Guide?