Change Excel Formula References to the Next Row
Question details
The user needs to update formulas across monthly invoice worksheets so references pointing to one row point to the next row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Multiple invoice formulas contain row references that must be changed, such as changing row 9 references to row 10.
- Observed behavior
- Existing formulas continue to reference the original row instead of the next required row.
Identify whether the formulas use absolute row references, such as $A$9 or A$9, before replacing text. Select only the formula cells or worksheet range that should be updated so unrelated values and labels are not changed.
Replace an absolute row reference in selected Excel formulas
Use this method when many formulas contain the same absolute row reference, such as $9, and all selected formulas should point to the next row.
Replacing $9 with $10 changes only the row portion of absolute row references in the selected formulas. Set Excel to search formulas so it does not replace matching text displayed in cell values.
On the invoice worksheet, select the cells containing the formulas that currently reference row 9.
Go to Home > Find & Select > Replace, or press Ctrl+H.
Select Options if needed, then set Look in to Formulas so Excel searches the formula text.
In Find what, enter $9. In Replace with, enter $10, then select Replace All.
Review the changed formulas, then repeat Home > Find & Select > Replace for each required row reference and monthly invoice worksheet.

Fill formulas down when references should change relatively
Use this method when the formula should automatically move from row to row, such as A9 becoming A10, rather than changing a fixed absolute row reference.
Update Formula Row References Quickly in WPS Spreadsheets
WPS Spreadsheets lets you update repeated formula references with Find and Replace or fill formulas down when relative references should advance automatically. Its familiar spreadsheet interface makes it easy to move invoice formulas to the next row while reviewing the results.
- 1. Open the invoice workbook: Open the .xlsx or .xls invoice workbook in WPS Spreadsheets and select the worksheet to update.
- 2. Select the formula range: Highlight the cells containing formulas with the row reference that needs to change.
- 3. Open the Replace dialog: Press Ctrl+H, or use Home > Find and Replace > Replace.
- 4. Replace the row reference in formulas: Enter $9 in Find what and $10 in Replace with, choose to look in formulas if the option is shown, then select Replace All.
- 5. Check and save the worksheet: Review several updated formulas in the formula bar, then use File > Save or Save As to keep the revised workbook.

Frequently Asked Questions
How do I change $9 to $10 in Excel formulas without changing cell values?
Select the formula cells, press Ctrl+H, choose Options, set Look in to Formulas, enter $9 in Find what and $10 in Replace with, then select Replace All.
Why did Excel not change my formula references with Find and Replace?
Open the Replace dialog options and make sure Excel is searching in Formulas. Also confirm that you selected the correct worksheet or formula range before running Replace All.
Will copying a formula down change $9 to $10 automatically?
No. A row preceded by a dollar sign is absolute, so A$9 remains A$9 when copied down. Remove the dollar sign from the row reference if you want it to change relatively, or use Find and Replace to change $9 to $10.
Can I update formula references on several Excel worksheets at once?
You can repeat the replacement on each worksheet. If worksheets have the same layout, select the appropriate formula range on each sheet and use Ctrl+H with Look in set to Formulas.




