This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, expenses, attendance, or anything that can be quantified.
In the example below I show you how to get theĀ Percent of ColumnĀ Total:
STEP 1:Ā Insert a new Pivot table by clickingĀ on your data and goingĀ toĀ Insert > Pivot Table > New Worksheet or Existing Worksheet
STEP 2:Ā In theĀ ROWSĀ section put in theĀ SalesĀ MonthĀ field, in theĀ COLUMNSĀ put in the FinancialĀ YearĀ field and in theĀ VALUESĀ area you need to put in theĀ SalesĀ field twice, I explain why below:
STEP 3:Ā Click the second Sales fieldāsĀ (Sum of SALES2) drop down and chooseĀ Value Field Settings
STEP 4:Ā Select theĀ Show Values AsĀ tab and from the drop down chooseĀ % ofĀ ColumnĀ Total.Ā
Also change theĀ Custom Name into Percent of ColumnĀ Total to make it more presentable.Ā ClickĀ OK.
STEP 5:Ā Notice that the Percent of ColumnĀ TotalĀ data is in a decimalĀ format that is hard to read:
To format the Percent of ColumnĀ TotalĀ column, click the secondĀ Sales fieldāsĀ (Percent of ColumnĀ Total) drop down and chooseĀ Value Field Settings.Ā
The goal here is for us to transformĀ numbers fromĀ a decimalĀ format (i.e. 0.23), into a percentageĀ format that is more readable (i.e. 23%).
STEP 6: Click the Number Format button.
STEP 7:Ā Inside theĀ Format Cells dialog box, make your formatting changes within here and pressĀ OKĀ twice. Ā
In this example, we used theĀ PercentageĀ category to make our Percent of ColumnĀ TotalĀ numbers become more readable.
You now have your Pivot Table, showing the Percent of ColumnĀ TotalĀ for the sales data of years 2012, 2013, and 2014.
All of the sales numbersĀ are now represented as a Percentage of each column (Years 2012, 2013 and 2014), which you can see onĀ each columnĀ is represented as 100% in totality:
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.