How to calculate the difference using a pivot table

Table of contents:

Although pivot tables are very useful tools for analyzing and presenting data in Excel, alas, working with them can be confusing. Some functions, such as calculating the difference, must be performed in a special way in order to work correctly. The process is not well documented in the Excel manual, so here's how to calculate the difference in a PivotTable using third-party formulas.

Steps

Calculate Difference in Pivot Table Step 1

Step 1. Start Microsoft Excel

Calculate Difference in Pivot Table Step 2

Step 2. Open the PivotTable worksheet and the data source you are working with

Calculate Difference in Pivot Table Step 3

Step 3. Select the sheet containing the data

The data can be on the same sheet as the table or on a different one

Calculate Difference in Pivot Table Step 4

Step 4. Decide on the calculations that you want to add

Calculate Difference in Pivot Table Step 5

Step 5. Insert a column for calculating the difference

For example, suppose your table contains a cell that contains the difference between Column G and Column H, and both columns contain numeric values.
Right-click on column I and choose Insert Column from the pop-up menu. The column will be inserted to the right of H, and all columns to the right of it will be shifted to the right along with the data.

Calculate Difference in Pivot Table Step 6

Step 6. Enter a column heading such as Difference

Calculate Difference in Pivot Table Step 7

Step 7. Create a formula in the first cell of the column to calculate the difference

Using the example above, the formula should look like "= H1-G1" if you subtract column G data from column H data; "= G1-H1" if vice versa.
Make sure the formulas are spelled correctly so that you get a positive or negative number, depending on your desire.

Calculate Difference in Pivot Table Step 8

Step 8. Copy and paste the formula to the rest of the column

Calculate Difference in Pivot Table Step 9

Step 9. Click on the tab of the sheet with the pivot table if it differs from the column with data

Calculate Difference in Pivot Table Step 10

Step 10. Modify the original data for your pivot table

In Excel 2003, restart the PivotTable Wizard by clicking inside the PivotTable and choosing Wizard from the pop-up menu.
In Excel 2007 or 2010, click the Change Data Source button on the Pivot tab on the Tools menu.
Click and drag a selection rectangle or simply enter a range formula in the Range box to include the desired cells.

Calculate Difference in Pivot Table Step 11

Step 11. Refresh the pivot table by clicking the Refresh button

Calculate Difference in Pivot Table Step 12

Step 12. Add the difference column to the PivotTable by clicking on the column header and dragging and dropping it into the Values field in the PivotTable Wizard

You may need to swap the column names in the Values section so that the columns appear in the PivotTable in the correct order. You can drag and drop items in the Values section or in the table itself to change the order of the columns

Advice

Double check the resulting totals in the pivot table with the totals of the original data

Make sure that the original data range of the pivot table does not include a complete row from the data source table. The PivotTable will interpret this row as an additional data series, not a row of totals.