If you are looking for some of the easiest ways to calculate the percentage difference between two columns in the Pivot Table, then you will find this article useful. So, let’s start with our main article.
Calculate Percentage Difference between Two Columns in Pivot Table: 2 Ways
Here, we have the following table containing the selling prices and the cost prices of different products of a company. Our task is to convert this dataset to a Pivot Table and then calculate the percentage difference between selling and cost prices. To do this we will use the following two procedures.
We have used Microsoft Excel 365 version here, you can use any other version according to your convenience.
Method-1: Using ‘Fields, Items, & Sets’ Option to Calculate Percentage Difference between Two Columns in Pivot Table
In this section, we will use the Fields, Items, & Sets option of Pivot Table to calculate the percentage difference between the columns; Selling Price, and Cost Price.
Step-01: Creating Pivot Table
➤ Go to the Insert Tab >> Tables Group >>PivotTable Option.
Then the PivotTable from table or range dialog box will appear.
➤ Select the range, click on New Worksheet, and then press OK.
Then, you will be taken to a new sheet with two portions; PivotTable, and PivotTable Fields.
Step-02: Calculating Percentage Difference in Pivot Table
➤ Drag down the Product field to the Rows area, the Selling Price, and the Cost Price fields to the Values area.
After that, you will get the following pivot table.
Now, we will add an extra field that will contain the percentage difference between the Selling Price, and Cost Price fields.
➤ Go to the PivotTable Analyze Tab >> Calculations Group >> Fields, Items, & Sets Dropdown >> Calculated Field Option.
After that, the Insert Calculated Field wizard will pop up.
➤ Give the name of the new column as % Change (or whatever you want) in the Name box.
➤ To write the formula to do the calculation type =( in the Formula box.
Now, we will insert the fields into the formula to complete this formula.
➤ Select the Selling Price field from the Fields box and click on the Insert Field option.
In this way, we have inserted our desired field into the formula.
By inserting the rest of the fields into the Formula box we have completed our formula like the following.
=(Selling Price-Cost Price)/Cost Price➤ Press OK.
Finally, we will have a new column containing the results but we have to change the formatting now.
➤ Right-click on any cell of the Sum of % Change column and then select the Number Format option.
Afterward, the Format Cells dialog box will open.
➤ Select Percentage from the list of categories and select your desired value in the Decimal Places value (here, we have chosen 2).
➤ Press OK.
Finally, you will have the following percentage differences between the Selling Price and Cost Price columns.
Read More: Excel Pivot Table: Difference between Two Columns
Method-2: Using % Difference From Option to Calculate Percentage Difference between Two Columns
Here, we will be using the % Difference From option of Pivot Table to have the percentage differences between the selling prices and cost prices.
But to use this option we have to modify our dataset like the following. We have to combine the selling and cost prices of the products in the Value column and the type of the prices in the Price column.
Steps:
➤ Follow Step-01 of Method-1 to create the Pivot Table like the following figure.
➤ Drag down the Product field to the Rows area, the Price field to the Columns area, and the Value field to the Values area.
➤ Drag down the Value field again to the Values area.
After that, you will have the Pivot Table as below.
Here, we don’t need the sum of the values so we can hide them.
➤ Go to the PivotTable Analyze Tab >> PivotTable Group >> Options Option.
Afterward, the PivotTable Options dialog box will pop up.
➤ Go to the Totals & Filters option and then uncheck the options under the Grand Totals.
➤ Press OK.
In this way, we will get rid of the totals and now we will work with the Sum of Value2 field.
➤ Click on the Sum of Value2 field in the Values area.
➤ Select the Value Field Settings option.
Then, you will have the Value Field Settings dialog box.
➤ Change the name from Sum of Value2 to % Change in the Custom Name box and click on the Show Values As option.
➤ Select Price from the Base field and Cost from the Base item and then press OK.
Eventually, you will have the following percentage differences between the selling prices and cost prices.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Read More: Calculate Difference Between Two Rows in Pivot Table
Download Workbook
Conclusion
In this article, we tried to cover the ways to calculate the percentage difference between two columns in Pivot Table. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
<< Go Back to Difference in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
