How to calculate the difference in a pivot table

Although pivot tables are perfect for analyzing and explaining data in Excel, they can also be very confusing to work with. Some functions, such as calculating differences, must be carried out in a certain way in order for them to work correctly. The process is not well explained in the Excel help function, so here is how to calculate the differences in a pivot table without using weird formulas.

Steps

Calculate Difference in Pivot Table Step 1

Step 1. Open Microsoft Excel

Calculate Difference in Pivot Table Step 2

Step 2. Open the spreadsheet that contains the pivot table and the data you are going to work with

Calculate Difference in Pivot Table Step 3

Step 3. Select the sheet tab that contains the data

This may or may not be the same sheet where your pivot table is located

Calculate Difference in Pivot Table Step 4

Step 4. Determine the calculation you want to add

Calculate Difference in Pivot Table Step 5

Step 5. Insert a column for the calculated differences

For example, let's say you want your pivot table to include a field that shows the difference between columns G and H, and both columns contain numeric fields.
Click on column I and choose the "Insert column" option from the pop-up menu. A column will be inserted to the right of column H and all columns of data beyond that column will be shifted one place to the right.

Calculate Difference in Pivot Table Step 6

Step 6. Give the column a name, such as “Difference

”

Calculate Difference in Pivot Table Step 7

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

Using the previous example, the formula would be like this "= H1-G1" in the case that you are subtracting column G from column H; "= G1-H1" if you want to do the opposite.
Make sure to choose the correct syntax for the formula so that you get a positive or negative number as desired.

Calculate Difference in Pivot Table Step 8

Step 8. Copy and paste the formula into the rest of the new column

Calculate Difference in Pivot Table Step 9

Step 9. Click on the spreadsheet tab that contains your pivot table, if it has a different location than where you have the data

Calculate Difference in Pivot Table Step 10

Step 10. Modify the data in your pivot table

In Excel 2003, relaunch the pivot table by clicking inside the table and selecting "Wizard" from the pop-up menu.
In Excel 2007 or 2010, click the "Change Data" option in the PivotTable Options tab.
You can click and drag to select a new range or just modify the existing formula in the "range" field to include the next column.

Calculate Difference in Pivot Table Step 11

Step 11. Update your pivot table by clicking the "Update" button

Calculate Difference in Pivot Table Step 12

Step 12. Add the column with the calculated differences to your pivot table by clicking on the column name, dragging and dropping it into the "values" field located in the pivot table attendance table

You may need to change the order of the column names for the "values" to make the columns appear in the pivot table in the correct order. You can click and drag from the "values" section or directly into the pivot table to rearrange the order of the columns

Advice

Remember that all changes to the data displayed in a pivot table must take place within the data source table. You cannot edit or manipulate the contents of cells in a pivot table.
Double check the total results in your pivot table against the totals from the source data. Make sure your pivot table ranges do not include a total of rows from the table where the original data is. A pivot table would interpret this row as an additional row of data and not as a row of sums.