How to Keep an Excel Formula Range Fixed When Copying Down in WPS Office
Question details
The user needs to copy a formula downward in WPS Office Spreadsheet so the lookup header range C2:I2 remains fixed, while the comparison range C3:I3 updates to the next row automatically.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Copying an INDEX/MATCH formula down a column to return the header for the maximum value in each row.
- Observed behavior
- Without absolute referencing, the header range C2:I2 shifts downward with the fill action, causing incorrect lookup results.
Open the workbook in WPS Office Spreadsheet and select the cell that contains the formula. Identify the range that must stay fixed (C2:I2) and the range that should change row by row (C3:I3).
Use an Absolute Row Reference for the Lookup Header Range
Use this method when you fill a formula down and only the row in the lookup header range must stay locked while the comparison row updates.
The dollar sign placed before the row number (C$2:I$2) locks row 2 but leaves the column relative. When you drag the fill handle down, WPS Office Spreadsheet keeps the lookup range on row 2 and changes C3:I3 to C4:I4, C5:I5, and so on.
Click the cell containing the formula you want to copy down, then place the cursor in the formula bar.
Change C2:I2 to C$2:I$2 by typing a dollar sign before the row number 2 in the formula, for example: =INDEX(C$2:I$2,MATCH(MAX(C3:I3),C3:I3,0)).
Press Enter to confirm the formula, then drag the fill handle from the bottom-right corner of the cell down through the target rows.
Check that the header range remains C$2:I$2 in each copied formula while C3:I3 changes to C4:I4, C5:I5, and so on.
Keep Excel Formula Ranges Fixed in WPS Office Spreadsheets
WPS Office Spreadsheet handles Excel absolute and mixed references the same way, so you can add dollar signs to lock rows or columns, fill formulas down safely, and keep your workbook compatible with Microsoft Excel.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, click Spreadsheets, and open the workbook that contains the formula.
- 2. Edit the formula with an absolute row reference: Click the formula cell, go to the formula bar, and type $ before the row number in the range that must stay fixed, such as C$2:I$2.
- 3. Fill the formula down: Press Enter and drag the fill handle down the column to apply the formula to the remaining rows.
- 4. Check compatibility and save: Verify the fixed range stays on row 2, then save the file as .xlsx to keep full Microsoft Excel compatibility.
Frequently Asked Questions
What does C$2:I$2 mean in the formula?
C$2:I$2 is a mixed reference: the column letters are relative, but the row number 2 is locked by the dollar sign. When you fill the formula down, the range stays in row 2 while columns may adjust if copied sideways.
How do I keep the entire lookup range fixed in both row and column?
Use $C$2:$I$2 instead of C$2:I$2. The dollar signs before the column letters and row numbers lock the range completely, so it does not change when you copy the formula down or across.
Why does my INDEX/MATCH formula return the wrong header after copying down?
If the header range is written as C2:I2 without a dollar sign, Excel and WPS Office treat it as relative and shift it to C3:I3, C4:I4, etc. Change it to C$2:I$2 so the lookup range stays on the original header row.
Can I use absolute row references in WPS Office on Windows, Mac, and mobile?
Yes. WPS Office Spreadsheet uses the same formula syntax as Excel across Windows, macOS, Android, and iOS. Edit the formula in the formula bar and add the dollar sign to the row number.
Does WPS Office support INDEX and MATCH for Excel files?
Yes. WPS Office Spreadsheet supports INDEX, MATCH, MAX, and other Excel-compatible functions, and it can open and save .xlsx files without conversion.
