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

Do You Use These Features of Microsoft Excel?

Most people hardly use the most useful features available in Microsoft Excel. Many are just using Excel as a calculator to do basic calculations like adding or subtracting numbers. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work to improve your productivity and efficiency.

  1. Finding the Top 10 Customers or Finding the Bottom 5 Performers in the organization
  2. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding – like =Salary – CPF + Allowance
  3. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – like finding sales by product groups, or calculating productivity by department, or calculating sales by department by product by area.
  4. Write Macros to automate routine things that save you a huge amount of time – example creating different pivots, charts, tables, and doing complex calculations, or compiling the cost benefit ration, cash flow statement automatically.
  5. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted, or all products with a net profit margin of less than 5% be highlighted.
  6. Sort the values in Ascending, Descending or any Customized sorting order – like sorting in order of Manufacturing, Accounts, Sales departments.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria – like salary > 50,000 and sales <100,000 or Zone in East, West.
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason. Do you know when to use which type of chart – like two axis charts, stacked column and line charts, bubble charts, pie, slice of a pie etc.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a dropdown value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas – find values that in the left of the looked up value
  12. Protect certain cells from data entry, and hide the formulas from other cells. Ensure that only certain values are keyed in the cells to avoid data discrepancies.

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
!


This is just the tip of the iceberg… These are common things that can be done easily with Excel’s in-built features.

So how many of these did you know? And how many would you like to learn more about? Even if you learn one new trick in Excel, it will save you immense time and improve your productivity. Investing in Microsoft Excel Training bears fruit in no time, and is a no brainer.

Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013 adds more and more features to the already powerful dynamite of a package.

At ExcelChamp, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, just let us know. We provide online training through videos, which will show you each click, step by step, so you can master the new techniques easily, with exercises, and practical examples.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to us to know more about Online Excel Training at ExcelChamp.Net.

Cheers,
VInai Prakash, PMP, ITIL, GAP, Six Sigma, Excel Guru

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!

How to Analyze Data using Excel Pivot Tables

Do you know how easy it is to use the Pivot Tables in Excel, for Data Analysis?

To demonstrate this, I went to the Internet, and  found some travel data. It belongs to an unnamed Travel Company. The travel company collects the amount of ticket sales, the age of the passenger, and their gender only.

Based on this, how do we analyze the data, and find some hidden gems of information that can make the data come alive, and aid us in doing some real life Marketing.

Watch this step by step video on analyzing this data, using the Pivot Table technique in Microsoft Excel. I am using Excel 2010 version for this demonstration.

As you can see, many hidden insights can be found by using the Pivot Tables in Excel. So get started… Use them in your day to day work, and become a Data Analyst, rather than a Data Gatherer or Data Reporter…

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
!


Have you subscribed to the ExcelChamp.Net Email Newsletter, where we send you such tips by email?

Additional Resources

Cheers,
Vinai Prakash, PMP, ITIL, MBA, GAP
Founder: ExcelChamp.Net – Providing Everyday Tips for Improving Productivity, Using Excel!

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!