Pivot Table: Percentage Difference between Two Columns – 2 Methods

The following table contains selling and cost prices of different products.

Method 1 – Using the ‘Fields, Items, & Sets’ Option to Calculate the Percentage Difference between Two Columns in a Pivot Table

Step 1: Creating a Pivot Table

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

The PivotTable from table or range dialog box will be displayed.

• Select the range, click New Worksheet, and click OK.

A new sheet with PivotTable, and PivotTable Fields will be displayed.

Step 2: Calculating the Percentage Difference in the Pivot Table

• Drag Product to Rows, Selling Price and Cost Price to Values.

You will see the following pivot table.

• Go to the PivotTable Analyze Tab >> Calculations >> Fields, Items, & Sets >> Calculated Field.

• Name of the new column % Change in the Name box.
• Enter =( in the Formula box.

• Select the Selling Price in Fields box and click Insert Field.

The field is added to the formula.

• Enter the rest of the fields into the Formula box:

• Click OK.

A new column with the results is displayed.

• Right-click a cell in the Sum of % Change column and select Number Format.

• In Format Cells, select Percentage.
• Choose the Decimal Places (here, 2).
• Click OK.

This is the output.

Read More: Excel Pivot Table: Difference between Two Columns

Method 2 -Using the % Difference From Option to Calculate the Percentage Difference between Two Columns

Modify the dataset like the following: combine selling and cost prices in the Value column and enter prices in the Price column.

Steps:

• Follow Step 1 in Method 1 to create the Pivot Table.

• Drag Product to Rows, Price to Columns, and Value to Values.

• Drag Value to Values again.

You will see the Pivot Table.

• Go to the PivotTable Analyze Tab >> PivotTable >> Options.

• Select Totals & Filters and uncheck the options under Grand Totals.
• Click OK.

No totals are displayed, only the Sum of Value2.

• Click Sum of Value2.

• Select Value Field Settings.

• Change the name Sum of Value2 to % Change in Custom Name.
• Click Show Values As.

• Select Price in Base field and Cost in Base item.
• Click OK.

You will see the following percentage differences between selling and cost prices.

Practice Section

Practice here.

Read More: Calculate Difference Between Two Rows in Pivot Table

Download Workbook

<< Go Back to Pivot Table Calculations | Pivot Table in Excel | Learn Excel