Great question! I’ve run into this exact confusion before with pivot table calculated fields vs. calculated items—let’s break this down clearly based on your setup (years as column fields, no separate year-specific fields in your field list).

Option 1: Use Calculated Items (Best for Adding Difference Columns to Your Year Labels) #

This is the most straightforward approach if you want to add new columns like "2014-2013" and "2015-2014" directly alongside your existing year columns:

• First, click on any year label in your pivot table’s column area (e.g., the cell containing "2013").
• Go to the Analyze tab (Excel 2013+) → Fields, Items, & Sets → Calculated Item.
• In the pop-up window:
  1. For the name, enter something clear like 2014-2013 Difference.
  2. For the formula, simply write:

     =2014 - 2013
     

     (If your years are formatted as text, e.g., "2013年", wrap them in quotes: ="2014年" - "2013年".)
• Click Add, then repeat the process for the 2015-2014 difference using:

  =2015 - 2014
  

• Hit OK, and you’ll see the new difference columns added right next to your original year columns.

Option 2: Use Calculated Fields with GETPIVOTDATA (For Adding Difference as a New Measure) #

If you need the year-over-year difference to appear as a separate measure (e.g., a new row alongside your original values), use GETPIVOTDATA to target specific year values in your pivot table:

• Click any cell inside your pivot table.
• Go to Analyze → Fields, Items, & Sets → Calculated Field.
• For the name, enter 2014-2013 Difference.
• For the formula, use GETPIVOTDATA to pull values for each year. Replace $A$1 with your pivot table’s top-left cell address, "数值" with your value field name, and "年份" with your year field name:

  =GETPIVOTDATA("数值",$A$1,"年份",2014) - GETPIVOTDATA("数值",$A$1,"年份",2013)
  

• Repeat for the 2015-2014 difference:

  =GETPIVOTDATA("数值",$A$1,"年份",2015) - GETPIVOTDATA("数值",$A$1,"年份",2014)
  

• Click Add then OK, and the new difference measures will be added to your pivot table’s values area.

Key Notes #

• Calculated Items work directly with the year field’s existing items (2013/2014/2015) and automatically calculate differences for every row in your pivot table.
• GETPIVOTDATA is more flexible if your pivot table layout might change (it locks onto specific field-value pairs instead of relying on column position).
• Always double-check that your year text/numbers match exactly between the formula and your pivot table labels (no extra spaces, different capitalization, etc.).

内容的提问来源于stack exchange，提问作者Knauxu