Displaying Percentages Using Pivot Tables In Microsoft Excel

Displaying Percentages Using Pivot Tables In Microsoft Excel

There are many useful functions for Pivot Tables that are available in Microsoft Excel. Among these calculations is the Percent Of function which is shown in the Show Values As menu. It will automatically represent the percentage of one value against another within the given data.

To demonstrate, an example is shown below on displaying the sales percentage of the Previous Year.

STEP 1: Select your data table. Under the Insert Menu, choose New Worksheet or Existing Worksheet from the Pivot Table option.

STEP 2: Place Sales Person at Rows Section, put Financial Year at Columns Section and place the Sales field two times in the Values Section.

STEP 3: Choose Value Field Settings from the dropdown menu of the second entry of Sales field.

STEP 4: Under the Show Values As option, choose % of from the menu that will drop down. Select Financial Year and (Previous) as the Base Field and Item respectively. This will represent the values of the Financial Year before the current year as percentages. You are free to change the default Custom Name to Previous Year’s Percent for presentation purposes. Click OK to confirm changes.

STEP 5: To make the Previous Year’s Percent values easier to read, select Value Field Settings from the drop down menu of the second entry of Sales field.

STEP 6: Choose Number Format option.

STEP 7: Under the dialog box of Format cells, change the format from decimal to percentage. Click OK twice to confirm changes. This makes it easier to read the data from the Previous Year’s Percent field.

As a finished product, your Microsoft Excel window will have a Pivot Table displaying a comprehensible Percent of Previous Year for the years’ 2012 to 2014 sales values. The values now symbolize the year as the previous year’s percentage (The year of 2013 is displayed as percentage of the Year of 2012).