WPS Spreadsheet: Copy a Formula Down Without Changing a Range
Question details
The user needs to fill a formula down while keeping the same lookup range in every row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A formula checks values against the names in A2:A13 and must be copied to additional rows without moving that lookup range.
- Observed behavior
- Relative references change when the formula is filled down, causing the lookup range to shift from A2:A13 to other rows.
Confirm which references should change by row and which range must remain fixed. In this example, K9 or K2 is row-relative, while A2:A13 is the fixed lookup range.
Lock the lookup range with absolute references before filling down
Use this method when every copied formula must check the same cells, such as A2:A13.
Add dollar signs to both the column and row numbers of the lookup range. The reference $A$2:$A$13 stays unchanged when the formula is copied, while K9 or K2 can adjust for each row.
Select the result cell in WPS Spreadsheet and enter =OR(COUNTIF(K9,"*"&$A$2:$A$13&"*")) or =OR(ISNUMBER(SEARCH($A$2:$A$13,K2))).
Confirm that the lookup range appears as $A$2:$A$13 in the formula bar. The dollar signs lock both the column and row positions.
Press Enter, select the formula cell again, and drag its fill handle down the column, or select the destination range and choose Home > Fill > Down.
If an older WPS Spreadsheet or Excel-compatible calculation mode does not evaluate the array expression automatically, confirm the formula with Ctrl+Shift+Enter.
Copy the formula with keyboard or paste fill commands
Use this option when the formula already contains absolute references and you want to fill a selected range quickly.
Fill WPS Spreadsheet formulas while preserving fixed lookup ranges
WPS Spreadsheet lets you lock lookup ranges with absolute references, then fill formulas down using the fill handle, Home > Fill > Down, or Ctrl+D. It supports familiar Excel-compatible formulas and keeps row-relative references working as expected.
- 1. Open the spreadsheet: Launch WPS Spreadsheet and open the workbook containing the formula.
- 2. Lock the range: Select the formula cell and edit the formula bar so the fixed lookup range uses absolute references such as $A$2:$A$13.
- 3. Fill the formula down: Select the formula and destination cells, then choose Home > Fill > Down or press Ctrl+D.
- 4. Verify the copied formulas: Click several filled cells and confirm that the lookup range remains fixed while row-relative references update.
Frequently Asked Questions
Why does my lookup range move when I copy the formula down?
The range uses relative references, such as A2:A13. Add dollar signs to make it absolute: $A$2:$A$13. This keeps both the column and row positions fixed.
Which part of the formula should remain relative?
Keep the reference to the current row relative when it should change, such as K9 or K2. Lock only the shared lookup range with absolute references.
How can I fill a formula down in WPS Spreadsheet?
Select the formula cell and drag its fill handle downward. You can also select the source and destination cells and choose Home > Fill > Down, or press Ctrl+D.
Why might Ctrl+Shift+Enter be needed for the SEARCH formula?
Formulas that evaluate multiple lookup values as an array may require Ctrl+Shift+Enter in older Excel-compatible calculation modes. Newer versions may calculate the array automatically.
