Pivot Table Issues

Fix Excel Formulas Returning the Same PivotTable Value in Every Row

Bushra ParveenBushra Parveen Nov 13, 2026 870 views

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.

Fix Excel Formulas Returning the Same PivotTable Value in Every Row
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first formula cell

Click the cell containing the first formula and press Ctrl+` to show formulas in WPS Office Spreadsheets.

2
Compare the row reference

Check that the lookup value changes from A2 to A3, A4, and so on down the column, instead of staying $A$2.

3
Adjust absolute references

Remove any $ from the row part of the lookup reference that should move, for example change $A$2 to A2, then press Enter.

4
Save in Excel Workbook format

Click Menu > Save As, choose Excel Workbook (*.xlsx) from the file type list, and save the file.

5
Recalculate and test

Press F9 to recalculate and confirm the results now change per row.

Check Row References, Absolute Ranges, and Workbook Format in WPS Office Spreadsheets
Formula fixed: After this check, formulas should update row by row. If they still do not, use the direct lookup formula solution below.

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. 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. 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. 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. 4. Save as Excel Workbook: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save.
  5. 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.
Edit Excel formulas and PivotTables without changing your existing .xlsx workbook format.Full compatibility with Microsoft Excel formulas, PivotTables, and workbook files.Free, lightweight Office suite with a familiar ribbon interface.Quickly switch from Excel and keep using XLOOKUP, VLOOKUP, and GETPIVOTDATA workflows.
QA img-9

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.