Excel Pivot Tables have a lot of usefulÂ calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF COLUMNÂ TOTALÂ calculation.

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: