How create pivot table or table that shows the change after refresh

Brass Contributor


Currently, I have created a pivot table that tracks that number of completed activities, so when I click refresh, the pivot table will update. 


However, the problem is that I would like to know the change from previous. For example, currently Australia has completed 3 activities. If I click refresh and it changes to 5, then instead of seeing "5", I would like to see "+2" as this is easier for me to monitor progress. Is this possible?






2 Replies


By adding a calculated field to a pivot table, you can determine how much the value has changed from the previous one. Here's how you can do it-

  1. Go to the PivotTable Field List.
  2. Right-click the field that represents the total number of completed activities in the "Values" area and select "Value Field Settings."
  3. In the "Value Field Settings" dialog box, select "Show Values As" and "% Difference From."
  4. In the "Base Field" dropdown, select the field that you want to compare to (in your case, the "Month" field).
  5. In the "Base Item" dropdown, select "Previous."
  6. Click "OK" to create the calculated field.
  7. Right-click on the calculated field in the Values area of the PivotTable.
  8. Select "Value Field Settings" again.
  9. In the "Value Field Settings" dialog box, select "Number Format."
  10. Choose the number format that you prefer (e.g., "Number" with zero decimal places).
  11. Click "OK" to apply the number format.

This should do it.


Afraid that's not possible. If activities are linked to date, when you may calculate total activities compare to total in previous period, whatever.

However, perhaps with cached PivotTable something could be done with VBA programming, not sure.