Formula Errors

How to Write an Excel Formula with a Relative Row Reference

Tauseeq MagsiTauseeq Magsi Oct 22, 2026 871 views

Question details

The user wants an Excel formula to automatically update its row reference when a new row is inserted, rather than staying locked to a specific row.

How to Write an Excel Formula with a Relative Row Reference
Product
Excel
Device & OS
not provided
Scenario
Updating formulas dynamically when inserting new rows into a spreadsheet.
Observed behavior
The current formula uses an absolute reference ($D$35), which prevents the row number from updating to 36 when a new row is inserted.
Before you start

Ensure you understand the difference between absolute references (locked with a $) and relative references (no $) before modifying your formula.

Solution 1Recommended

Use a Mixed Reference by Removing the Row Dollar Sign

Change the absolute row reference to a relative row reference so it adjusts dynamically when rows are added or the formula is dragged.

In Excel, a dollar sign ($) locks a column or row. The reference `$D$35` is completely absolute, meaning it will always point to row 35 even if you insert new rows. To let the row adjust but keep the column locked, you need to use a mixed reference.

1
Locate the formula

Select the cell containing your formula, for example, the one currently showing `=$D$35*B34`.

2
Edit the reference

Click into the formula bar and delete the dollar sign ($) immediately preceding the row number 35. The reference should now read `$D35`.

3
Apply and test

Press Enter to save the formula. Now insert a row above your referenced data and check your formula; it will automatically update to `$D36*B34` (or similar depending on your insertion point).

Use a Mixed Reference by Removing the Row Dollar Sign
Keyboard Shortcut: You can place your cursor in the formula bar on the reference and press F4 repeatedly to toggle between absolute, mixed, and relative references.
Manage Spreadsheet Formulas Easily

Use WPS Spreadsheet for Smart Formula Management

WPS Office Spreadsheet provides an intuitive interface for writing, editing, and troubleshooting complex formulas. It fully supports standard Excel formulas, absolute/relative references, and advanced data calculation features.

  1. 1. Download WPS Office: Install WPS Office from the official website and open your existing Excel workbook.
  2. 2. Select the Cell: Click the cell with the formula you want to modify.
  3. 3. Toggle Cell References: Highlight the cell reference in the formula bar and press F4 to quickly switch between relative, absolute, and mixed references.
  4. 4. Save Your Work: Your updated formulas will be perfectly preserved when you save your document in .xlsx format.
100% compatible with Microsoft Excel file formats (.xlsx) and formulasEasy-to-use formula builder and reference togglingFree, lightweight, and fast performanceBuilt-in error checking for common formula mistakes
microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between $D35 and D$35?

`$D35` locks column D so it will not change when copied horizontally, but allows row 35 to adjust when copied vertically. `D$35` locks row 35 but allows column D to adjust.

Why did my formula not update after inserting a row?

If your formula contains absolute row references (e.g., `$D$35`), the application will force the formula to continue calculating from row 35 regardless of inserted rows. Removing the row's dollar sign fixes this.

How do I quickly change reference types in a formula?

Select the cell reference within the formula bar and press the F4 key on your keyboard. This cycles through absolute (`$A$1`), mixed row (`A$1`), mixed column (`$A1`), and relative (`A1`) references.