Formula Errors

Change Excel Formula References to the Next Row

Natalie TaylorNatalie Taylor Nov 12, 2026 868 views

Question details

The user needs to update formulas across monthly invoice worksheets so references pointing to one row point to the next row.

Change Excel Formula References 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the formulas to update

On the invoice worksheet, select the cells containing the formulas that currently reference row 9.

2
Open Find and Replace

Go to Home > Find & Select > Replace, or press Ctrl+H.

3
Search within formulas

Select Options if needed, then set Look in to Formulas so Excel searches the formula text.

4
Replace the row reference

In Find what, enter $9. In Replace with, enter $10, then select Replace All.

5
Repeat for other references or sheets

Review the changed formulas, then repeat Home > Find & Select > Replace for each required row reference and monthly invoice worksheet.

Replace an absolute row reference in selected Excel formulas
Use a specific search value: Searching for $9 is safer than searching for 9 because it targets the absolute row marker. Check formulas containing numbers such as $90 before using Replace All.
Edit spreadsheet formulas with WPS Office

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. 1. Open the invoice workbook: Open the .xlsx or .xls invoice workbook in WPS Spreadsheets and select the worksheet to update.
  2. 2. Select the formula range: Highlight the cells containing formulas with the row reference that needs to change.
  3. 3. Open the Replace dialog: Press Ctrl+H, or use Home > Find and Replace > Replace.
  4. 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. 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.
Find and Replace can update repeated absolute row references in selected formulas.Fill handles automatically adjust relative formula references for the next invoice row.Compatible with Microsoft Excel .xlsx and .xls worksheet files.A free, lightweight spreadsheet editor with a familiar Office-style interface.
microsoft office alternative - wps office

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.