How to Fix Different Excel Results When Copying vs Filling Table Formulas in WPS Office
Question details
The user needs to understand why a table formula returns different results when filled with the fill handle than when copied with Ctrl+C and Ctrl+V, and how to keep the structured column reference fixed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user filled a formula across a table row and noticed the totals changed from the Price ex column to VAT%, VAT€, and Price incl. because the structured reference adjusted.
- Observed behavior
- Dragging the fill handle changes a structured reference such as Price ex to the corresponding table column, while copying and pasting preserves the original reference.
Open the workbook in WPS Office Spreadsheets and verify that the data range is an Excel table with structured references shown in the formula bar.
Lock a Table Column Reference with the Same-Column Range Syntax
Use this method when the formula must always refer to one table column, even if you fill it across other columns.
The fill handle treats structured column references as relative. When you drag a formula to the right, Excel or WPS Office Spreadsheets may shift Price ex to the next table column, producing VAT%, VAT€, or Price incl. totals.
Wrapping the same column name on both sides of a structured range locks the reference because the range does not have another column to shift to.
In WPS Office Spreadsheets, open the workbook and click the cell that contains the table formula you need to fill.
Click the formula bar and change the column reference to a fixed same-column range, for example =SUM(Table1[[Price ex]:[Price ex]]).
Press Enter to apply the edited formula to the selected cell.
Drag the fill handle across the cells to the right. The Price ex reference now remains unchanged because both ends of the structured range point to the same column.
Use Copy and Paste When You Only Need a One-Time Duplicate
Use this approach when you are not filling a series and you want the destination cells to keep the original structured reference unchanged.
Fix Excel Table Formula Reference Shifts in WPS Office Spreadsheets
Edit, test, and save Excel table formulas in WPS Office Spreadsheets using the same structured reference syntax. You can lock a column with =SUM(Table1[[Price ex]:[Price ex]]) and continue working in a familiar spreadsheet interface.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the table formula.
- 2. Locate the formula cell: Click the cell that shows the incorrect result after filling the table formula to the right.
- 3. Enter the fixed structured reference: In the formula bar, replace the shifting column reference with a same-column range such as =SUM(Table1[[Price ex]:[Price ex]]).
- 4. Fill and verify the result: Press Enter, drag the fill handle across the destination cells, and confirm the values still come from the Price ex column.
- 5. Save the workbook: Press Ctrl+S to save the corrected workbook in .xlsx format.
Frequently Asked Questions
Why does my table formula change when I drag the fill handle to the right?
Excel and WPS Office Spreadsheets treat structured column references as relative when you use the fill handle. A reference like Price ex can shift to VAT%, VAT€, or Price incl. as you fill across columns.
How do I lock a structured reference so it does not change?
Use a range that starts and ends with the same table column, such as =SUM(Table1[[Price ex]:[Price ex]]). This keeps the reference fixed when you fill the formula to the right.
Does copying with Ctrl+C and Ctrl+V also adjust structured references?
No. Copying and pasting normally duplicates the formula as written and preserves the original structured reference. The fill handle is the action that adjusts column references.
Can I use this same-column range syntax in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel table structured references and .xlsx files, so you can open, edit, and save workbooks with formulas like =SUM(Table1[[Price ex]:[Price ex]]).
What if my Excel or WPS version uses a localized function name?
The structure of the formula stays the same. Only the function name may be translated, for example from SUM to SOMMA in an Italian version, so use the localized function name followed by the same-column range.
