Table of Contents
Pivot tables in Excel allow users to quickly and easily analyze and compare data sets. One useful feature of pivot tables is the ability to calculate the percentage difference between two columns. To find the percentage difference, simply add the two columns to the pivot table and then right-click on one of the cells. Select “Show Values As” and then choose “Difference From” from the drop-down menu. This will display the numerical difference between the two columns, and by clicking on the small arrow next to the cell, you can choose to show it as a percentage difference. This feature is helpful for quickly identifying the percentage change between two data points and can be used for various analysis purposes.
Excel: Find Percentage Difference Between Two Columns in Pivot Table
The following step-by-step example shows how to calculate the percentage difference between two columns in a pivot table in Excel.
Step 1: Enter the Data
First, let’s enter the following sales data for three different stores:
Step 2: Create the Pivot Table
Next, let’s create the following pivot table to summarize the total sales by store and by year:
Step 3: Calculate Percentage Difference Between Two Columns in the Pivot Table
Suppose we would like to create a new column in the pivot table that displays the percentage difference between the Sum of 2021 and Sum of 2022 columns.
To do so, we need to add a calculated field to the pivot table by clicking on any value in the pivot table, then clicking the PivotTable Analyze tab, then clicking Fields, Items & Sets, then Calculated Field:
In the new window that appears, type “Percentage Difference” in the Name field, then type the following in the Formula field:
= ('2022' - '2021') / '2021'
Then click Add, then click OK.
This calculated field will automatically be added to the pivot table:
For example:
- There was a 16.67% increase in sales between 2021 and 2022 for store A.
- There was a 15.44% increase in sales between 2021 and 2022 for store B.
- There was a 24.62% increase in sales between 2021 and 2022 for store C.
Feel free to highlight the values in the new field and change their format to a percentage format:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I find the percentage difference between two columns in a pivot table using Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-percentage-difference-between-two-columns-in-a-pivot-table-using-excel/
stats writer. "How can I find the percentage difference between two columns in a pivot table using Excel?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-percentage-difference-between-two-columns-in-a-pivot-table-using-excel/.
stats writer. "How can I find the percentage difference between two columns in a pivot table using Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-percentage-difference-between-two-columns-in-a-pivot-table-using-excel/.
stats writer (2024) 'How can I find the percentage difference between two columns in a pivot table using Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-percentage-difference-between-two-columns-in-a-pivot-table-using-excel/.
[1] stats writer, "How can I find the percentage difference between two columns in a pivot table using Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the percentage difference between two columns in a pivot table using Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.