Pivot Table: Percentage Difference between Two Columns

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.

Download Workbook


2 Ways to Calculate Percentage Difference between Two Columns in Pivot Table

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.

pivot table percentage difference between two columns

We have used Microsoft Excel 365 version here, you can use any other versions 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.

pivot table percentage difference between two columns

Step-01: Creating Pivot Table

➤ Go to the Insert Tab >> Tables Group >>PivotTable Option.

fields, items, & sets option

Then the PivotTable from table or range dialog box will appear.
➤ Select the range, click on New Worksheet, and then press OK.

fields, items, & sets option

Then, you will be taken to a new sheet with two portions; PivotTable, and PivotTable Fields.

pivot table percentage difference between two columns

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.

fields, items, & sets option

After that, you will get the following pivot table.

fields, items, & sets option

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.

fields, items, & sets 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.

pivot table percentage difference between two columns

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.

fields, items, & sets option

In this way, we have inserted our desired field into the formula.

fields, items, & sets option

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.

fields, items, & sets option

Finally, we will have a new column containing the results but we have to change the formatting now.

pivot table percentage difference between two columns

➤ Right-click on any cell of the Sum of % Change column and then select the Number Format option.

fields, items, & sets 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.

fields, items, & sets option

Finally, you will have the following percentage differences between the Selling Price and Cost Price columns.

fields, items, & sets option

Read More: Excel Pivot Table: Difference between Two Columns (3 Cases)


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.

pivot table percentage difference between two columns

Steps:
➤ Follow Step-01 of Method-1 to create the Pivot Table like the following figure.

% difference from option

➤ Drag down the Product field to the Rows area, the Price field to the Columns area, and the Value field to the Values area.

% difference from option

➤ Drag down the Value field again to the Values area.

pivot table percentage difference between two columns

After that, you will have the Pivot Table as below.

% difference from option

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.

% difference from 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.

% difference from option

In this way, we will get rid of the totals and now we will work with the Sum of Value2 field.

pivot table percentage difference between two columns

➤ Click on the Sum of Value2 field in the Values area.

% difference from option

➤ Select the Value Field Settings option.

% difference from 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.

% difference from option

➤ Select Price from the Base field and Cost from the Base item and then press OK.

% difference from option

Eventually, you will have the following percentage differences between the selling prices and cost prices.

pivot table percentage difference between two columns

Read More: Excel formula to find difference between two numbers


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.

% difference from option


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo