PivotTable in Excel: Get the Difference between Two Rows - Original Table Value

Pivot Table Excel: Getting the Difference Between Two Rows - Original Table Value

Pivot tables in Excel are a powerful tool for summarizing, analyzing, and presenting data in a meaningful way. One common use case is to compare the values of two rows in the original table and calculate the difference. This article will guide you through the process of getting the difference between two rows in an Excel pivot table, using the original table values.

Creating a Pivot Table in Excel

Before diving into the specifics of calculating the difference between two rows, let's first create a pivot table using the sample data provided:

To create a pivot table, follow these steps:

1. Copy and paste the sample data into an Excel worksheet.
2. Click anywhere within the data range.
3. Go to the Insert tab and click on PivotTable.
4. In the Create PivotTable dialog box, ensure the correct data range is selected and choose where you want the PivotTable to be placed.
5. Click OK.

Calculating the Difference Between Two Rows

To calculate the difference between two rows in the pivot table, follow these steps:

1. Add the required fields to the pivot table (in this example, we will use Period, Retailer, and Metric1: Quantity Sold).
2. Add a calculated field to get the difference between the two rows. To do this, go to the PivotTable Analyze tab, click on Fields, Items, & Sets, and then click on Calculated Field.
3. In the Name box, enter a name for the calculated field (e.g., Difference).
4. In the Formula box, enter the following formula: =Sum(Metric1: Quantity Sold) - Prev(Sum(Metric1: Quantity Sold))
5. Click Add and then click OK.

The Difference column will now show the difference between the current row and the previous row for the Metric1: Quantity Sold field.

Formatting the Pivot Table

To make the pivot table easier to read, you can format the values and headings. For example, you can change the number format for the Difference column to display as a percentage or currency.

In this article, we have covered how to create a pivot table in Excel, calculate the difference between two rows using the original table values, and format the pivot table for better readability. These techniques can be applied to various types of data and use cases, making pivot tables an essential tool for data analysis and presentation.

References

• Create a PivotTable report
• Calculate fields and items in a PivotTable
• Pivot Tables