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.
Step 1. Open Microsoft Excel
Step 2. Open the spreadsheet that contains the pivot table and the data you are going to work with
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
Step 4. Determine the calculation you want to add
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.
Step 6. Give the column a name, such as “Difference
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.
Step 8. Copy and paste the formula into the rest of the new column
Step 9. Click on the spreadsheet tab that contains your pivot table, if it has a different location than where you have the data
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.
Step 11. Update your pivot table by clicking the "Update" button
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
- 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.