Fix Excel Formulas Returning the Same PivotTable Value in Every Row
Question details
The user needs to understand why Excel formulas copied down beside a PivotTable keep returning the first row's value even though the visible formulas look correct.

- Product
- WPS Office Spreadsheets (Excel workbook compatibility)
- Device & OS
- not provided
- Scenario
- A user has a PivotTable and adjacent formulas, disabled GETPIVOTDATA, and copied the formulas down the column.
- Observed behavior
- The displayed formulas appear correct, yet every copied result is identical to the first row's value.
Open the workbook in WPS Office Spreadsheets and make sure you can view the formula bar and the PivotTable field list. Identify the lookup value cell and the PivotTable range before editing formulas.
Check Row References, Absolute Ranges, and Workbook Format in WPS Office Spreadsheets
Use this first to identify why copied formulas remain locked to the first row.
Most same-value issues are caused by an absolute reference to the first lookup cell or a lookup range that does not shift down. Saving as .xlsx and confirming the PivotTable source also avoids GETPIVOTDATA limitations.
Click the cell containing the first formula and press Ctrl+` to show formulas in WPS Office Spreadsheets.
Check that the lookup value changes from A2 to A3, A4, and so on down the column, instead of staying $A$2.
Remove any $ from the row part of the lookup reference that should move, for example change $A$2 to A2, then press Enter.
Click Menu > Save As, choose Excel Workbook (*.xlsx) from the file type list, and save the file.
Press F9 to recalculate and confirm the results now change per row.

Replace the Formula with an Exact-Match XLOOKUP or VLOOKUP That Adjusts by Row
Use this when the current formula still returns the same PivotTable value because the cell reference is not shifting or GETPIVOTDATA is misbehaving.
Fix PivotTable Formula Row Reference Issues in WPS Office Spreadsheets
Open the Excel workbook in WPS Office Spreadsheets, check the formula row references and workbook format, then use a row-aware lookup formula to return the correct PivotTable value in each row.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the PivotTable and formulas.
- 2. Show and inspect formulas: Click the Formulas tab, then use the Show Formulas option or press Ctrl+` to review which references are absolute.
- 3. Correct the row reference: In the first formula cell, change the lookup value to a relative reference such as A2, and remove unwanted $ signs from the row portion.
- 4. Save as Excel Workbook: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save.
- 5. Fill down and verify: Drag the fill handle down the result column and press F9 to confirm each row returns a different value where expected.

Frequently Asked Questions
Why does my copied formula still show the first row’s value even though the formula text looks correct?
Usually the lookup value or return range is either absolute or not shifting. In WPS Office Spreadsheets, press Ctrl+` to show formulas and check whether the row reference changes from A2 to A3, A4, and so on. Remove any $ signs that lock the row, then press F9 to recalculate.
Does GETPIVOTDATA need to be enabled for normal lookup formulas to work?
No. If you prefer not to use GETPIVOTDATA, you can enter an exact-match lookup such as =XLOOKUP(A2,LookupRange,ReturnRange,"Not found") or =VLOOKUP(A2,TableArray,ColumnIndex,FALSE). Make sure the first argument uses a relative row reference.
Why does the Data Model affect GETPIVOTDATA in my workbook?
When a PivotTable is based on the workbook Data Model, Excel and WPS Office may not expose GETPIVOTDATA arguments in the expected way. Saving the file as an Excel Workbook (*.xlsx) and using a direct lookup formula usually avoids the issue.
How can I save my workbook in the correct Excel format in WPS Office?
Click Menu > Save As, select Excel Workbook (*.xlsx) from the file type list, choose a location, and click Save. This helps preserve compatibility for PivotTables and formulas.
When should I use XLOOKUP instead of VLOOKUP for this problem?
Use XLOOKUP if your WPS Office Spreadsheets version supports it because it separates the lookup range and return range and includes a not-found message. Use VLOOKUP with FALSE as the fourth argument when you need broader compatibility with older Excel files.




